I don't speak queries, but can't you set the .BackgroundQuery = False and have
excel wait for it to be refreshed?



JonR wrote:

Hi

I hav e aspreadsheet that I would like to update every time it is opened. It runs multiple queries to an external database, which fill in the current data. Afterward, I have one cell which the update MS Query function deletes the content and I have to type the name ("unassigned") manually to keep the graphs from having a blank in the legend. Unfortunately, the word "Unassigned" is placed into the appropriate cell before the data is updated, and is subsequently erased during the update process. Is there a way I can adjust the timing of these events, or add a second subroutine to perform the correct actions in sequence? Code below.
TIA

Sub Auto_Open()
'
' Refresh Macro
' Macro recorded 7/21/2004 by JonR
'

'
ActiveWorkbook.RefreshAll
Sheets("SR List Detail DATA & CHARTS").Select
Range("E5").Select
ActiveCell.FormulaR1C1 = "Unassigned"

End Sub

--

Dave Peterson

I put a web query into a test worksheet.

I rightclicked on the range and selected "Data Range Properties"

There's a checkbox named: "Enable background refresh"

It was checked (to allow refreshes in the background).

(I don't speak the database either--so I didn't test that.)

JonR wrote:

What the spreadsheet does is imports external data from an Access database. I'm a novice at VB, and I've never seen the BackgroundQuery function. It seems that it only works for pivot tables. My workbook, amazingly enough for its size, does not have any pivot tables. I am importing raw external data through a series of roughly twenty external links. The code is the result of recording a macro that clicks the "Refresh All External Data" button and then selects the appropriate cell and types "Unassigned" into it.

"Dave Peterson" wrote:

I don't speak queries, but can't you set the .BackgroundQuery = False and have
excel wait for it to be refreshed?



JonR wrote:

Hi

I hav e aspreadsheet that I would like to update every time it is opened. It runs multiple queries to an external database, which fill in the current data. Afterward, I have one cell which the update MS Query function deletes the content and I have to type the name ("unassigned") manually to keep the graphs from having a blank in the legend. Unfortunately, the word "Unassigned" is placed into the appropriate cell before the data is updated, and is subsequently erased during the update process. Is there a way I can adjust the timing of these events, or add a second subroutine to perform the correct actions in sequence? Code below.
TIA

Sub Auto_Open()
'
' Refresh Macro
' Macro recorded 7/21/2004 by JonR
'

'
ActiveWorkbook.RefreshAll
Sheets("SR List Detail DATA & CHARTS").Select
Range("E5").Select
ActiveCell.FormulaR1C1 = "Unassigned"

End Sub

--

Dave Peterson



--

Dave Peterson

The background refresh works didn't work the way you expected, but it was a good try. I did find a solution. I ended up turning off the background refresh for the particular troublesome query, then re-wrote the VB code:

Sub Auto_Open()
'
' Refresh Macro
' Macro recorded 7/21/2004 by JonR

Sheets("SR List Detail DATA & CHARTS").Select
Range("E5").Select

ActiveWorkbook.RefreshAll

If IsEmpty(Range("E5")) Then

Sheets("SR List Detail DATA & CHARTS").Select
Range("E5").Select
ActiveCell.FormulaR1C1 = "Unassigned"

End If

End Sub

Don't know if the background refresh being on or off has anything to do with it, but right now it works and after hours of head-banging I'm not about to mess with it.

"Dave Peterson" wrote:

I put a web query into a test worksheet.

I rightclicked on the range and selected "Data Range Properties"

There's a checkbox named: "Enable background refresh"

It was checked (to allow refreshes in the background).

(I don't speak the database either--so I didn't test that.)

JonR wrote:

What the spreadsheet does is imports external data from an Access database. I'm a novice at VB, and I've never seen the BackgroundQuery function. It seems that it only works for pivot tables. My workbook, amazingly enough for its size, does not have any pivot tables. I am importing raw external data through a series of roughly twenty external links. The code is the result of recording a macro that clicks the "Refresh All External Data" button and then selects the appropriate cell and types "Unassigned" into it.

"Dave Peterson" wrote:

I don't speak queries, but can't you set the .BackgroundQuery = False and have
excel wait for it to be refreshed?



JonR wrote:

Hi

I hav e aspreadsheet that I would like to update every time it is opened. It runs multiple queries to an external database, which fill in the current data. Afterward, I have one cell which the update MS Query function deletes the content and I have to type the name ("unassigned") manually to keep the graphs from having a blank in the legend. Unfortunately, the word "Unassigned" is placed into the appropriate cell before the data is updated, and is subsequently erased during the update process. Is there a way I can adjust the timing of these events, or add a second subroutine to perform the correct actions in sequence? Code below.
TIA

Sub Auto_Open()
'
' Refresh Macro
' Macro recorded 7/21/2004 by JonR
'

'
ActiveWorkbook.RefreshAll
Sheets("SR List Detail DATA & CHARTS").Select
Range("E5").Select
ActiveCell.FormulaR1C1 = "Unassigned"

End Sub

--

Dave Peterson



--

Dave Peterson