Copy/Insert spreadsheet programatically

[dhutch75]

I'm writing VBA code in Access 2003 to copy database values into an Excel 2003 spreadsheet.

If my record count is too high, I'd like to create copies of the active worksheet, "CostModel". Here's a snippet of my code, which generates error #3021 (No Current Record) in Excel when it hits the 'Copy' command line.

Dim xlApp As Object
Dim xlBook As Object
Dim tmpSheetCount As Integer

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("filename.xls")

tmpSheetCount = 5

If tmpSheetCount > 1 Then
For i = 1 To tmpSheetCount
xlBook.Sheets("CostModel").Copy After:=Sheets(0)
Next i
End if


I've actually tried several different methods of adding/copying the original "CostModel" sheet, but all fail with the same error #3021.

[Mavyak]

The Sheets collection begins its index with 1. You are referencing index 0 in your After argument.

[dhutch75]

Thanks for the suggestion, but I was unsure of the starting index number, so I had already tested it with Sheets(1). No luck. I also eliminated the After:=Sheets(0) notation altogether and got the same error. I'm actually going to try to use a variable ( After:=Sheets(i+1) ) in the code when I finally get it working.

[Mavyak]

Is your filename really "filename.xls"? If not, verify that the value being passed/hard-coded is a valid path.