Pull in External Data from Access using MS Query using parameter range

[Whose_Anon]

Okay - I'm beat.

I thought up until today I was good at Excel. Alas, I fold after 12 hours of trying!

Here's what I'm trying to achieve:
1. I have an array of UK postcodes starting in A2 and continuing down indefinitely (depending on other inputs elsewhere in my model), for arguments sake, lets call it A200
2. I want to look up each of these postcodes in an Access database which has all these postcodes using MS Query and then return their corresponding latitude and longtitude numbers in their corresponding rows in Column's B and C in Excel, i.e. in A1 in Excel I have the postcode 'OX1 1AA', this query's Access for this postcode, which then returns the longtitude '51.3598391' and latitude '-0.35313351'.

If it can be done in SQL/MS Query great.

If VBA is needed or you think it would be more straightforward - please let me know! Please bear in mind it's essential to be able to lookup the whole cell range and return multiple query results - this restriction(?) in only letting you search for one cell in the parameter seems silly.

Some excel god is out there, and I need your help!

 

[anvg]

You can try this macro. You did not write what are versions of a Excel file and an Access file to use (I suppose: Excel 2003, Acess 2003). You need to change the path "d:\path.mydb.db" (sConn variable) to a needed path of your Access database file. And You will need to change field names and your database table name in a select query string (sSQL variable).
Macros creates new worksheet with a result in your soruce workbook.

Public Sub GetPostCoordinates()
    Dim pQTable As QueryTable
    Dim pSheet As Worksheet
    Dim sSQL As String, vLast As Long
    Dim sConn As String
    
    sConn = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\path\mydb.mdb"
    
    Set pSheet = ActiveSheet
    vLast = pSheet.Cells(pSheet.Rows.Count, 1).End(xlUp).Row
    sSQL = "Select Postcode,Lon,Lat From postcodes Inner Join [Excel 8.0;DATABASE=" '
    sSQL = sSQL & ThisWorkbook.FullName & ";HDR=NO].[Sheet1$" & "A1:A" & CStr(vLast)
    sSQL = sSQL & "] As TRange On (TRange.F1=postcodes.Postcode)"
    
    Set pSheet = ThisWorkbook.Worksheets.Add
    Set pQTable = pSheet.QueryTables.Add(sConn, pSheet.Cells(1, 1), sSQL)
    pQTable.Refresh
End Sub

 

[Whose_Anon]

You can try this macro. You did not write what are versions of a Excel file and an Access file to use (I suppose: Excel 2003, Acess 2003). You need to change the path "d:\path.mydb.db" (sConn variable) to a needed path of your Access database file. And You will need to change field names and your database table name in a select query string (sSQL variable).
Macros creates new worksheet with a result in your soruce workbook.

Public Sub GetPostCoordinates()
    Dim pQTable As QueryTable
    Dim pSheet As Worksheet
    Dim sSQL As String, vLast As Long
    Dim sConn As String
    
    sConn = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\path\mydb.mdb"
    
    Set pSheet = ActiveSheet
    vLast = pSheet.Cells(pSheet.Rows.Count, 1).End(xlUp).Row
    sSQL = "Select Postcode,Lon,Lat From postcodes Inner Join [Excel 8.0;DATABASE=" '
    sSQL = sSQL & ThisWorkbook.FullName & ";HDR=NO].[Sheet1$" & "A1:A" & CStr(vLast)
    sSQL = sSQL & "] As TRange On (TRange.F1=postcodes.Postcode)"
    
    Set pSheet = ThisWorkbook.Worksheets.Add
    Set pQTable = pSheet.QueryTables.Add(sConn, pSheet.Cells(1, 1), sSQL)
    pQTable.Refresh
End Sub

Hi anvg,

Thanks for your help!

So my headings in the database are 'Postcode', 'Latitude', 'Longtitude', the database table is called 'Postcodes', and the sheet with the postcodes that I am looking up is called 'Postcode lookup' in my workbook 'Book 1'. I am using Microsoft Office Professional Plus 2010 ver. 14.0.6129.5000.

