I'm writing a report building application in MS Access, at one point in the
program, it's supposed to open two seperate Excel workbooks, and copy data in
selected cells from one workbook to the other. The particular function that
I've written and am having trouble with is included below. Keep in mind that
SourceBook and TargetBook are declared with module scope, and by the time
this function is called the workbooks are already open, and it has been
proven that the program does have access to them correctly.

The problem is occuring where I'm pasting the data into the targetbook. The
error I'm getting is # 1004: PasteSpecial method of Worksheet class failed.
What am I doing wrong here? I've apparently gone code blind and can't see
the obvious.

Private Sub DoReportCopy(ByVal CellRange As String, ByVal PasteCell As String)
On Error GoTo errDoReportCopy
' Select CellRange from source book
SourceBook.Application.Range(CellRange).Select
' Copy selection
SourceBook.Application.Selection.Copy
' Select CellRange in target book
TargetBook.Application.Range(PasteCell).Select
' Paste values
TargetBook.Application.ActiveSheet.PasteSpecial Format:=3, Link:=1,
DisplayAsIcon:=False, IconFileName:=False
Exit Sub
errDoReportCopy:
Log.WriteLog "Error copying data in cell range " & CellRange & " to " &
PasteCell & ".", "SummaryReport.DoReportCopy", Err
End Sub

--
"Why live in the real world adhering to thier rules, when you can live in
code and write your own"

Nevermind. I figured it out. I had to change the function to the following

Private Sub DoReportCopy(ByVal CellRange As String, ByVal PasteCell As String)
On Error GoTo errDoReportCopy
' Select CellRange from source book
SourceBook.Application.Range(CellRange).Copy
' Select CellRange in target book
TargetBook.Application.Range(PasteCell).PasteSpeci al xlPasteValues
Exit Sub
errDoReportCopy:
Log.WriteLog "Error copying data in cell range " & CellRange & " to " &
PasteCell & ".", "SummaryReport.DoReportCopy", Err
End Sub

--
"Why live in the real world adhering to thier rules, when you can live in
code and write your own"


"Magius96" wrote:

I'm writing a report building application in MS Access, at one point in the
program, it's supposed to open two seperate Excel workbooks, and copy data in
selected cells from one workbook to the other. The particular function that
I've written and am having trouble with is included below. Keep in mind that
SourceBook and TargetBook are declared with module scope, and by the time
this function is called the workbooks are already open, and it has been
proven that the program does have access to them correctly.

The problem is occuring where I'm pasting the data into the targetbook. The
error I'm getting is # 1004: PasteSpecial method of Worksheet class failed.
What am I doing wrong here? I've apparently gone code blind and can't see
the obvious.

Private Sub DoReportCopy(ByVal CellRange As String, ByVal PasteCell As String)
On Error GoTo errDoReportCopy
' Select CellRange from source book
SourceBook.Application.Range(CellRange).Select
' Copy selection
SourceBook.Application.Selection.Copy
' Select CellRange in target book
TargetBook.Application.Range(PasteCell).Select
' Paste values
TargetBook.Application.ActiveSheet.PasteSpecial Format:=3, Link:=1,
DisplayAsIcon:=False, IconFileName:=False
Exit Sub
errDoReportCopy:
Log.WriteLog "Error copying data in cell range " & CellRange & " to " &
PasteCell & ".", "SummaryReport.DoReportCopy", Err
End Sub

--
"Why live in the real world adhering to thier rules, when you can live in
code and write your own"