Creating new Excel Application problem

[Rishi Dhupar]

Hi,
Before this code is run, I have two workbooks open (One where all
the code and forms are, and the other is just a database of
information). I am trying to create a report from my application using
a template (.xlt file) I created. The code below is just a test to see
if my copying and pasting between two Excel application objects is
working, but it isn't. On the set tempRow = ... I keep getting a Run
Time error 1004 "application-defined or object-defined error"

Anyone got any idea how to fix it?

Thanks

Dim currTemplate As String
currTemplate = MyPath & BACKSLASH & templateContainer & BACKSLASH &
Template1

Set tempApp = CreateObject("Excel.Application")
Set tempWB = tempApp.Workbooks.add(currTemplate)
Set tempWS = tempWB.Sheets(1)

tempApp.Visible = True

For i = 3 To 10
'db.Worksheets("dbdata").Select
Set foundRow = db.Worksheets("dbdata").Range(Cells(i, 1),
Cells(i, 5))
Set tempRow = tempWB.Worksheets(1).Range(Cells(i, 1), Cells(i,
5))
Debug.Print foundRow.Address
Debug.Print tempRow.Address
foundRow.Copy
tempRow.PasteSpecial
Next i

 

[Rishi Dhupar]

FYI, I didn't copy the part where I do dim foundrow and temprow as
ranges

 

[Jim Rech]

The problem with this is that Cells is unmodified so it refers to the active
sheet. It would work only if the worksheet is active.

Set tempRow = tempWB.Worksheets(1).Range(Cells(i, 1), Cells(i, 5))

Instead try this:

I'll use tempWS since you do create it:

With tempWS
Set tempRow = .Range(.Cells(i, 1), .Cells(i, 5))
End With

--
Jim
| Hi,
| Before this code is run, I have two workbooks open (One where all
| the code and forms are, and the other is just a database of
| information). I am trying to create a report from my application using
| a template (.xlt file) I created. The code below is just a test to see
| if my copying and pasting between two Excel application objects is
| working, but it isn't. On the set tempRow = ... I keep getting a Run
| Time error 1004 "application-defined or object-defined error"
|
| Anyone got any idea how to fix it?
|
| Thanks
|
| Dim currTemplate As String
| currTemplate = MyPath & BACKSLASH & templateContainer & BACKSLASH &
| Template1
|
| Set tempApp = CreateObject("Excel.Application")
| Set tempWB = tempApp.Workbooks.add(currTemplate)
| Set tempWS = tempWB.Sheets(1)
|
| tempApp.Visible = True
|
| For i = 3 To 10
| 'db.Worksheets("dbdata").Select
| Set foundRow = db.Worksheets("dbdata").Range(Cells(i, 1),
| Cells(i, 5))
| Set tempRow = tempWB.Worksheets(1).Range(Cells(i, 1), Cells(i,
| 5))
| Debug.Print foundRow.Address
| Debug.Print tempRow.Address
| foundRow.Copy
| tempRow.PasteSpecial
| Next i
|

 

[Rishi Dhupar]

Brillaint. Never would have thought of that.

thanks a lot.