Save As Macro

[Guest]

I often load CSV files into Excel and then re-save them as Excel Workbooks
(*.xls) format. The loading of the CSV file is handled by a command-line
that invokes Excel and loads the CSV.

After the CSV is loaded into Excel it would be VERY nice if a macro could be
executed that would then save the CSV in XLS format, instead of doing the
SAVE As manually.

Suggestions would be welcome.

 

[SudokuKing]

This code will perform the desired "save as" action. I'm not sure how
to auto-execute this macro, though. If you are opening multiple files,
you could modify this to loop through all the files after they've been
opened.

Sub SaveAsExcelWorkbook()

Dim fname As String

'Auto-generate a filename by changing the extension to ".xls"
If InStr(1, ActiveWorkbook.FullName, ".") <> 0 Then
fname = Left(ActiveWorkbook.FullName, InStr(1,
ActiveWorkbook.FullName, ".") - 1) & ".xls"
Else: fname = ActiveWorkbook.FullName & ".xls"
End If

'Uncomment line below to display a "Save As" dialog box
'fname =
Application.GetSaveAsFilename(InitialFileName:=Left(ActiveWorkbook.Name,
InStr(1, ActiveWorkbook.Name, ".") - 1) & ".xls", fileFilter:="Excel
Workbook files (*.xls), *.xls")
If fname <> "False" Then
'Save as excel Workbook.
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=fname,
FileFormat:=xlWorkbookNormal
If err.Number <> 0 Then MsgBox "File was not saved"
On Error GoTo 0
End If
'Uncomment line below to automatically close the workbook.
'ActiveWorkbook.Close
End Sub

 

[Guest]

To SudokuKing
Thanks for the quick response. How do I save this into a macro that will be
available for any CSV file that is loaded? I think it needs to go into the
personal.xls file but I'm not sure as to how to do it.

 

[SudokuKing]

How do I save this into a macro that will be
available for any CSV file that is loaded? I think
it needs to go into the personal.xls file but I'm not
sure as to how to do it.

--From any workbook in excel, press Alt-F11 to open the VBA editor.
--In the project explorer there should be "VBAProject (PERSONAL.XLS)".

--Right click the name -> Insert -> Module

You can paste the code into that module.

if personal.xls DOESN'T exist..... you can convince excel to create it
by recording a dummy macro.
--Tools -> Macro -> Record New Macro....
--Select "Personal Macro Workbook" from the drop down.
--Click OK, then Click the Stop button.
The personal.xls workbook now exists.

hope this helps.
SudokuKing

 

[Guest]

Thanks for the info and quick response. It worked great!
I'm still trying to figure out how to execute the macro when Excel loads,
but it's not a critical need - just curiosity.

 

[Steve Yandl]

Here is a slightly different approach. I create a vbScript file named
CSVtoXL.vbs which I stored in C:\Scripts. The content of the vbs file is
between the dotted lines below. I feed the script a csv file named
"C:\Test\RawTable.csv with the command line

C:\Scripts\CSVtoXL.vbs "C:\Test\RawTable.csv"

The script opens the csv file in Excel, saves it as an xls file with the
same file name and path as the csv file except for the extension and then
closes Excel.

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Const xlWorkbookNormal = -4143

' Only run script if it has been fed
If WScript.Arguments.Count = 0 Then
WScript.Echo "Need a file as a command line argument"
WScript.Quit
End If

fileCSV = WScript.Arguments.Item(0)

Set fso = CreateObject("Scripting.FileSystemObject")

If Not fso.FileExists(fileCSV) Then
WScript.Quit
End If

If Not Lcase(fso.GetExtensionName(fileCSV)) = "csv" Then
WScript.Quit
End If

strFileXL = fso.GetParentFolderName(fileCSV) & "\" & _
fso.GetBaseName(fileCSV) & ".xls"

Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = False
objExcel.Workbooks.OpenText fileCSV
objExcel.Application.ActiveWorkbook.SaveAs strFileXL, xlWorkbookNormal
objExcel.Quit

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Steve Yandl

 

[Guest]

Thanks Steve,

I'll give your suggestion a shot and see which I am most comfortable with.

I appreciated everyone's responses and suggestions. You guys are great!

Roy

 

[Steve Yandl]

Roy,

I wasn't sure how you had your command line set up at the start so I
suggested the command line option. My personal preference would be to place
a shortcut to the script in my SendTo folder which would allow me to right
click a csv file from Windows Explorer, send it into the shortcut and have
the xls file created. You can also use drag and drop into the script or you
can invoke the script from within a batch file. If you've disabled the vbs
file association for security reasons, you might need a different command
line that would include either cscript.exe or wscript.exe to launch the vbs
file and then the argument.

Steve