Type mismatch on CopyFromRecordset Access

[Lynch225]

Hey everyone, I am having an issue when exporting a table from Access to Excel. I do not know why I keep getting an error, as I have used this code before. I'm assuming it's an easy fix and I'm just brain dead at this point of the day. Here's the code:

Private Sub Staffing_Click()
 
Dim dbs As DAO.Database
Dim objrst As DAO.Recordset
Dim strXLStaffing As String
Dim strStaff As String
Dim xlApp As Object
Dim xlWorkbook As Object
Dim xlSheet As Object
 
strXLStaffing = "C:\Lynch\Staffing.xlsx"
strStaff = "Daily Staffing List"
 
Set dbs = CurrentDb
Set objrst = Application.CurrentDb.OpenRecordset(strStaff)
 
Set xlApp = CreateObject("Excel.Application")
With xlApp
    .Visible = False
    .DisplayAlerts = False
    Set xlWorkbook = Workbooks.Open(strXLStaffing)
    Set xlSheet = xlWorkbook.Worksheets("Sheet2")
    xlSheet.Activate
    xlSheet.Range("A1").CopyFromRecordset strStaff
End With
 
With xlApp
    .Save
    .Quit
End With
 
Set objrst = Nothing
 
End Sub

I get the Run-time error '13' on the following line ({Edit} Line #24):

xlSheet.Range("A1").CopyFromRecordset strStaff

Any help would be greatly appreciated!

Thanks

[TheSmileyCoder]

Lets see....you want to copy a recordset, but pass a string to the function, and get a TYPE mismatch.

I will just leave that comment hanging and see if you can figure the rest out. If you truly can't, I will give the "solution" but I would much prefer for you to figure it out yourself and learn from it.

[Lynch225]

Ahh yes, of course I was being brain dead. Just changed strStaff in the line to objrst and worked like a charm. Thanks!

Now I will have to revisit the early chapters in my Access 2007 VBA manual...

[TheSmileyCoder]

Good to see you caught it. I do know the feeling of staring myself blind at something, while thinking "grrr, this should be so simple but I just can't catch it".

Best of luck with you project.