query table indexing - HELP!
This is a good one.
I am taking over a project from another analyst, who has moved out of state. His coding was pretty cool to have two tables on a sheet. Lets call then CurrentReport and LatestReport. The file will eventully be saved to OldReport.xls (with a twist as I will explain). The template always current data into the CurrentReport section and then looks at the link to the OldReport for its LastReport section. The trick is that before the file is saved the LatestReport query table is deleted and reassigned to the CurrentReport range adddress. So when the new report is generated it looks at the OldReport refering to the €œCurrent€ section thinking that is it is the €œLatestReport€ section. Pretty cool. Here is my problem: I am getting a 1004 error €˜Method range of object €˜ error message combined with another error. He used code that rebuilds the datatable range: (error trapping statements removed) €¦Sheets("Corrective Action Record").QueryTables("LatestReport").Delete Sheets("Corrective Action Record").QueryTables("Latest_Report").Delete€¦ then €¦UpdateNameRangeforQT "Corrective Action Record", "CorrectiveActions"€¦ (3 out of 4 tabs querytable(1) ='LatestReport", on sheet("Corrective Action Record" querytable(2) ='LatestReport" and I have a querytable(3)) This used to work before I added the third suery table and had to rebuild the second query table which seemed to put all objects in the wrong order.) Public Sub UpdateNameRangeforQT(sSheetName As String, sRangeName As String) Dim oSheet As Worksheet Dim oQT As QueryTable Dim oRange As Range Dim oName As Name 'get the sheet Set oSheet = ActiveWorkbook.Sheets(sSheetName) 'get the Query table Set oQT = oSheet.QueryTables(1) 'get the range of cells occupied by the query table Set oRange = oQT.ResultRange 'Get the workbook-level name object Set oName = ActiveWorkbook.Names(sRangeName) 'Update what the name object refers to oName.RefersTo = oRange I found out that since I had to modify one of the tables it now resides in position number 2 for that sheet and there is another querytable I use in position Number 3. Yet when I try to reinstate those query tables it jams and gives a does not exist message. I tried : If sSheetName = "Corrective Action Record" Then If sRangeName = "Last_Date" Then Set oQT = oSheet.QueryTables(3) Else Set oQT = oSheet.QueryTables(2) End If Else Set oQT = oSheet.QueryTables(1) End If It still jams. Hmmm How do I reorder the table position in the inding or make the code above work? I could really use sonme help. |
All times are GMT +1. The time now is 03:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com