Copying data in Excel using Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have thousands to records to put inside a Spreadsheet template (several
sheets, extra calculations, etc). I figured the faster way would be to create
a spreadsheet using TransferSpreadsheet and them copying the data among
Spreadsheets but, how can I access the Copy/Paste from Access? Can I use
Paste Special?

Thanks

Mauricio Silva
 
You can automate EXCEL from within ACCESS, so in general you can use copy
and paste and other commands in VBA code.
 
Could you help me with some examples?
I tried a few and they didn't work.
I need to copy from SheetC to SheetT (different files) the range A2:E50000

I've tried:
SheetC.Copy "A2:E50000"
SheetT.Paste "A2"

and:
SheetC.Select "A2:E50000"
SheetC.Copy
SheetT.Paste "A2"
Thanks
Mauricio Silva
 
You need to use fully qualified references to the ranges. If you didn't set
SheetC and SheetT by code to specific worksheets, then the code steps you're
using won't work.

Try this generic code (I'm going from memory here on how to use Copy and
Paste, so these may be slightly wrong, but note the fully qualified
references):

Excel.Application.Workbooks("Workbookname").Worksheets("SheetNameC").Range("
A2:E50000").Copy
Excel.Application.Workbooks("Workbookname").Worksheets("SheetNameT").Range("
A2:E50000").Paste
 
Hey Ken, Thanks for this

I manage to use the copy command:

Excel.Application.Workbooks("Workbookname").Worksheets("SheetNameC").Range("A2:E50000").Copy

but the only way to use the paste was:
Excel.Application.Workbooks("Workbookname").Worksheets("SheetNameT").Paste

unfortunately, it paste the data where the cursor is (of course, it is never
where we wand it). Would you know a way to move the cursor? Or the correct
way of the Paste command? Or even better, is there a help for this kind of
automation? (Other than MS Access in my computer)

Thanks again

Mauricio Silva
 
Use the Destination argument of the Copy method (next step is meant to be
all one line, so watch for linewrapping):

Excel.Application.Workbooks("Workbookname").Worksheets("SheetNameC").Range("
A2:E50000").Copy
Excel.Application.Workbooks("Workbookname").Worksheets("SheetNameT").Range("
A2:E50000")
 
As far as help outside of Access go to www.msdn.microsoft.com and paste in
the code. You may get a lot of references to other Microsoft products. But
usually code will bring up what you want.

You could also try a Google search.

Mark
 
Back
Top