Is the below correct? Because it is not yet working for me?

Public Sub GetPostCoordinates()
    Dim pQTable As QueryTable
    Dim pSheet As Worksheet
    Dim sSQL As String, vLast As Long
    Dim sConn As String
    
    sConn = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\path\mydb.mdb"
    
    Set pSheet = ActiveSheet
    vLast = pSheet.Cells(pSheet.Rows.Count, 1).End(xlUp).Row
    sSQL = "Select Postcode,Longtitude,Latitude From postcodes Inner Join [Excel 8.0;DATABASE=" '
    sSQL = sSQL & ThisWorkbook.FullName & ";HDR=NO].[Sheet1$" & "A1:A" & CStr(vLast)
    sSQL = sSQL & "] As TRange On (TRange.F1=postcodes.Postcode)"
    
    Set pSheet = ThisWorkbook.Worksheets.Add
    Set pQTable = pSheet.QueryTables.Add(sConn, pSheet.Cells(1, 1), sSQL)
    pQTable.Refresh
End Sub

Where;
Red is database headings
Green is database table name
Purple is file location

Have I done this correctly? Or do I need to enter something in the

Excel 8.0;DATABASE=" '

bit?

 

[anvg]

I am sorry. I had not an internet connection last weekend. I changed that macro code above for MS Office 2010.
Next, it is some details about macro’s parameters.

Public Sub GetPostCoordinates2()
    Dim pQTable As QueryTable
    Dim pSheet As Worksheet
    Dim sSQL As String
    Dim sConn As String
    
    'Connection string to your database
    sConn = "OLEDB;Provider=Microsoft.Jet.ACE.12.0;Data Source=d:\path\mydb.mdb"
    
    'SQL query for mixed data sources
    sSQL = "Select Postcode,Longtitude,Latitude From postcodes Inner Join "
    sSQL = sSQL & "[Excel 12.0;DATABASE=d:\path\YourExcelFile.xlsx;HDR=NO].[Sheet1$A1:A60000] As TRange "
    sSQL = sSQL & "On (TRange.F1=postcodes.Postcode)"
    
    Set pSheet = Activesheet
    Set pQTable = pSheet.QueryTables.Add(sConn, pSheet.Cells(1, 1), sSQL)
    pQTable.Refresh
End Sub

“Data Source=d:\path\YourDatabase.mdb” a part of the sConn variable contains a path and a file name of your Access database. Please, check its name and extension. Microsoft ACE’s Provider allows using mixed data sources.
Yes, You are right, "Select Postcode,Longtitude,Latitude From postcodes Inner Join " substring of sSQL variable “Postcode, Longtitude,Latitude” are fields of your table “postcodes” of your Access database file.
"[Excel 12.0;DATABASE=d:\path\YourExcelFile.xlsx;HDR=NO].[Sheet1$A1:A60000] As TRange" as a part of the sSQL variable defines such a second data source file. I describe it from right to left.

1. A1:A60000 is a cell range which contains required postcodes.
2. Sheet1 is a sheet name.
3. “NDR=NO”. If data in the range starts from first top cell you need to write “No” else it has a column name (for example, mypostcode) you need to write “Yes” and change F1 in sSQL variable (F1 is a default name of a nonamed column) to that name.
4. d:\path\YourExcelFile.xlsx is a path and a file name of your Excel .
5. Excel 12.0 defines a version of your Excel file. If its extension is ‘xls’ then change it to Excel 8.0 else its extension are ‘xlsx’, ‘xlsm’, ‘xlsb’ do not change.

In the code before the macros created this subpart of sSQL automatically.
Now, the macro code creates a query table on Activesheet.
Indeed, you can use MS Query for such solve, but you would need a hand-writing mode of a work with the application, because it does not allow adding other type of data source.