I am trying to import an Access query into Excel using VBA, but with a parameter. For example, the Access query I am trying to import asks the user for a valuation year, such as 2017.

I have already found some code, using Microsoft ActiveX Data Objects (ADODB). My understanding is that this is the technique that MS is currently supporting for doing imports, even though are other ways, such as DAO (Data Access Objects)

The ADO code below works to import queries that have no parameters. It uses 6 variables that have been set up in the rest of the code, but this subroutine does most of the actual importing work. I would like to add a prompt and a parameter field on the sheet in Excel where I am importing the parameter query.

Regarding the code below where there are no parameters, I believe I need to add something to the query string to use the prompt and parameter, and some additional code to use the parameter.

Has anyone done this before, and perhaps can point me in the right direction?

VBA subroutine to import Access query with No Parameters:
Private Sub PullQueryDataNoParam(SrcPathfile, SrcQry, TgtTab, Col2Start, TgtCol, Row2Start)
'Purpose: Pull query results from Access into Excel, using ActiveX Data Objects interface
Dim strDb, strQry As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim i As Integer
Dim Cell4Title, Cell2Start As String

Cell4Title = Col2Start + CStr(Row2Start) 'the first row pasted is the column headings
Cell2Start = Col2Start & CStr(Row2Start + 1) 'the query results are pasted in the next row

Sheets(TgtTab).Select 'select tab in Excel WB for results to be imported
strDb = SrcPathfile 'path and filename of Access DB
strQry = "SELECT * FROM " & SrcQry 'code for select query

Set cn = New ADODB.Connection 'create a new ADO connection to Access DB
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDb & ";"

Set rs = New ADODB.Recordset

'open the query in Access
With rs
Set .ActiveConnection = cn
.Open strQry
End With

'copy field names in Access as a title row in Excel (bold font), then copy the query results in the next row
With Sheets(TgtTab)
For i = TgtCol To rs.Fields.Count + TgtCol - 1
.Cells(Row2Start, i).Value = rs.Fields(i - TgtCol).Name 'fields is a 0 based collection
Next i
.Range(Cell4Title).Resize(ColumnSize:=rs.Fields.Co unt).Font.Bold = True 'title row is bolded and resized to query
.Range(Cell2Start).CopyFromRecordset rs
End With

rs.Close 'close ADODB.RecordSet
cn.Close 'close ADODB.Connection

End Sub 'PullQueryData(a,b,c,d,e,f)