macro problem with cut and paste and queries

mikeco

New Member
Joined
Sep 17, 2002
Messages
5
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).
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

mikeco

New Member
Joined
Sep 17, 2002
Messages
5
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

Active Member
Joined
Feb 15, 2002
Messages
337
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

New Member
Joined
Sep 17, 2002
Messages
5

ADVERTISEMENT

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

Board Regular
Joined
Apr 10, 2002
Messages
236
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

New Member
Joined
Sep 17, 2002
Messages
5
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,400
Messages
5,601,467
Members
414,452
Latest member
Dannysamworth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top