macro problem with cut and paste and queries

[mikeco]

I have a workbook with a main sheet and several data sheets. Each data sheet has a single parameterized query that returns data for a business. I have the queries setup so they all use the same parameter, and the parameter value is taken from a cell on the main sheet. By changing this parameter cell, all the queries recalculate and return data for the business I'm interested in. This works fine manually.

Now I want to run a macro and that creates a "report" for each business. I run the macro recorder and carefully record cutting and pasting the name of each business into the parameter cell and and saving the results in a new workbook.

When I play the mactro,
the cutting and pasting and saving all takes place ok, but none of the queries "fire". Each "report" has the same data (for the last business) instead of the actual correct individual business data.

I have the parameters for my queries set to "Get the Value from the following cell", and then have specified the cell and also have checked to "Refresh automatically when the cell value changes".

When the macro cut and pastes into the parameter cell, the queries don't run except for the very last cut and paste.

Here is an extract from the macro:

Range("I6").Select
Selection.Copy
Range("I2").Select
ActiveSheet.Paste
Sheets("Main Sheet").Select
Application.CutCopyMode = False
Sheets("Main Sheet").Copy
ChDir "C:Business"
ActiveWorkbook.SaveAs Filename:= _
"C:BusinessBus1.xls", _
FileFormat:=xlNormal, Password:="",
WriteResPassword:="",
_ReadOnlyRecommended:=False,
CreateBackup:=False
ActiveWindow.Close
Range("I7").Select
Selection.Copy
Range("I2").Select
ActiveSheet.Paste
Sheets("Main Sheet").Select
Application.CutCopyMode = False
Sheets("Main Sheet").Copy
ActiveWorkbook.SaveAs Filename:= _
"C:BusinessBus2.xls", _
FileFormat:=xlNormal,
Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Range("I8").Select
Selection.Copy
Range("I2").Select
ActiveSheet.Paste
Sheets("Main Sheet").Select
Application.CutCopyMode = False
Sheets("Main Sheet").Copy
ActiveWorkbook.SaveAs Filename:= _
"C:BusinessBus3.xls", _
FileFormat:=xlNormal,
Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close


It only seems to activate the queries for the last cut and paste. How do I get the macro to run the queries on the other cut and pastes? Is there a different pattern I can use instead of cut and paste? Thanks

p.s. I'm using Excel 2000 (9.0.3821 SR-1).

 

[zzydhf]

Are you doing queries or look-ups?

 

[mikeco]

They are queries. They were defined from
Data>Get Exeternal Data>New Database Query...
In the query itself I used a criteria...
Business
[enter a business name]

where the brackets signal a parameter.

It all works manually by cutting and pasting. It doesn't work in the macro except the last value cut and pasted (in the macro) will cause the queries to recalc when the macro finishes.

 

[Ziggy]

Hi Mike,

I don't see a reference to a query (.dqy file extension)in your code. I think somewhere in your recording process you would have selected the query to run. Plus you want to use a variable from your main sheet so that needs to be declared I would think. You might be able to remove the parameter and have the code filter based on the main sheet variable.

I have macro's recorded to run queries saved through MS Query (same as you describe)but I'm not sure of your whole process.

Ziggy

 

[mikeco]

I thought since I didn't need to do this in manual mode that I wouldn't need to as well inside the macro.

In manual mode, all my queries depend on a single parameter. This location of the parameter value is specified to excel for each data sheet and is the same. It was done by right clicking the mouse, and doing Parameters>Get the value from the following cell, specifying the cell, and checking the box: Refresh automatically when the cell value changed.

This seems to have no effect when running the macro. When the cut and paste commands are executed inside the macro, they change the value in my parameter cell, but none of my queries refresh. Or...I should say only the last cut and paste value put into the cell when the macro finishes causes the queries to recalculate.

Is your advice to explicity invoke the queries inside the macro?

 

[zzydhf]

with your macro, select a cell in the query adn run this code:

Selection.QueryTable.RefreshBackgroundQuery:=False


_________________
Oh, you hate your job? Why didn't you say so? There's a support group
for that. It's called EVERYBODY, and they meet at the bar. - Drew Carey
This message was edited by zzydhf on 2002-09-19 11:51

 

[mikeco]

Hi,

OK, I wrote a little subroutine that explicitly selects each data sheet and query and then does the command you suggested. I put this into my other macro so that after I cut and paste the value into my parameter cell, it calls this little refresh subroutine.

It works!!!
Thanks