VBA Import Access Data

[IanT]

Hi

I am using the code below to import data from access, unfortunately the code does not recognise the variable 'FindFile' in the 'ODBC;DSN=MS Access Database' section of the code, can anyone advise why!!!


Sub MEU_AccessImport()

Dim FindFile As String


FindFile = Left(ActiveWorkbook.Path, InStrRev(ActiveWorkbook.Path, "\")) & "MI"





With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=FindFile" _
), Array( _
".mdb;DefaultDir=FindFile;DriverId=25;FIL=MS Access;" _
), Array("MaxBufferSize=2048;PageTimeout=5;")), Destination:=Range("A1"))

.CommandText = Array( _
"SELECT `MEU Rpt1 New Number 2011`.`Method used to ID claim`, `MEU Rpt1 New Number 2011`.`1`, `MEU Rpt1 New Number 2011`.`2`, `MEU Rpt1 New Number 2011`.`3`, `MEU Rpt1 New Number 2011`.`4`, `MEU Rpt1 N" _
, _
"ew Number 2011`.`5`, `MEU Rpt1 New Number 2011`.`6`, `MEU Rpt1 New Number 2011`.`7`, `MEU Rpt1 New Number 2011`.`8`" & Chr(13) & "" & Chr(10) & "FROM `C:\Documents and Settings\0123456\Desktop\MI\MIMI`.`MEU Rpt1 New Numb" _
, "er 2011` `MEU Rpt1 New Number 2011`")
.Name = "Query from MS Access Database_25"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Range("T22").Select
End Sub

 

[Trevor G]

How have you been getting on with your issue, have you found a solution?

One method you can do is in your Excel Workbook set the reference in the VBA screen to use Access and then you can use a DoCmd.TransferSpreadsheet command that will allow you to get the data into your workbook.

In your string command you would also have to use the .xls extension.