Using VB to save an excel spreadsheet

[SBelyea]

Hey everyone -

I've got a script that cleans up a spreadsheet I have, but am running into trouble when I try to save it.

The source that I get the spreadsheet from saves it in a format that, while it says its .xls, isn't really .xls. I can open the spreadsheet fine and do all of the cleaning I need to but, I but want to be able to force it to save the file as a specific file type (.xls).

Here is my current (relevant) code:

Dim excelPath
Dim objExcel

Set objExcel = CreateObject("Excel.Application")

'where is the Excel file located?
excelPath = "C:\All Fields Report.xls"

'Save and exit
objExcel.ActiveWorkbook.SaveAs excelPath

I can't seem put any arguments in the SaveAs line of code beyond the filepath... any ideas?


Thanks!

 

[GhostWolf]

The SaveAs method has several parameters to it, and FileFormat is the second.

Have you tried something like

  objExcel.ActiveWorkbook.SaveAs (excelPath, [FileFormat])

I've only used the target name on the SaveAs method, so I don't know what values are recognized for the FileFormat. Maybe one of our more experienced members can give us some guidance on that...

 

[SBelyea]

GhostWolf -

I think I was just putting the wrong data in there. I had tried it with the following:

objExcel.ActiveWorkbook.SaveAs excelPath, "fileformat:=56" 

objExcel.ActiveWorkbook.SaveAs excelPath, fileformat:=56

..and discovered that the proper syntax for it is (using the proper file format as well):

objExcel.ActiveWorkbook.SaveAs excelPath, -4143

Problem solved

 

[GhostWolf]

Thanks for the hint.