Please help with my code
 

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.

Please help with my code
 

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.

Please help with my code
 

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.

Please help with my code
 

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.

Please help with my code
 

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.

Please help with my code
 

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.