Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Save xls file to cvs file 1

Status
Not open for further replies.

Rick_Stanich

Technical User
Jun 8, 2023
29
0
1
US
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.

Code:
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
 
 https://files.engineering.com/getfile.aspx?folder=03d2a4da-b648-4f98-a0b0-2d261da45bc9&file=57094-97_B_Op_2_of_2_03-04-24_3.csv
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
 
Workbook instead of name....
Got it!
Worked fine with your code.
Solved.

Thank you.

Rick Stanich
CMM Programming and Consulting, LLC
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top