By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,946 Members | 720 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,946 IT Pros & Developers. It's quick & easy.

VBA Excel paste from access function fails every second time it runs!

P: n/a
I am pasting in a recordset from access to an excel sheet via VBA
successfully. Then my routine copies a range of cells and pastes them
into another range, this works fine every other time it runs. Every
other run of the code it fails at the line Selection.Copy with a
Runtime error 91:. I then run it again and it works fine. This is
really puzzling me a snippet of my code is below. Any ideas?
>>>>>>>>>>>>>>>>>>

ExcelApp.Workbooks.Open ("C:\Test.xls")

Set ExcelSheet = ExcelApp.Worksheets("Sheet1")
ExcelSheet.Range("B13").CopyFromRecordset rs
ExcelSheet.Range("O13:X13").Select
Selection.Copy ' Code fails here every second time it runs with Runtime
error 91:
ExcelSheet.Range(Cells(14, 15), Cells(rec + 12, 24)).Select
ActiveSheet.Paste

ExcelApp.ActiveWorkbook.Save
ExcelApp.Quit

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Try using
ExcelSheet.Selection.Copy

Also be sure to close your ActiveWorkbook before you do your Quit,
and set ExcelApp=Nothing

Once you've run this code, it's a good idea to check in your Task Manager on
the Processes tab to make sure you're not leaving a "zombie" Excel instance.

HTH

<in**@lowerbill.co.uk> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.com...
I am pasting in a recordset from access to an excel sheet via VBA
successfully. Then my routine copies a range of cells and pastes them
into another range, this works fine every other time it runs. Every
other run of the code it fails at the line Selection.Copy with a
Runtime error 91:. I then run it again and it works fine. This is
really puzzling me a snippet of my code is below. Any ideas?
>>>>>>>>>>>>>>>>>>>

ExcelApp.Workbooks.Open ("C:\Test.xls")

Set ExcelSheet = ExcelApp.Worksheets("Sheet1")
ExcelSheet.Range("B13").CopyFromRecordset rs
ExcelSheet.Range("O13:X13").Select
Selection.Copy ' Code fails here every second time it runs with Runtime
error 91:
ExcelSheet.Range(Cells(14, 15), Cells(rec + 12, 24)).Select
ActiveSheet.Paste

ExcelApp.ActiveWorkbook.Save
ExcelApp.Quit

Nov 13 '05 #2

P: n/a
Thanks for the tips. Yes a "zombie" excel instance was being left.
Sorted by ensuring the ActiveWorkbook was properly closed. Also
hooking up to an already running instance when initiating. Solutions
to these problems found on this message board.

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.