Variable to reference a spreadsheet from Access

[lawdy]

I have button on an Access form that opens an existing Excel spreadsheet. All the code is listed below. But the one thing I am having trouble is the following line: "Worksheets("2015").Range("A5").CopyFromRecord set rst". Is there anyway the use variables instead of the "2015" and the "A5" which is the worksheet name and range?


Private Sub btnAddToExcel_Click()
Dim strYear As String
Dim fn As String
Dim ln As String
Dim ph As String


Dim db As Database
Dim rst As Recordset
Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset("Customers", dbOpenDynaset)

Dim objXLApp As Object
Dim objXLBook As Object
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("C:\Dev\P&L.xls")
strYear = "2014"
With rst
fn = ![FirstName]
ln = ![LastName]
ph = ![Phone]
Worksheets("2015").Range("A5").CopyFromRecordset rst
End With
objXLApp.Application.Visible = True
End Sub

[June7]

Yes. Try it. How do you want to set the variables?

[lawdy]

The variable would come from another Access sub as a public variable like pubNum = 2015.

[lawdy]

I have already tried it. It don't work. I added the next 2 lines-
Dim strYear As String
strYear = "2015"
and changed the following line as shown below.
Worksheets(strYear).Range("A5").CopyFromRecordset rst

I run the code, the first time it opens the spreadsheet but inserts NO data. Push the button again and I get -
Run Time Error '1004: Method 'Worksheets' of object - Global Failed.

Any ideas?

[June7]

The code works for me.

However, the workbook must already exist with a worksheet named '2015'.

If you need the worksheet created, that is another issue.

[John_G]

Try using objXLBook.Worksheets(strYear).Range("A5").CopyFrom Recordset rst

I have found that Excel can be very fussy about not having qualifiers.

In your case, it doesn't know which workbook Worksheets refers to, unless you specifically tell it which one.

[lawdy]

'Worksheets("2015").Range("A5").CopyFromRecordset rst' works and there is a worksheet named "2015". However, it does not work when I try to use a variable as:
Dim strYear As String
strYear = "2015"
and changed the following line as shown below.
Worksheets(strYear).Range("A5").CopyFromRecordset rst

I run the code, the first time it opens the spreadsheet but inserts NO data. Push the button again and I get -
Run Time Error '1004: Method 'Worksheets' of object - Global Failed.

Any help is appreciated.

[June7]

As previously noted in post 5: If you need the worksheet created, that is another issue.

[lawdy]

It is already created.

[June7]

Okay, also noted the technique works for me. Don't know why your code fails. If you want to provide files for analysis, follow instructions at bottom of my post.