Paste four queries to specific places in open spreadsheet (1 Viewer)

Hi,

I have a button in my Access database that runs four queries and displays the results. The queries are designed in such a way that I know in advance how many rows and columns they will have and this won't change. The queries are named "qryExec01", "qryExec02" etc.

I have an Excel spreadsheet called "Exec and RDMs report.xlsx" which contains a sheet called "Main Setup sheet". I have prepared certain areas of that sheet to hold the output of my four queries (then other sheets feed off that). But at the moment I am copying and pasting manually. My plan is to replicate this approx 30 more times so I need to automate it.

I paste qryExec01 to cells B601:E632. I paste qryExec02 to cells C685:J701. And other predefined areas for the others. In each case I overwrite what was there before.

The full path of the spreadsheet is G:\Database\Exec and RDMs Report.xlsx and I don't ever need to change this, the name of the worksheet or the locations of the cells within it.

The spreadsheet will already be open as I will be doing other things with it, and then grabbing data from my database. So ideally, I don't want to have to close it before clicking the button. There may be other Excel workbooks open at the same time, just in case that complicates things.

Given the set-up as described, can someone tell me the code to put on my button's OnClick event?

Thank you.

After a quickie search for this topic through the Office support and MSDN sites, the best answer I can manage is this:

1. Open an Excel Application Object. Use VBA-based automation to open your workbook file. Select the worksheet.

2. Build queries with desired formatting for each field.

3. For each query, open a recordset to the query. Do a .MoveFirst

4. Establish base/offset numbering for the Excel row and column. Then, for each field in the recordset, store the field in the appropriate cell using syntax that will resemble

ActiveSheet.Row(row-base+row-offset).Column(col-base+col-offset) = recordset.fieldname

You would have one line like that for each field, where the row-base and col-base are constants for any one recordset, but the row-offsets increment when you do a .MoveNext of the recordset. First record/row is offset 0, second is offset 1, etc.

The col-offset corresponds to the position of the field in the recordset. First field is offset 0, second field is offset 1, etc. (There is actually a way to do this to make it perfectly general, because you have access to the Recordset.FieldDefs collection so could look up a field number, but it probably would only confuse the issue.)

When you reach .EOF condition on the recordset, you are done with it. Close it, open the next query, update the row-base and col-base. Lather, rinse, repeat.

When you've done the last query, use VBA to tell Excel to save and close the workbook.

Unfortunately, there are a myriad ways to selectively IMPORT data from an Excel range to an Access table or query, but to EXPORT from Access to Excel, you cannot specify a range argument. (Drat the bad luck...)

paste this code into a module.
you must make an instance of Excel that Access can control.
The target file cannot be open beforehand.
and you may need the Excel reference.

I don't know Excel very well, but can't you connect to live data from the Excel file itself, and not have to push-update a file? Can't the Excel file, when you open it, requery the data directly from the .mdb or .accdb file and save you having to routinely perform a data moving type of operation?

Thank you all for your assistance. I may have given the impression that I generally know what I'm talking about - if so, that was my mistake!! Actually, I didn't understand an awful lot of that. I'm not an Access or VBA developer at all, but someone who knows just enough to be dangerous!

BUT... I have made some progress and learned some things.

In the macro, I assumed "range" wanted me to specify a range of cells but was puzzled there was nowhere to add the sheet name. That attempt resulted in "This table contains cells that are outside of the range of cells defined in this spreadsheet."

Thinking back to my manual process and realising I paste in just one cell, without selecting the range first, I tried just B601 as the range and this time it created a new sheet called _B601. OK, so "range" seems to be the sheet name and there is nowhere to specify the range of cells.

But this does work, and it seems not to care whether the Excel sheet is open or closed. It just works!

If I just had those four queries I could operate like this, pasting them to four separate setup sheets, which would then feed into the required areas in my formatted report. But I need about 35 of them and that seems unwieldy.

I'll try to get my head round the offset method you mentioned. But it's approaching midnight on a cold winter's night in the UK and that battle is for another day.

Ranman
Your approach looks incredibly compact and elegant. Can you explain more about this?

My G: drive is actually a 32MB flash drive, and I have previously figured out (though I forget the details) how to have it load as G on both the laptop I take from hospital to hospital in my clinical research job, and the main desktop computer I use in my base office. I'm not always connected to a specific server but keeping things on this G drive means I can be flexible.

I have no idea what a UNC path is I'm afraid. But I think the references are OK. I've attached a screenshot - does this look right?

MarkK
Very possibly. That's an approach I hadn't thought of and have never attempted but it could well work.

Basically what I am doing in this Excel file is bringing data in from several sources, including Access queries, Excel outputs that have been created by other systems (with formatting errors!) and other Excel files that are emailed to me.

I get all this data into three set-up sheets in Excel and these in turn update other sheets in the same xlsx which are formatted as final reports. The whole thing works pretty well but I'm copying/pasting manually it's time consuming.

I have just now tried this method and the query I wanted to use did not appear in the list of available queries. Maybe because it's a crosstab. I will experiment with running a select query off that crosstab, or possibly a maketable query and then using that.

A standard select query worked just fine though and maybe another approach is to use COUNTIFS to do my calculations directly in Excel.

Anyway...need sleep!! May be a few days before I get a chance to come back to this.

Thanks for your help so far everyone.

the Excel reference allows Access to use Excel as an app. Everything you can do in excel can be controlled via code IF Access has this reference loaded.