dhutch75
01-06-2009, 03:55 PM
I would like to be able to update Query data on an Excel Spreadsheet programatically.
The query is on a MasterShippingTemplate.xlt. That template is opened by a number of other 'job' spreadsheets via a command button backed by VBA code. The button on the Job sheet opens a Shipping workbook from the Shipping template file. Next, it writes job specific data to the Shipping Form that was created.
The Shipping form (and template) has a parameter query that is linked to a cell in the Shipping form. The cell contains a Job#. When the Job# cell changes, the query automatically refreshes and retrieves a list of part numbers that is associated with the job from an external database. All cell references between workbooks are managed by using Range names.
All of it works as intended, if I manually change the Job# on the spreadsheet. I cannot get the query to update when I write the new job number to the Shipping Form with VBA.
Even though the Job# cell has changed, it does not trigger the update to the query. I tried marking the Job# cell as Dirty after writing to it. No joy. I also issued a Calculate command after making the cell Dirty. Still no luck. The code below is located in the Job sheet.
wkbShipMstr.Sheets("Shipping").Range("shpJobNo") = sJobNo
Application.Range("shpJobNo").Dirty
Calculate
As I said, the query updates automatically if I change the Job# manually, but not if I edit the Job# with VBA. Any suggestions?
Thanks, Deborah
The query is on a MasterShippingTemplate.xlt. That template is opened by a number of other 'job' spreadsheets via a command button backed by VBA code. The button on the Job sheet opens a Shipping workbook from the Shipping template file. Next, it writes job specific data to the Shipping Form that was created.
The Shipping form (and template) has a parameter query that is linked to a cell in the Shipping form. The cell contains a Job#. When the Job# cell changes, the query automatically refreshes and retrieves a list of part numbers that is associated with the job from an external database. All cell references between workbooks are managed by using Range names.
All of it works as intended, if I manually change the Job# on the spreadsheet. I cannot get the query to update when I write the new job number to the Shipping Form with VBA.
Even though the Job# cell has changed, it does not trigger the update to the query. I tried marking the Job# cell as Dirty after writing to it. No joy. I also issued a Calculate command after making the cell Dirty. Still no luck. The code below is located in the Job sheet.
wkbShipMstr.Sheets("Shipping").Range("shpJobNo") = sJobNo
Application.Range("shpJobNo").Dirty
Calculate
As I said, the query updates automatically if I change the Job# manually, but not if I edit the Job# with VBA. Any suggestions?
Thanks, Deborah