Copy an Excel sheet to another workbood with access vba

[Nick]

I'm attempting to copy Excel worksheet 1 in workbook 1 to a worksheet 2
in workbook 2 on click of a button in Access 97 using VBA. I believe I
have the jist of the code worked out, but I'm getting a "Run time error
'438' - Object doesn't support this property or method" error when I
try to test it. here is the code I'm running:

Dim App As Excel.Application
Dim WB1 As Workbook
Dim WS1 As Worksheet
Dim WB2 As Workbook
Dim WS2 As Worksheet
Dim strSB As String
Dim strDB As String
Dim strSS As String
Dim strDS As String
Dim RS As Excel.Range
Dim RD As Excel.Range


strSB = "C:\Deposits.xls"
strDB = "C:\Deposit_Template.xls"
strSS = "depwkst"
strDS = "deposits"

Set App = CreateObject("Excel.Application")
Set WB1 = App.Open(strSB)                        'THIS IS WHERE I GET
THE ERROR
Set WB2 = App.Open(strDB)

RS = WB1.Worksheets(strSS).Range("A6:R2532")
RD = WB2.Worksheets(strDS).Range("A2")

RS.Copy RD

I have the following checked in my References (in this order):
Visual Basic for Applications
Microsoft Access 8.0 Object Library
Microsoft DAO 3.51 Object Library
Microsoft Excel 11.0 Object Library

Any thoughts on how to get past this error?

Thanks,

Nick

 

[DomThePom]

Hi Nick

Running down your code from the top:

* you should prefix all excel object declarations with 'excel.'

* the preferred way of linking into Excel is normally check to see if Excel
is open already (using getobject) and if it is not then creating a new
instance using code like:

On Error Resume Next
Set app = GetObject(, "Excel.Application")
If Err <> 0 Then
Set app = New Excel.Application
End If
on error goto Proc Error

* 'open' is not a method of the excel application object - this is why you
are getting your error - you need to add the workbooks collection so your
code should read

Set WB1 = App.workbooks.Open(strSB)

* The code:

RS = WB1.Worksheets(strSS).Range("A6:R2532")
RD = WB2.Worksheets(strDS).Range("A2")

will not work for 2 reasons:

(a) whenever you are defining an object you need to prefix with a 'set'
(b) you want to copy a sheet not a range

The worksheet object has a copy method which takes one argument which is the
worksheet either before or after you want to put the sheet you are copying.
So your code will look something like:

wb1.worksheets(strss).copy Before:=wb2.worksheets(1)

This will copy the sheet onto the top of the destination workbook.

As a pointer, the easiest way to get familiar with this stuff is to record
your actions in Excel and then copy and paste into access, making sure that
you add the appropriate references (ie 'app.' above) to your excel
application objects in the access environment.

Plus use intellisense within the access environment - ie you can see that
'open' is not a method of the excel application object because it is not in
the list of properties and methods that appear when you enter 'App.'

Hope this helps

Dom

I'm attempting to copy Excel worksheet 1 in workbook 1 to a worksheet 2
in workbook 2 on click of a button in Access 97 using VBA. I believe I
have the jist of the code worked out, but I'm getting a "Run time error
'438' - Object doesn't support this property or method" error when I
try to test it. here is the code I'm running:

Dim App As Excel.Application
Dim WB1 As Workbook
Dim WS1 As Worksheet
Dim WB2 As Workbook
Dim WS2 As Worksheet
Dim strSB As String
Dim strDB As String
Dim strSS As String
Dim strDS As String
Dim RS As Excel.Range
Dim RD As Excel.Range


strSB = "C:\Deposits.xls"
strDB = "C:\Deposit_Template.xls"
strSS = "depwkst"
strDS = "deposits"

Set App = CreateObject("Excel.Application")
Set WB1 = App.Open(strSB)                        'THIS IS WHERE I GET
THE ERROR
Set WB2 = App.Open(strDB)

RS = WB1.Worksheets(strSS).Range("A6:R2532")
RD = WB2.Worksheets(strDS).Range("A2")

RS.Copy RD

I have the following checked in my References (in this order):
Visual Basic for Applications
Microsoft Access 8.0 Object Library
Microsoft DAO 3.51 Object Library
Microsoft Excel 11.0 Object Library

Any thoughts on how to get past this error?

Thanks,

Nick

 

[Nick]

Thanks Dom, that worked well. I see exactly what you mean about
intellisense (I never new the name of it before either). I appreciate
you taking the time to respond.

Nick