exporting data to excell

[Brandon]

I am trying to run a macro to export the results of a query to an excell
spreadsheet. I know you can specify the path of the file to output the file
to, but can you specify which cell to start in? What I really want to do is
have the spreadsheet set up with some formulas and based on the data coming
in from access, have it automatically show some totals from this data, is
there an easier way?

 

[Ken Snell \(MVP\)]

Cannot be done via a macro using TransferSpreadsheet. Can't be done via VBA
code using TransferSpreadsheet, either.

This will require you to use Automation -- use VBA code to create an EXCEL
application instance, open an EXCEL file, and then have the code write the
data directly onto the spreadsheet page in that file, starting with
whichever cell you wish.

 

[Ken Snell \(MVP\)]

Let me add to my post.

Many posters have reported that they've successfully used the "Range"
argument for the TransferSpreadsheet action (macro or VBA) when exporting to
an EXCEL file -- they use the Range argument to specify the worksheet and
cells. However, the "Range" argument is an undocumented feature, and I've
found it to be inconsistently successful. Thus, I do not rely on it.

 

[Phil]

I solved the problem a little differently. Export your data query to a
spreadsheet, build all of your formulas, etc. Save the results. Then,
export the query again, to another spreadsheet, (your DATA spreadsheet.)
Now, go into your first spreadsheet, (the master spreadsheet,) and
replace all of the data, with references to the data in the other
spreadsheet. Basicaly, your data will be a table running from a:1 to
n:2000, (if your data will vary in record count, jsut go WAY over). So
in the master spreadsheet, a:1 references a:1 in the other spreadsheet.
In YOUR case, since you want the data to show up beginning somewhere
other than A:1, adjust accordingly. Make sure you remove the dollar
signs, then just copy a:1 to a:1-n:2000. Again, adjust according to
yoru own needs.

Save your master spreadhseet, and you are done. From now on, all you
have to do is:
Export your dataquery to a file, making sure you use the same name and
location every time.
Open your MASTERspreadsheet, and when it asks you if you wish to refresh
the linked data, say yes.

Make sense?

 

[Phil]

I solved the problem a little differently. Export your data query to a
spreadsheet, build all of your formulas, etc. Save the results. Then,
export the query again, to another spreadsheet, (your DATA spreadsheet.)
Now, go into your first spreadsheet, (the master spreadsheet,) and
replace all of the data, with references to the data in the other
spreadsheet. Basicaly, your data will be a table running from a:1 to
n:2000, (if your data will vary in record count, jsut go WAY over). So
in the master spreadsheet, a:1 references a:1 in the other spreadsheet.
In YOUR case, since you want the data to show up beginning somewhere
other than A:1, adjust accordingly. Make sure you remove the dollar
signs, then just copy a:1 to a:1-n:2000. Again, adjust according to
yoru own needs.

Save your master spreadhseet, and you are done. From now on, all you
have to do is:
Export your dataquery to a file, making sure you use the same name and
location every time.
Open your MASTERspreadsheet, and when it asks you if you wish to refresh
the linked data, say yes.

Make sense?

 

[Brandon]

Thanks Phil for the suggestion. I actually had thought about this, but never
set down to map it all out. Just to clarify for myself though, are you using
one worksheet in the workbook for the master and a second worksheet in the
same workbook as the data range? Or are you using two separate workbooks and
just linking to the other file name, (if you can do that, I've never tried).
My thoughts were to use the same workbook, but two worksheets within it.
Also, your talking about just exporting the results of my query manually,
copying and pasting to the worksheet, instead of using a macro, correct? Or
can this be done through a Macro?

Thanks again....Brandon

 

[Phil]

I use two different workbooks. You can do it either way, (using
transfer spreadsheet will put a sheet into an existing workbook,)
however, I do not know what happens if you try to insert a worksheet
with the same name as the one that is already in there, and don't know
how to delete a worksheet out of a workbook. I can have a macro delete
a workbook, no problem. As far as how to do it, just open both
workbooks up in excel, and point and click. Excell will add the file
name and tuff for you automatically.

No, you export the results of the query with a macro, to the data
workbook. Then when you open the MasterWorkbook, simply updating the
links will "bring in" the data automagicly. Anytime you open a
spreadsheet linked to external data, it asks you if you wish to update
the links. This will be ideally a two step process, once the access
database is open. Click to run the macro, (which will automatically
open the Masterspreadsheet when you are done,) and hit YES to the
"update links" question. You coudl even use SendKeys to get around this...