Error Query HDR=No Text File

[adygelber]

Hi guys!

I don't manage to figure it out a "no value given for one or more required parameters" error on this code:

    Const INPUT_PATH As String = "C:\Input\"
    Dim ConnTXT As New ADODB.Connection
    Dim QryTxtRec As New ADODB.Recordset

    ConnTXT.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & INPUT_PATH & ";Extended Properties='text;HDR=NO;IMEX=1;FMT=Fixed'"
    '-----
    QryTxtRec.Open "SELECT F1 as Col1 FROM FILE1.txt", ConnTXT, adOpenStatic, adLockReadOnly, adCmdText
    '-----
    With ThisWorkbook.Sheets(1)
        .Cells.ClearContents
        '-----
        .Range("A2").CopyFromRecordset QryTxtRec
    End With

Can someone help me with the correct syntax?

NB: if I use * instead of "F1 as Col1" it works well but tbh I would have liked to have something like "F1 as Col1, F2 as Col2,..."

Cheers,
Adrian

 

[adygelber]

I forgot to say that I am using a Schema.ini like this:

ColNameHeader=False
Format=FixedLength
MaxScanRows=0
CharacterSet=OEM
DateTimeFormat=YYYY-MM-DD
Col1= "Col1Name"          Long   Width  5
Col2= "Col2Name"           Long   Width  5
Col3= "Col3Name"         Char   Width 13
Col4= "Col4Name"           Char   Width  9
Col5= "Col5Name"         Char   Width  4

 

[JEC]

What if you use the headername instead of "F1"?

 

[adygelber]

What if you use the headername instead of "F1"?

Unfortunately the TXT file doesn't have a header. I am using the HDR = No property.

 

[RoryA]

The column names are specified in the schema.ini so you should use those = Col1Name etc.

 

[adygelber]

The column names are specified in the schema.ini so you should use those = Col1Name etc.

Thanks a lot, it worked!