Excel VBA listobjects.add querytable error 9

Question

I aim to get data from a csv file using an SQL request. I already made it successfully in a previous Workbook, so I copied it and modified it only on the SQL request part. I have an execution error 9 on the first line, which has not been modified after copying the code:

ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
    "ODBC;DBQ=" & Path & ";DefaultDir=" & Path & _
    ";Driver={Microsoft Access Text Driver (*.txt, *.csv)};DriverId=27;FIL=text", _
    ";MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;" _
    ), Destination:=ThisWorkbook.Sheets("Sessions").Range("$A$1")).QueryTable

The variable Path is the path to get to the folder where the file is stored. The Sheet "Sessions" is created before this line is ran, an is activated.

This is the complete code of the request:

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
    "ODBC;DBQ=" & Path & ";DefaultDir=" & Path & _
    ";Driver={Microsoft Access Text Driver (*.txt, *.csv)};DriverId=27;FIL=text", _
    ";MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;" _
    ), Destination:=ThisWorkbook.Sheets("Sessions").Range("$A$1")).QueryTable
    .CommandText = Array( _
    Selection & Chr(13) & Chr(10) & "FROM `" & NameFile(1) & "` `" & NameFile(2) & "`" & _
    Chr(13) & Chr(10) & Condition)
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = "Session"
    .Refresh BackgroundQuery:=False
End With

I am quite new with connections in VBA, as it is only my second program using it. It is probably very simple to correct but I did not find a working solution for my case.

Does it help if you use: Destination:=Activesheet.Range("$A$1") — Rory
– Rory, Commented Sep 6, 2017 at 15:06
Thx for the answer. I tried the following destination: range("$A$1"), activesheet.range("$A$1"), ThisWorkbook.Sheets("Sessions").Range("$A$1") I precise that the path specified is the right one (link to the folder containing the file on my computer). — Guillaume B.
– Guillaume B., Commented Sep 6, 2017 at 15:17
Try removing the Array() command from the Source argument - it is not needed. — Rory
– Rory, Commented Sep 7, 2017 at 8:16
I'll check, it may be linked to this indeed. After rebuilding my program step by step, I found that the problem was coming from the "Option base 1" that I put at top of the module. So the array may not be read properly. — Guillaume B.
– Guillaume B., Commented Sep 7, 2017 at 8:44

Guillaume B. · Accepted Answer · 2017-09-07 08:52:01Z

0

The problem is comming from the "Option base 1" and the array used by the recorder. Keeping the option and modifying the code to avoid array works, as well as well deleting the "Option Base 1".

answered Sep 7, 2017 at 8:52

Guillaume B.

11 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA listobjects.add querytable error 9

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related