Solved: Update Excel Query Programatically

[dhutch75]

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.

[vba]wkbShipMstr.Sheets("Shipping").Range("shpJobNo") = sJobNo
Application.Range("shpJobNo").Dirty
Calculate
[/vba]

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

[lucas]

Just a wild guess:
[VBA]sJobNo = wkbShipMstr.Sheets("Shipping").Range("shpJobNo")[/VBA]

[dhutch75]

Thanks, Steve, but I'm not having trouble setting the values. All of that works properly.

My issue is related to the query that SHOULD refresh itself after the job number in Range("shpJobNumber") is updated. If I edit the job number manually, the query updates. If I programatically update the job number with VBA, as shown in my code snippet, Excel doesn't perceive that the cell has changed and thus, doesn't refresh the query data.

[lucas]

Deborah, I usually don't use queries so I'm wondering if you need to refresh the query?

[dhutch75]

You're right. Most often, the trick is knowing the "majic word". I needed to 'refresh' rather than 'update'. The syntax that solves my issue is:
[VBA]wkbShipMstr.Sheets("QAData").QueryTables(1).Refresh[/VBA]

Thanks.

[lucas]

Sometimes I luck out. I often offer suggestions or ask questions and sometimes it clicks for everyone......glad you worked it out.