Do a move copy of a spread sheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jln - 03 May 2007 14:32 GMT

What I have right now in access is a macro that exports 4 spreadsheets into
one workbox. I need to add a move copy of a template file after the
spreadsheets are exported, to the work book that was created from the above
exports.

Reply to this Message

Klatuu - 03 May 2007 19:56 GMT

In that you say you are using a Macro to achieve this, I need to ask how your
VBA skills are. The reason is, it takes VBA coding using Automation to
manipulate the Excel object model to do this.

Signature

Dave Hargis, Microsoft Access MVP

> What I have right now in access is a macro that exports 4 spreadsheets into
> one workbox. I need to add a move copy of a template file after the
> spreadsheets are exported, to the work book that was created from the above
> exports.

Reply to this Message

jln - 03 May 2007 20:21 GMT

Sorry im using VBA to export the 4 files into one and gving them a name based
off of a text box. I would say my VBA skill are avg to alittle above avg.

>In that you say you are using a Macro to achieve this, I need to ask how your
>VBA skills are. The reason is, it takes VBA coding using Automation to
[quoted text clipped - 3 lines]
>> spreadsheets are exported, to the work book that was created from the above
>> exports.

Reply to this Message

Klatuu - 03 May 2007 20:35 GMT

Okay, then.
You will need to use Automation to open the excel file you have created and
the one contraining the template. Then use the Copy method of the Worksheets
object to copy the sheet into the created file in the order you want it.

Signature

Dave Hargis, Microsoft Access MVP

> Sorry im using VBA to export the 4 files into one and gving them a name based
> off of a text box. I would say my VBA skill are avg to alittle above avg.
[quoted text clipped - 6 lines]
> >> spreadsheets are exported, to the work book that was created from the above
> >> exports.

Reply to this Message

MyMel - 04 May 2007 21:04 GMT

Hi,

This is a response on the formun maybe it can assist you. It helped me. Do
a search on the subject line.

Subject: Re: How to export four tables to one excel file? 7/28/2005 12:32
AM PST

By: Nikos Yannacopoulos In: microsoft.public.access.modulesdaovba

Hmm... risky? I would stick with the export approach. The trick is to
use the Range argument of TransferSpreadsheet. Although Acess help says
it is not used in exporting, in fact it does direct the output to a
sheet named after the argument, if no defined range with the same name
exists in the target workbook. Some interesting features of
TransferSpreadsheet:
* if the workbook does not exist, it will create it; if it does, it will
overwrite or create new sheets (see next point)
* if a sheet by the name of the Range argument does not exist, it will
be created; if it does, its contents will be overwritten.
Just make sure you don't use any funny characters in the argument; most
punctuation characters fail to get across, and get converted to
underscores. Alphanumeric and spaces are fine.

Sampel code:

Dim vTable(3) As String
vTable(0) = tlbTable1
vTable(1) = tlbTable2
vTable(2) = tlbTable3
vTable(3) = tlbTable4
For i = 0 to 3
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, _
vTable(i), "C:\Report",True, vTable(i)
Next

This will name the sheets after the tables. If you want different names,
just make the array two-dimensional and define different names.

HTH,
Nikos

Reply to this Message