Save Excel file to a CSV without opening it first


J

Jodie

I have an Excel file that is too large to open in Excel. I need to save it
as a csv file so I can use it in an import. How would I write a macro to
save this file? This is what I have, but it tries to open the file before
saving:
Sub ChangetoCsv()
ActiveWorkbook.SaveAs Filename:= _
"H:\_YEAR_END_REPORTS\DOWNLOAD_FILES\ORIGINALS\2009\" & "account
numbers" & ".csv" _
, FileFormat:=xlCSV, CreateBackup:=False
End Sub
 
Ad

Advertisements

J

John Bundy

Your code looks to be saving an already open file "Activeworkbook.SaveAs".
All you need to do is change the extension, use this from the help files,
have newname the same as oldname but with a .csv at the end instead of .xls.
Name Statement Example
This example uses the Name statement to rename a file. For purposes of this
example, assume that the directories or folders that are specified already
exist. On the Macintosh, “HD:†is the default drive name and portions of the
pathname are separated by colons instead of backslashes.

Dim OldName, NewName
OldName = "OLDFILE": NewName = "NEWFILE" ' Define file names.
Name OldName As NewName ' Rename file.

OldName = "C:\MYDIR\OLDFILE": NewName = "C:\YOURDIR\NEWFILE"
Name OldName As NewName ' Move and rename file.
 
J

Jodie

John, when I tried changing the extension manually, just to test before
asking for help, it made the file unusable. Wouldn't this do the same thing?
 
J

John Bundy

What version of Excel are you using? It isn't unuable for me, it has a
warning that changing the extension MAY make it unusable, but it works great
for me. Make a copy of a file and try it out!
 
Ad

Advertisements

J

Jodie

I am using 2003 11. I tried it and can't get it to work. I must be doing
something wrong. Here is what I have:

Sub ChangetoCsv()

Dim OldName, NewName
OldName = "OLDFILE": NewName = "NEWFILE"
OldName = "H:\_YEAR_END_REPORTS\DOWNLOAD_FILES\ORIGINALS\2009\resource
files\New Folder\account number.xls": NewName =
"H:\_YEAR_END_REPORTS\DOWNLOAD_FILES\ORIGINALS\2009\resource files\New
Folder\account number.csv"

End Sub
Am I missing a step?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top