Please help with my code

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AccessHelp - 24 Sep 2007 21:56 GMT

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.

Reply to this Message

Mike - 24 Sep 2007 22:10 GMT

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

> 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
[quoted text clipped - 25 lines]
>
> Thanks.

Reply to this Message

JLGWhiz - 24 Sep 2007 22:12 GMT

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

> 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
[quoted text clipped - 25 lines]
>
> Thanks.

Reply to this Message

AccessHelp - 24 Sep 2007 22:38 GMT

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/postui.aspx?mid=f73f0a77-f96c-47cb-878e-fbcebb
b703ee&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.programming&base
=http://www.microsoft.com/office/community/en-us/default.mspx&bclr=ececec&fclr=0
00000&sxml=http://www.microsoft.com/office/community/en-us/site.xml&stgxml=http:
//www.microsoft.com/office/community/en-us/settings.xml&sdgloc=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
[quoted text clipped - 41 lines]
> >
> > Thanks.

Reply to this Message

Mark Ivey - 25 Sep 2007 02:09 GMT

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.

> Hi guys,
>
[quoted text clipped - 65 lines]
>> >
>> > Thanks.

Reply to this Message

AccessHelp - 25 Sep 2007 14:40 GMT

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.

> Actually,,,
>
[quoted text clipped - 75 lines]
> >> >
> >> > Thanks.

Reply to this Message

Mark Ivey - 26 Sep 2007 02:13 GMT

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_fileexist
s.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

> Good morning Mark,
>
[quoted text clipped - 108 lines]
>> >> >
>> >> > Thanks.

Reply to this Message

Mark Ivey - 26 Sep 2007 02:19 GMT

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_fileexist
s.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

> AccessHelp,
>
[quoted text clipped - 153 lines]
>>> >> >
>>> >> > Thanks.

Reply to this Message