I have this VBS code:
Dim txtFilePath
txtFilePath = WScript.Arguments(0)
Dim excelApp
Set excelApp = CreateObject("Excel.Application")
Dim workbook
Set workbook = excelApp.Workbooks.OpenText(FileName:=txtFilePath, DataType:=xlDelimited, Semicolon:=True)
It fails. I read somewhere to remove the parenthesis, still fails. I've tried many variations of this and cannot get it to work. Can someone please tell me what I'm doing wrong? I've spent more time trying to automate this task than it takes me to do 100 times already... :(
doing this on Windows 11, if that makes a difference
EDIT:
This works now. Thanks for the help. I hate VBS... ;)
Dim txtFilePath
txtFilePath = WScript.Arguments(0)
Dim excelApp
Set excelApp = CreateObject("Excel.Application")
excelApp.Workbooks.OpenText txtFilePath, , , 1, , , , True
Dim workbook
Set workbook = excelApp.ActiveWorkbook
EDIT EDIT:
The reason I was trying to use OpenText was because I read somewhere that Excel couldn't Open a CSV with semicolon options or something like that. But after getting the above to work I revisited Open and found that this works better for me:
excelApp.Workbooks.Open txtFilePath, , True, 4
But! The file still needs to have the ".txt" extension! It can't have ".csv" or it ignores the semicolon option. Thanks Microsoft...
Tried assigning directly as BigBen suggested, but this fails (800A0401) for me:
Dim workbook
Set workbook = excelApp.Workbooks.Open txtFilePath, , True, 4
xlDelimitedis. Try addingConst xlDelimited = 1.OpenText()doesn't return aWorkbookobject reference so you shouldn't be usingSet. See Workbooks.OpenText Error "Expected function or variable". Also, VBScript doesn't support named arguments so you will need to drop theFilename:=,DataTRype:=andSemiColon:=. You will also have to define the constantxlDelimited.