Set wbCSV = Workbooks syntax error

TimTDP

New Member
Joined
Aug 3, 2011
Messages
23
I am new at Excel VBA! I have the following code:

Dim wbCSV As Workbook
Dim fPath As String
Dim fCSV As String

fPath = "C:\Users\Tim\Store Data\"
fCSV = Dir(fPath & "FileName.txt")

Set wbCSV = Workbooks.OpenText Filename:=fPath & fCSV, _
DataType:=xlDelimited, Tab:=True, _
FieldInfo:=Array(Array(1, xlGeneralFormat), _
Array(2, xlDMYFormat), _
Array(3, xlGeneralFormat), _
Array(4, xlGeneralFormat), _
Array(5, xlGeneralFormat), _
Array(6, xlGeneralFormat))


There is a syntax error in Set wbCSV = Workbooks......
as it stays red in colour

Where is the error?

Many thanks
 
Last edited:

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

tlowry

Well-known Member
Joined
Nov 3, 2011
Messages
1,367
Since you are useing ...OpenText as a function, () is/are required around the arguments i.e.

Set wbCSV = Workbooks.OpenText(Filename:=fPath & fCSV, _
DataType:=xlDelimited, Tab:=True, _
FieldInfo:=Array(Array(1, xlGeneralFormat), _
Array(2, xlDMYFormat), _
Array(3, xlGeneralFormat), _
Array(4, xlGeneralFormat), _
Array(5, xlGeneralFormat), _
Array(6, xlGeneralFormat)))
 

TimTDP

New Member
Joined
Aug 3, 2011
Messages
23
Thanks

Now I get an error on .OpenText
Expected function or Variable
 

tlowry

Well-known Member
Joined
Nov 3, 2011
Messages
1,367
Next thing I'd try is to not use the array inside the function.

I'd try a for/next loop.
 

TimTDP

New Member
Joined
Aug 3, 2011
Messages
23

ADVERTISEMENT

Can you explain how to do that?
sorry but I am new to Excel vba
 

tlowry

Well-known Member
Joined
Nov 3, 2011
Messages
1,367
“Upon further review…”

· I learned a lot on this one
· OpenText cannot be used as a function
· The fPath should NOT include the file name
· The FieldInfo is a description of the columns in the file (see below)

<table style="width:310.0pt; border-collapse:collapse;mso-padding-alt:0in 0in 0in 0in" border="0" cellpadding="0" cellspacing="0" width="517"> <tbody><tr style="height:18.6pt"> <td style="width:155.0pt;padding:0in 0in 0in 0in;height:18.6pt" width="258"> Member name
</td> <td style="width:155.0pt;padding:0in 0in 0in 0in;height:18.6pt" width="258"> Description
</td> </tr> <tr style="height:18.6pt"> <td style="width:155.0pt;padding:0in 0in 0in 0in; height:18.6pt" valign="top" width="258"> xlDMYFormat
</td> <td style="width:155.0pt;padding:0in 0in 0in 0in; height:18.6pt" valign="top" width="258"> DMY date format.
</td> </tr> <tr style="height:18.6pt"> <td style="width:155.0pt;padding:0in 0in 0in 0in; height:18.6pt" valign="top" width="258"> xlDYMFormat
</td> <td style="width:155.0pt;padding:0in 0in 0in 0in; height:18.6pt" valign="top" width="258"> DYM date format.
</td> </tr> <tr style="height:18.6pt"> <td style="width:155.0pt;padding:0in 0in 0in 0in; height:18.6pt" valign="top" width="258"> xlEMDFormat
</td> <td style="width:155.0pt;padding:0in 0in 0in 0in; height:18.6pt" valign="top" width="258"> EMD date format.
</td> </tr> <tr style="height:18.6pt"> <td style="width:155.0pt;padding:0in 0in 0in 0in; height:18.6pt" valign="top" width="258"> xlGeneralFormat
</td> <td style="width:155.0pt;padding:0in 0in 0in 0in; height:18.6pt" valign="top" width="258"> General.
</td> </tr> <tr style="height:18.6pt"> <td style="width:155.0pt;padding:0in 0in 0in 0in; height:18.6pt" valign="top" width="258"> xlMDYFormat
</td> <td style="width:155.0pt;padding:0in 0in 0in 0in; height:18.6pt" valign="top" width="258"> MDY date format.
</td> </tr> <tr style="height:18.6pt"> <td style="width:155.0pt;padding:0in 0in 0in 0in; height:18.6pt" valign="top" width="258"> xlMYDFormat
</td> <td style="width:155.0pt;padding:0in 0in 0in 0in; height:18.6pt" valign="top" width="258"> MYD date format.
</td> </tr> <tr style="height:18.6pt"> <td style="width:155.0pt;padding:0in 0in 0in 0in; height:18.6pt" valign="top" width="258"> xlSkipColumn
</td> <td style="width:155.0pt;padding:0in 0in 0in 0in; height:18.6pt" valign="top" width="258"> Column is not parsed.
</td> </tr> <tr style="height:18.6pt"> <td style="width:155.0pt;padding:0in 0in 0in 0in; height:18.6pt" valign="top" width="258"> xlTextFormat
</td> <td style="width:155.0pt;padding:0in 0in 0in 0in; height:18.6pt" valign="top" width="258"> Text.
</td> </tr> <tr style="height:18.6pt"> <td style="width:155.0pt;padding:0in 0in 0in 0in; height:18.6pt" valign="top" width="258"> xlYDMFormat
</td> <td style="width:155.0pt;padding:0in 0in 0in 0in; height:18.6pt" valign="top" width="258"> YDM date format.
</td> </tr> <tr style="height:18.6pt"> <td style="width:155.0pt;padding:0in 0in 0in 0in; height:18.6pt" valign="top" width="258"> xlYMDFormat
</td> <td style="width:155.0pt;padding:0in 0in 0in 0in; height:18.6pt" valign="top" width="258"> YMD date format.
</td> </tr> </tbody></table> .

So the input file has 6 columns the second is a date format of DMY format

what I ended with is:

fPath = "c:\temp\"
fCSV = "test.csv"

Workbooks.OpenText Filename:=fPath & fCSV, _
DataType:=xlDelimited, Tab:=True, _
FieldInfo:=Array(Array(1, xlGeneralFormat), _
Array(2, xlDMYFormat), _
Array(3, xlGeneralFormat), _
Array(4, xlGeneralFormat), _
Array(5, xlGeneralFormat), _
Array(6, xlGeneralFormat))
 

TimTDP

New Member
Joined
Aug 3, 2011
Messages
23
tlowry

Thanks very much
The only part you missed in your solution was the Set... in the last line below

Workbooks.OpenText Filename:=fPath & fCSV, _
DataType:=xlDelimited, Tab:=True, _
FieldInfo:=Array(Array(1, xlGeneralFormat), _
Array(2, xlDMYFormat), _
Array(3, xlGeneralFormat), _
Array(4, xlGeneralFormat), _
Array(5, xlGeneralFormat), _
Array(6, xlGeneralFormat))
Set wbCSV = ActiveWorkbook

Problem solved!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,333
Messages
5,601,008
Members
414,421
Latest member
tonybear1994

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top