Save xls file to cvs file 1

[Rick_Stanich]

I am trying to write VBScript to save an active Excel file as a CVS file, The format of the Excel file needs to be retained.
See example Excel file (57094-97_B_Op 2 of 2_03-04-24_3.xls) and example CVS file (57094-97_B_Op 2 of 2_03-04-24_3.cvs) for comparing.

The Excel file is from a CMM Software called, Calypso. You will get the standard "SECURITY WARNING Macros have been disabled." when opened. It seems the macros from the template that generates the Excel file gets carried over.

Back to my question.
I am failing to save the xls file in the script as a cvs file.

Dim objXL, strMessage

On Error Resume Next

Set objXL = GetObject(,"Excel.Application")

If Not TypeName(objXL) = "Empty" then
Const xlCSV=6
    'strMessage = "Excel Running"    
	src_file = objXL.ActiveWorkbook.Name
	'msgbox src_file 'for testing
		'New
		dest_file = Replace(Replace(src_file,".xlsx",".csv"),".xls",".csv")
		
		src_file.SaveAs "C:\Users\Public\Documents\Zeiss\CALYPSO 7.4\workarea\results\Excel Files" & "\" & dest_file, xlCSV
		msgbox dest_file 'for testing
		msgbox src_file 'for testing

		src_file.Close False
		objXL.Quit
		'End New
Else 
    MsgBox "No active Excel file open."
End If

Any help is appreciated.

Rick Stanich
CMM Programming and Consulting, LLC

 

[Rick_Stanich]

Excel xls file.

Rick Stanich
CMM Programming and Consulting, LLC

 

[combo]

You need to work with Excel workbook instead of its name:

[pre]If Not TypeName(objXL) = "Empty" then
Const xlCSV=6
Set objWbk = objXL.ActiveWorkbook ' may require additional test before to check if a workbook is open
src_file = objWbk.Name
'msgbox src_file 'for testing
'New
dest_file = Replace(Replace(src_file,".xlsx",".csv"),".xls",".csv")

objWbk.SaveAs "C:\Users\Public\Documents\Zeiss\CALYPSO 7.4\workarea\results\Excel Files" & "\" & dest_file, xlCSV
msgbox dest_file 'for testing
msgbox src_file 'for testing

objWbk.Close False[/pre]



combo

 

[Rick_Stanich]

Workbook instead of name....
Got it!
Worked fine with your code.
Solved.

Thank you.

Rick Stanich
CMM Programming and Consulting, LLC