Set wbCSV = Workbooks syntax error

[TimTDP]

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

 

[tlowry]

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]

Thanks

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

 

[tlowry]

Next thing I'd try is to not use the array inside the function.

I'd try a for/next loop.

 

[TimTDP]

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

 

[tlowry]

“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]

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!

 

[shg]

Also posted at http://www.excelforum.com/excel-programming/801973-set-wbcsv-workbooks-syntax-error.html