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.

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

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.

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



"AccessHelp" wrote:

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.

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/po...loc=en-US&tt=2

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



"AccessHelp" wrote:

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.

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.


"AccessHelp" wrote in message
...
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/po...loc=en-US&tt=2

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



"AccessHelp" wrote:

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.

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" wrote:

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.


"AccessHelp" wrote in message
...
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/po...loc=en-US&tt=2

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



"AccessHelp" wrote:

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.