Visual Basic runtime error 1004
I am trying to write a Visual Basic Script to run by windows task scheduler. The script is supposed to open a delimited text file into excel and then save it as an .xlsx file.
Function ImportText() Path = "C:\Users\username\Documents\" inFile = "2014_1_31_data_parse.txt" outFile = "2014_1_31_data_parse.xlsx" Set objExcel = CreateObject("Excel.Application") objExcel.Workbooks.OpenText FileName:=Path & inFile, Other:=True, OtherChar:="^" objExcel.ActiveWorkbook.SaveAs FileName:=Path & outFile objExcel.Quit WScript.Quit WScript.Echo "Completed" End Function
Currently it gives a Run-time error of 1004 file can not be found. However the file is there.
There are a few issues with your code, but the first thing to change is the method call syntax. You are using named parameters (which I guess comes from a recorded macro?).
objExcel.Workbooks.OpenText FileName:=Path & inFile, Other:=True, OtherChar:="^"
should be changed to
objExcel.Workbooks.OpenText Path & InFile, , , , , , , , , , True, "^"
That's quite easy to see if you use IntelliSense in the VBA editor. Once you've solved that, you will have issues with error handling and other stuff that gets harder when the application is invisible, but that's a different story