Exporting to excel Adding new Tab

[greggue]

Hell all,

I appreciate the help in advance.

I'm trying to add a new worksheet to excel and then import the data from a query to this new worksheet. I am able to import the data to the first 3 worksheets but when I get to the 4th one which is not created I get a Subscript out of range error at the line XlBook.Worksheets("Sheet4").Add

Here's part of the code



Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
Dim wrkshtcnt As Integer ' worksheet count

wrkshtcnt = 1
Stop
'Open an instance of Excel, open the workbook.
Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(MySheetPath)
'Make sure everything is visible on the screen.
Xl.Visible = True
XlBook.Windows(wrkshtcnt).Visible = True
XlBook.Windows(wrkshtcnt).Activate

'Define the topmost sheet in the Workbook as XLSheet,


wrkshtcnt = 1

' LOOP

Do While wrkshtcnt < 5

If wrkshtcnt >= 4 Then
XlBook.Worksheets("Sheet4").Add
XlBook.Windows(wrkshtcnt).Activate
XlBook.Windows(wrkshtcnt).Visible = True
End If

Set XlSheet = XlBook.Worksheets(wrkshtcnt)
Select Case wrkshtcnt
Case 1
MySQL = qryDesig
XlSheet.Name = "Designations"
Case 2

MySQL = qryRegMeth
XlSheet.Name = "Registrations Methods"
Case 3
MySQL = qryPracArea
XlSheet.Name = "Practice Areas"
Case 4
MySQL = qryProv
XlSheet.Name = "Province Count"
End Select

'Copy the recordset to worksheet starting at cell B3.
MyRecordset.Open MySQL
XlSheet.Range("B3").CopyFromRecordset MyRecordset
wrkshtcnt = wrkshtcnt + 1
MyRecordset.Close
' END LOOP
Loop



'Clean up and end with worksheet visible on the screen.
Set cnn = Nothing
Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing
EventID = 0

End Sub

[boblarson]

The problem is that your worksheet name does not exist and you are asking it to put the new worksheet before that worksheet.

So, what you can do is to instead just use the worksheet number. Are you wanting the sheets to go before each other or just after the last one?

[alansidman]

Also, look at this blog.

http://accessblog.net/2006/07/export...cel-range.html

Alan

[greggue]

HI Thanks for your help.

I want to add it after.

[boblarson]

HI Thanks for your help.

I want to add it after.

So this code:

' LOOP

Do While wrkshtcnt < 5

If wrkshtcnt >= 4 Then
XlBook.Worksheets("Sheet4").Add

Should be this:

' LOOP

Do While wrkshtcnt < 5

If XlBook.Worksheets.Count < 4 Then
    XlBook.Worksheets.AddNew
End If

[greggue]

Thank you Bob,

This issue is resolved.