Do a move copy of a spread sheet

  • Thread starter Thread starter jln via AccessMonster.com
  • Start date Start date
J

jln via AccessMonster.com

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.
 
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.
 
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.
 
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.
 
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
 
Back
Top