471,310 Members | 1,090 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,310 software developers and data experts.

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

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
2 6476
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
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.

Similar topics

13 posts views Thread by Allison Bailey | last post: by
6 posts views Thread by Steve Richter | last post: by
2 posts views Thread by John Henry | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.