Error when adding sheets to Excel workbook from Access VBA

[Guest]

I'm using Office 2003. I have code that worked in Office 2000 which lets me
add a worksheet to an existing Excel file via VBA code within Access, and
then I populate that worksheet. With 2003, I now get the run-time error
-2147417851 (80010105) - Method 'Add' of object 'Sheets' failed. I do have a
reference set to the Excel 11 library.

Does anybody have any hints to fix this? My code is below.

Thanks,
Jeanne

*******************************************
Private Sub ExportToExistingFile(sPath As String, rstExport As DAO.Recordset)
Dim sSheet As String
Dim sCell As String
Dim sWorkbook As String
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet


On Error Resume Next
' Create an object from the XLS file
Set xlBook = GetObject(sPath)
sWorkbook = xlBook.Name

' Get a pointer to Excel's Application object
Set xlApp = xlBook.Parent

' Set object references for the worksheet where we want the data
sSheet = InputBox("Please specify a worksheet name in " & vbCr & _
sPath & "." & vbCr & "The FRT data will be placed in this
worksheet.", _
"Worksheet")
Set xlSheet = xlBook.Worksheets(sSheet)
If Err = 9 Then 'This sheet doesn't exist
On Error GoTo 0 ' declare error
Set xlSheet = xlBook.Sheets.Add '<---Here is where the error is!
xlSheet.Name = sSheet
xlSheet.Activate
sCell = "A1"
GoTo SkipStartCell
End If
xlSheet.Activate

' Specify starting cell
sCell = InputBox("Please specify a starting cell on worksheet " & sSheet
& _
". This must be in A1 format (ie: row number, column letter: A1).", _
"Starting Cell")

SkipStartCell:
' Make Excel visible
xlApp.Visible = False
xlBook.Windows(1).Visible = True

' Go to starting cell
xlSheet.Range(sCell).Select

PopulateExcelSheet rstExport, xlApp

' save and display the workbook
xlBook.Save
xlApp.Visible = True
xlBook.Windows(1).Visible = True

' clean up
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
Set rstExport = Nothing
End Sub

 

[Guest]

Try changing this line:
Set xlSheet = xlBook.Sheets.Add
To:
Set xlSheet = xlBook.WorkSheets.Add

 

[Douglas J Steele]

If I recall correctly, the Add method doesn't actually return a reference to
the sheet that was added. You could try using

xlBook.Sheets.Add
Set xlSheet = xlBook.ActiveSheet

but I'm not 100% certain that's correct.

 

[Guest]

Thanks, but I've already tried that, with the same result.

- Jeanne

 

[Guest]

Thanks Doug, but it still fails the same way. Actually, I started out with
code like this, but found some place on the web that suggested using the
"set" command on the same line.

- Jeanne

 

[Guest]

Here is a similar line of code from a working app:
xlApp.Worksheets.Add.Move after:=xlApp.Worksheets(xlApp.Worksheets.Count)
It adds the worksheet and moves it to the end of the workbook

 

[Guest]

With that code, I now get a 1004 run time error: "Method 'Worksheets' of
object '_Application' failed".

Just out of curiosity, why do you use "xlApp.Worksheets" instead of
"xlBook.Worksheets"?

Thanks again for trying to help!
- Jeanne

 

[Guest]

I use the xlApp construct because I found that in some cases because of how I
was referencing Excel objects from within Access, Access would create an
extra instance of Excel. So when I executed xlApp.Quit, an instance of Excel
would remain and cause problems when a user tried to open a spreadsheeet
within Excel.

 

[Guest]

I have no idea why, but adding the line

xlBook.Windows(1).Visible = True

before the "set xlSheet" line eliminates the error and everything works!

- Jeanne