1

I have tried so many websites and combinations but I can not create a VBS script to open my CsV file and then save it as a Xlsm file. I did get it to rename the file but the renamed file was deemed corrupt and would not open. In Excel you can open a CSV and do a save as Xlsm and it works. I want to automate this process with the sccript below. This is Excel 2013. Option Explicit

Dim xlApp, xlBook

Set xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = False
Set xlBook = xlApp.Workbooks.Open("c:\1Datadump\datadump.csv")
xlApp.Application.Visible = True


ActiveWorkbook.SaveAs Filename:="C:\1Datadump\datadump.xlsm"
xlApp.Workbooks.close
xlApp.Application.Quit

Thank you for any suggestions.

Les
  • 37
  • 1
  • 4
  • You may want to conside saving it as a .xlsx file extension (no reason for CSV file to be macro enabled) and to specifiy the second paramter (file format) as integer 50 (version 12 - 2007) – dbmitch Sep 10 '16 at 18:54

2 Answers2

1

Read here or here that/why

ActiveWorkbook.SaveAs Filename:="C:\1Datadump\datadump.xlsm"

(:=, named parameter) is not valid VBScript.

Community
  • 1
  • 1
Ekkehard.Horner
  • 38,498
  • 2
  • 45
  • 96
1

You need to specify the FileFormat. You'll also need to qualify your workbook reference:

If you want to save the ActiveWorkbook then use Application.ActiveWorkbook:

Const xlOpenXMLWorkbookMacroEnabled = 52
xlApp.ActiveWorkbook.SaveAs "C:\1Datadump\datadump.xlsm", xlOpenXMLWorkbookMacroEnabled

But since you already have a refernce to the workbook; I'd use:

Const xlOpenXMLWorkbookMacroEnabled = 52
xlBook.SaveAs "C:\1Datadump\datadump.xlsm", xlOpenXMLWorkbookMacroEnabled