Please help with my code

[Guest]

I have a sheet called "CSV" in Workbook A. Basically, I want to move that
worksheet "CSV" into a new workbook and turn everything into value. Then I
want to save the new workbook as a CSV file and to name it as "Test.csv". At
the end, I want the Test.csv to close.

I am having 2 problems to accomplish that process.

1. After saving as a Test.csv, I am still getting prompt for saving, even
though I use the code "Application.ScreenUpdating=False". How can I make it
to stop prompting?

2. When I open the Test.csv, the values with "1.00", "2.00", etc. (for
example) turn into 1, 2, etc., instead of staying as "1.00" and "2.00". How
can I make them to stay with "*.00"?

Below is my code:

Sheets("CSV").Select
ActiveSheet.Move
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone,
skipblanks:=False, _
Transpose:=False
ActiveWorkbook.SaveAs "C:\CSV Files\Test.csv", xlCSVMSDOS
Application.ScreenUpdating = False
ActiveWindow.Close

Thanks.

 

[Guest]

Application.DisplayAlerts = False 'will stop warning message and save
run your code
Application.DisplayAlerts = True 'turn warning mesages back on

 

[Guest]

See the change to your PasteSpecial line for formats. The second issue might
be because your SaveAs is not taking because of the syntax. But I could be
wrong.

Sheets("CSV").Select
ActiveSheet.Move
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
operation:=xlNone,
skipblanks:=False, _
Transpose:=False
ActiveWorkbook.SaveAs "C:\CSV Files\Test.csv", xlCSVMSDOS
Application.ScreenUpdating = False
ActiveWindow.Close

 

[Guest]

Hi guys,

Thanks for your responses. First of all, I got figured out on my first
problem. All I have to do is to turn it off at close, which means
"ActiveWindow.Close False".

As far as the second problem, I tried the code
"xlPasteValuesAndNumberFormats", and it didn't help. For somehow, once a
file is saved into CSV, CSV turns them into 1 from "1.00". I tried the
process manually, and that was what I discovered.

Thanks again.

"JLGWhiz"
wrote:http://www.microsoft.com/wn3/aspx/p...ommunity/en-us/settings.xml&sdgloc=en-US&tt=2

 

[Mark Ivey]

Actually,,,

The numbers are still intact using your original code. All you need to add
was what Mike mentioned to stop the prompt "Application.DisplayAlerts =
False". Try opening the saved CSV file with Notepad and you will see. When
you open a CSV file with MS Excel, it cannot retain special formatting
features (such as with number formats with 2 decimal places). If you are
using this CSV file as a repository, then it should do the trick when you
need to pull data back out of it at a later date.

 

[Guest]

Good morning Mark,

Thanks for the information. You are right. If I open the csv file in
Notepad, I would see them, and if I open it in Excel, I would not see those
zeros. Can I ask you 2 questions?

1. Is there a difference between "xlCSVMSDOS" and "xlCSVWindows"? If any,
what will be the differences/impacts in the output file?
2. I have a file called "Test.csv" in C:\. Can you help me with the code
to detect whether there is actually the file? I tried the following code,
and it does not seem to work when the file is not there.

Dim CSVDir as String
CSVDir = "C:\Test.csv"
If CSVDir <> " " then
perform this
else
perform this
end if

Thanks.

 

[Mark Ivey]

AccessHelp,

1. Is there a difference between "xlCSVMSDOS" and "xlCSVWindows"? If any,
what will be the differences/impacts in the output file?

I honestly don't know the difference between these two formats. It may have
only been important for older versions of MS Excel, but I am not sure.

2. I have a file called "Test.csv" in C:\. Can you help me with the code
to detect whether there is actually the file? I tried the following code,
and it does not seem to work when the file is not there.

See if the following code will help...

Sub TestForExistingFile()



''' Code to check for existing file

''' This code was modified from code at the following location

'''
http://www.devguru.com/technologies/vbscript/quickref/filesystemobject_fileexists.html

Dim filesys

Set filesys = CreateObject("Scripting.FileSystemObject")

If Not filesys.FileExists("c:\Temp.csv") Then

filesys.CreateTextFile "c:\Temp.csv", True

End If



''' Remaining code here



End Sub

 

[Mark Ivey]

Sorry... the code expanded. Sometimes I hate plain text formatting...

Take a look at this version without all the comments, but remember that this
code was obtained from another location (i.e. -
http://www.devguru.com/technologies/vbscript/quickref/filesystemobject_fileexists.html).

Sub TestForExistingFile()
Dim filesys
Set filesys = CreateObject("Scripting.FileSystemObject")
If Not filesys.FileExists("c:\Temp.csv") Then
filesys.CreateTextFile "c:\Temp.csv", True
End If
''' Remaining code here
End Sub