Pull Data from Access Table into Excel Based on Cell Input

[jabbarahm]

There is a table in Access with all of our historical data. The table is organized with different Pricing Hubs. I'd like to pull into Excel only the data from one specific Hub at at time. I'd like this to happen by inputting which Hub I want in a cell in Excel, clicking a button to refresh the data and it pulls it from our Access Database instead of relinking each time with a new parameter.

I've recorded macro as I do this and this is what I have.

Sub Macro1()
'
' Macro1 Macro
'


Dim PullHub As Variant
PullHub = Range("N1").Value
MsgBox PullHub


With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\Documents and Settings\#######\Desktop\#######.accdb;DefaultDir=C:\Docume" _
), Array( _
"nts and Settings\jabbar_ahmad\Desktop;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _
"SELECT `Historical Prices`.SETTLEMENT_PRICE_DATE, `Historical Prices`.SETTLEMENT_PRICE, `Historical Prices`.STRIP, `Historical Prices`.`PROD DESC`, `Historical Prices`.IDENTIFIER, `Historical Prices`." _
, _
"HUB, `Historical Prices`.TRANSACTION" & Chr(13) & "" & Chr(10) & "FROM `Historical Prices` `Historical Prices`" & Chr(13) & "" & Chr(10) & "WHERE (`Historical Prices`.HUB='PullHub')" _
)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_MS_Access_Database"
.Refresh BackgroundQuery:=False
End With
End Sub

The PullHub string will change based on what I type into cell N1. I'd like Access to pull only that, but I cannot get it to recognize PullHub as a string instead of just text with the colons around it. Anything else I try either errors or gives me a blank value.

Thank you very much.

 

[SydneyGeek]

Try changing this line...

Prices`.HUB='PullHub')" _

... to this

Prices`.HUB='" & PullHub & "')" _

You have a couple of other options.

1. Without using code, you can use MS Query and refresh data when a defined cell is changed. You do this by turning the query into a parameter query. See this tutorial and its associated links.

2. You can use ADO code to pull data from Access (for example), or any other database that supports ADO -- most do. This tutorial shows how to do it, using Access as the data source.

Denis

 

[jabbarahm]

Try changing this line...

Prices`.HUB='PullHub')" _

... to this

Prices`.HUB='" & PullHub & "')" _

You have a couple of other options.

1. Without using code, you can use MS Query and refresh data when a defined cell is changed. You do this by turning the query into a parameter query. See this tutorial and its associated links.

2. You can use ADO code to pull data from Access (for example), or any other database that supports ADO -- most do. This tutorial shows how to do it, using Access as the data source.

Denis

omg...that worked, thank you so much

Yea I saw a lot of tutorials on how to directly link the entire table, but the thing is the table is gigantic and would take so long to refresh each time. I only need parts of the table to do what I want.

Again, thank you very much.

 

[SydneyGeek]

You're welcome

Denis