Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListObjects.Add Question
I am building a workbook to automate reporrt generation for data in Access. I
have 40+ locations that I have to generate reports for, so I am creating worksheets for each site, and quereying Access for the specific data. What I would like to do, is on the worksheet for each site, have the data that I need for that site and the specific charts that I need to generate. I am using ListObjects.Add to add a QueryTable object to the worksheet. It query's the access db and gets the data that I need. This part works fine. It gets the data, and creates an xl table on the sheet. My question, is how do I make changes to that query. Let's say that the user wants to change the dates for the data being charted. How do I change that query and bring the new data back into the existing xl table (so that the chart updates itself)? I haven't been able to figure out how to do that. I need to make sure that I can change the query, re-run it and put the data into the previously defined xl table. From what I can tell, the QueryTable object will not put the data into a previously defined table. Any guidance or pointers are greatly appreciated. Steve |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListObjects.Add Question
You can't change the connection to a query table but you can change the other
properties. I usually record a macro when I start a query and then use then modify the recorded macro as required. Yo need to modify the command text portion of the query like the code below With Selection.QueryTable .CommandText = Array( _ "SELECT `0215 & 0064 parts received`.ID, & " "WHERE (`0215 & 0064 parts received`.`Date Received`<{ts '2009-03-23 00:00:00'})" End With Below I modified the above code to use a variable date. todayDate = format(Date,"'YYYY-MM-DD) & " 00:00:00'" With Selection.QueryTable .CommandText = Array( _ "SELECT `0215 & 0064 parts received`.ID, & " "WHERE (`0215 & 0064 parts received`.`Date Received`<{ts " & todaydate & "})" End With "Steve Haack" wrote: I am building a workbook to automate reporrt generation for data in Access. I have 40+ locations that I have to generate reports for, so I am creating worksheets for each site, and quereying Access for the specific data. What I would like to do, is on the worksheet for each site, have the data that I need for that site and the specific charts that I need to generate. I am using ListObjects.Add to add a QueryTable object to the worksheet. It query's the access db and gets the data that I need. This part works fine. It gets the data, and creates an xl table on the sheet. My question, is how do I make changes to that query. Let's say that the user wants to change the dates for the data being charted. How do I change that query and bring the new data back into the existing xl table (so that the chart updates itself)? I haven't been able to figure out how to do that. I need to make sure that I can change the query, re-run it and put the data into the previously defined xl table. From what I can tell, the QueryTable object will not put the data into a previously defined table. Any guidance or pointers are greatly appreciated. Steve |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListObjects.Add Question
Joel,
I think this is right on with what I need to do. Do you have a larger code sample that you can point me to so that I can take all this into context better (I'm new at a lot of this). Basically, I need to create the table the first time, then when users choose different dates, I need to re-query the data and update the charts. Thanks for the help, Steve "Joel" wrote: You can't change the connection to a query table but you can change the other properties. I usually record a macro when I start a query and then use then modify the recorded macro as required. Yo need to modify the command text portion of the query like the code below With Selection.QueryTable .CommandText = Array( _ "SELECT `0215 & 0064 parts received`.ID, & " "WHERE (`0215 & 0064 parts received`.`Date Received`<{ts '2009-03-23 00:00:00'})" End With Below I modified the above code to use a variable date. todayDate = format(Date,"'YYYY-MM-DD) & " 00:00:00'" With Selection.QueryTable .CommandText = Array( _ "SELECT `0215 & 0064 parts received`.ID, & " "WHERE (`0215 & 0064 parts received`.`Date Received`<{ts " & todaydate & "})" End With "Steve Haack" wrote: I am building a workbook to automate reporrt generation for data in Access. I have 40+ locations that I have to generate reports for, so I am creating worksheets for each site, and quereying Access for the specific data. What I would like to do, is on the worksheet for each site, have the data that I need for that site and the specific charts that I need to generate. I am using ListObjects.Add to add a QueryTable object to the worksheet. It query's the access db and gets the data that I need. This part works fine. It gets the data, and creates an xl table on the sheet. My question, is how do I make changes to that query. Let's say that the user wants to change the dates for the data being charted. How do I change that query and bring the new data back into the existing xl table (so that the chart updates itself)? I haven't been able to figure out how to do that. I need to make sure that I can change the query, re-run it and put the data into the previously defined xl table. From what I can tell, the QueryTable object will not put the data into a previously defined table. Any guidance or pointers are greatly appreciated. Steve |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListObjects.Add Question
It will be easier for you to see what is happening by strating the macro
recorder (tools - macro - start recording) and then perform the Query from the menu Data - Import External Data "Steve Haack" wrote: Joel, I think this is right on with what I need to do. Do you have a larger code sample that you can point me to so that I can take all this into context better (I'm new at a lot of this). Basically, I need to create the table the first time, then when users choose different dates, I need to re-query the data and update the charts. Thanks for the help, Steve "Joel" wrote: You can't change the connection to a query table but you can change the other properties. I usually record a macro when I start a query and then use then modify the recorded macro as required. Yo need to modify the command text portion of the query like the code below With Selection.QueryTable .CommandText = Array( _ "SELECT `0215 & 0064 parts received`.ID, & " "WHERE (`0215 & 0064 parts received`.`Date Received`<{ts '2009-03-23 00:00:00'})" End With Below I modified the above code to use a variable date. todayDate = format(Date,"'YYYY-MM-DD) & " 00:00:00'" With Selection.QueryTable .CommandText = Array( _ "SELECT `0215 & 0064 parts received`.ID, & " "WHERE (`0215 & 0064 parts received`.`Date Received`<{ts " & todaydate & "})" End With "Steve Haack" wrote: I am building a workbook to automate reporrt generation for data in Access. I have 40+ locations that I have to generate reports for, so I am creating worksheets for each site, and quereying Access for the specific data. What I would like to do, is on the worksheet for each site, have the data that I need for that site and the specific charts that I need to generate. I am using ListObjects.Add to add a QueryTable object to the worksheet. It query's the access db and gets the data that I need. This part works fine. It gets the data, and creates an xl table on the sheet. My question, is how do I make changes to that query. Let's say that the user wants to change the dates for the data being charted. How do I change that query and bring the new data back into the existing xl table (so that the chart updates itself)? I haven't been able to figure out how to do that. I need to make sure that I can change the query, re-run it and put the data into the previously defined xl table. From what I can tell, the QueryTable object will not put the data into a previously defined table. Any guidance or pointers are greatly appreciated. Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
listobject or listobjects sample code | Excel Programming | |||
If ActiveSheet.ListObjects = True ... Not Working | Excel Programming | |||
ListObjects.Add error | Excel Programming | |||
listobjects and querytables | Excel Programming |