QuerTable query!

[Rob]

Hi,

I have been given an Excel file that gets information from an Access
database using VBA code. Where will I find what the Access database is
called and what fields will it be looking at. The following code is a
snippet and I'd though that somewhere in the code I would have found out
where or what
the QueryTable refers.

ActiveSheet.Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
ActiveSheet.PivotTables("PivotTable1").RefreshTable

Thanks, Rob

 

[Guest]

Hi Rob:

Help us out here!

Hi,

I have been given an Excel file that gets information from an Access
database using VBA code. Where will I find what the Access database is
called and what fields will it be looking at. The following code is a
snippet and I'd though that somewhere in the code I would have found out
where or what
the QueryTable refers.

ActiveSheet.Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
ActiveSheet.PivotTables("PivotTable1").RefreshTable

Thanks, Rob

Well not in that part of the code and if you can't read it and you
done share it, it will remain a secret.
Think about it, you have the code and your asking someone in the group
that does not have the code to tell you what it says.

good Luck
TK

 

[Rob]

TK, The excel file has a command button which has the following code
attached, when the button is clicked it appears to me that a link is made
with an Access database as the range Team is populated with data and a pivot
table that reads from the data is updated. I understand the pivot table
update in the code but can't figure out how the data is extracted from the
Access database, there seems to be no code showing what database is being
used. My question is "how do I change what data is extracted from the
database? Thanks Rob

Private Sub btnPivotUpdate_Click()
On Error GoTo Err_btnPivotUpdate
Application.ScreenUpdating = False

If Sheets("Team").Visible = 0 Then
Sheets("Team").Visible = True
Sheets("Team").Select
ActiveSheet.Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
ActiveSheet.PivotTables("PivotTable1").RefreshTable
'ActiveSheet.Protect
ActiveWindow.SelectedSheets.Visible = False

Else
Sheets("Team").Select
ActiveSheet.Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
ActiveSheet.PivotTables("PivotTable1").RefreshTable
ActiveWindow.SelectedSheets.Visible = False

End If
Sheets("Main Menu").Select

Application.ScreenUpdating = True

MsgBox ("Tables have been updated successfully")


Exit_btnPivotUpdate:
Exit Sub

Err_btnPivotUpdate:
MsgBox Err.Description, , "Updating Tables" & ""
Resume Exit_btnPivotUpdate

End Sub

 

[Guest]

Rob :

I’m not gong to be much help here but that is a
refresh procedure not the query.
From your first post I thought it was a DAO or ADO
procedeure but it looks like an MS Query.
I do not have MS Query installed on this computer
but I looked at the help files and they seem pretty
straightforward.
Try (Excel Menu)
Data/Get ExternalData/New DataBase Query
then build your own query

Good Luck

TK

 

[Rob]

TK,

Thanks for the pointers, Data/Get ExternalData was the bit I couldn't fathom
out.

Rob