Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
QuerTable query!
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").RefreshTabl e Thanks, Rob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
QuerTable query!
Hi Rob:
Help us out here! "Rob" wrote: 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").RefreshTabl e 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
QueryTable query!
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").RefreshTabl e 'ActiveSheet.Protect ActiveWindow.SelectedSheets.Visible = False Else Sheets("Team").Select ActiveSheet.Range("A1").Select Selection.QueryTable.Refresh BackgroundQuery:=False ActiveSheet.PivotTables("PivotTable1").RefreshTabl e 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 "TK" wrote in message ... Hi Rob: Help us out here! "Rob" wrote: 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").RefreshTabl e 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
QueryTable query!
Rob :
Im 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" wrote: ActiveSheet.Range("A1").Select Selection.QueryTable.Refresh BackgroundQuery:=False ActiveSheet.PivotTables("PivotTable1").RefreshTabl e |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
QueryTable query!
TK,
Thanks for the pointers, Data/Get ExternalData was the bit I couldn't fathom out. Rob "TK" wrote in message ... 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" wrote: ActiveSheet.Range("A1").Select Selection.QueryTable.Refresh BackgroundQuery:=False ActiveSheet.PivotTables("PivotTable1").RefreshTabl e |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert hard coded query criteria to Parameter Query | Excel Discussion (Misc queries) | |||
Excel 2007 / MS Query - editing existing query to another sheet | Excel Discussion (Misc queries) | |||
Importing Data via Web Query - Can values be passed to query? | Excel Discussion (Misc queries) | |||
Stop to modify the SQL query manually entered into query ! | Excel Programming | |||
Problem with .Background Query option of ODBC Query | Excel Programming |