0

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
5
  • VBScript doesn't know what xlDelimited is. Try adding Const xlDelimited = 1. Commented Apr 8 at 15:30
  • Fails before that. Commented Apr 8 at 15:36
  • 1
    The issue is OpenText() doesn't return a Workbook object reference so you shouldn't be using Set. See Workbooks.OpenText Error "Expected function or variable". Also, VBScript doesn't support named arguments so you will need to drop the Filename:=, DataTRype:= and SemiColon:=. You will also have to define the constant xlDelimited. Commented Apr 8 at 16:00
  • 1
    Relevant - Does VBScript allow named arguments in function calls? Commented Apr 8 at 16:07
  • 1
    @BigBen : Line: 8 Char: 52 Error: Expected ')' Code: 800A03EE Source: Microsoft VBScript compilation error Commented Apr 8 at 16:10

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.