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?
[color=blue][color=green][color=darkred]
>>>>>>>>>>>>>>>>>>>>>>[/color][/color][/color]


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

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

<info@lowerbill.co.uk> wrote in message
news:1118271293.775023.54950@g47g2000cwa.googlegro ups.com...[color=blue]
> 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?
>[color=green][color=darkred]
> >>>>>>>>>>>>>>>>>>>>>>[/color][/color]
>
>
> 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
>[/color]

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.