Copying a query (1 Viewer)

[bgcogen]

Hi.
Basically I have to write a macro that opens a specific query and then copies all the records onto the clip board.

It's the only way of doing it. Does anyone have an idea of what the code would be like.

E.g. Opening a query in VBA etc..

Thanks,
D

 

[Rich]

What exactly do you want to do with the copied records?

 

[bgcogen]

Copied

Hi,
Basically I have to send the records in the query into an Excel worksheet so calculations can be performed on the data. The Formulaes are on sheet 2 and the query data is on sheet 1. I tried to link the query and the worksheet, but that doesn't work; anytime I update the query the spreadsheet isn't updated.

I also tried to transfer the query. This creates a new sheet. See the problem is that I can't go messing around with whole sheets as it messes up the formulae references in the other sheet (e.g. Sheet1!D36 ... ) . I've found that the only way is to physically highlight all the records and then special paste them into Excel, that doesn't mess up the formulaes on the other sheet.

When I try "Analyse with Excel" it just creates a new Excel worksheet.

The way I see it, my only option is to write a VB module that opens the query and then copies all the records onto the clip board, then I can simply paste them onto Excel.

I hope you can give me some help on anything

Thanks a million,
D

 

[FUBAR]

Copy paste is a solution...but not usually a good one especially if there are a lot of records that you are transferring from Access to Excel.

I can think of 2 different solutions off the top of my head...one that doesn't need VB at all.

Without VB------------------------------------------------------------------

Use a macro to export the query into the Excel file that you've created. The records in the table will be entered into the spreadsheet in a new sheet that will have the same name as the query name. All you would have to do is switch the formulas from "Sheet1!blah" to "QueryName!blah"

But then again depending on how you are using the Excel file...this might not be the answer you want.


With VB----------------------------------------------------------------------

You can export the query into a new Excel file or use VB to automatically enter the data for you. Afterwards you could create the formulas on the second sheet using VB (you would be creating a new formula sheet every time.)

Hope something in my babble is helpful to you

 

[Emohawk]

Half the fun of Access (Access is fun???) is finding out how most of this stuff actually works yourself. I've been looking (and learning) myself to the solution as this would be a great addition for something I'm writing at the moment.

The solution (using VBA, what can I say, I'm a code monkey) is going to lie using the GetObject or CreateObject methods. Go to the Microsoft knowledge base and search on those topics and you may pull something useful.

Let me know how you go or I'll check back with anything I find.

Good Luck!