0

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.

Community
  • 1
  • 1
Guillaume B.
  • 1
  • 1
  • Does it help if you use: `Destination:=Activesheet.Range("$A$1")` – Rory Sep 06 '17 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. Sep 06 '17 at 15:17
  • Try removing the Array() command from the Source argument - it is not needed. – Rory Sep 07 '17 at 08: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. Sep 07 '17 at 08:44

1 Answers1

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".

Guillaume B.
  • 1
  • 1