excel object (worksheet)

[toon10]

Hi

I have a routine which I want to open an existing spreadsheet, switch to a specific named worksheet and then paste the contents of a recordset into the sheet.

The recordset has been created successfully but I'm having trouble switching to a specific sheet. It opens the correct spreadhseet but this line produces an object required error. The sheet is called Switchon

"Set Worksheet = ExcelSheet.Sheets("Switchon").Select"

Any idea's would be appreciated.

My code:

Private Sub cmdExcel_Click(Index As Integer)
On Error GoTo ErrorHandler:
Dim SQLswitchon As String
Dim Msg As String
Dim i As Integer
Dim j As Integer
Dim TotalColumns As Integer
Dim Excel As Excel.Application
Dim Worksheet As Excel.Worksheet
Dim RecCount As Integer

SQLswitchon = "SELECT * from switchon ORDER BY S1TestDate" 'create recordset
Open_cn 'open a connection to the database
Set rsExecute = New ADODB.Recordset
rsExecute.Open SQLswitchon, cn, adOpenStatic, adLockOptimistic, adCmdText 'set rsExecute to switchon query

Set Excel = CreateObject("Excel.Application")

With Excel.Application
    .Visible = True
    .Workbooks.Open ExcelFile
    .WindowState = xlMaximized
End With

Set Worksheet = ExcelSheet.Sheets("Switchon").Select 'set worksheet to switchon
Worksheet.Columns("A:B").Select
ExcelSheet.Selection.ClearContents 'delete contents of 1st 2 columns
Worksheet.Range("A1").Select

For j% = 1 To rsExecute.Fields.Count
        With Worksheet.Cells(1, j%)
            .Interior.ColorIndex = 1 'sets the background colour of the header to black
            .Font.ColorIndex = 2 'sets the foreground text of the header to white
            .Font.Bold = True 'sets the header to bold
        End With
        Worksheet.Cells(1, j%) = rsExecute.Fields(j% - 1).Name 'copy the recordset information to the cells in the spreadsheet
        RecCount = rsExecute.RecordCount + 2
Next j%

TotalColumns% = j% - 1

With Worksheet
    .Range("A2").CopyFromRecordset rsExecute 'insert recordset data into cells
    .PageSetup.Orientation = xlLandscape
    .PageSetup.PrintArea = ""
    .PageSetup.FitToPagesWide = 1
    .PageSetup.FitToPagesTall = 100
    .PageSetup.TopMargin = 1
    .PageSetup.BottomMargin = 1
    .PageSetup.LeftMargin = 1
    .PageSetup.RightMargin = 1
    .PageSetup.HeaderMargin = 1
    .PageSetup.FooterMargin = 1
    .PageSetup.Zoom = False
    .Range("B2").Select
    .Activate 'launch worksheet
End With

Set Excel = Nothing 'dispose of excel object
Close_cn
Exit Sub

ErrorHandler:
    'If Err.Number = 0 Or Err.Number = 91 Then
    '    Resume Next
    'End If

    If Err.Number = 6 Then 'overflow
        MsgBox "Too much information for Excel to load.", vbCritical, "An error occurred populating Microsoft Excel."
        Exit Sub
    End If

    Screen.MousePointer = vbDefault
    MsgBox Err.Number & " " & Err.Description, vbCritical, "An error occurred populating Microsoft Excel."
    Close_cn
    Set Excel = Nothing
    Exit Sub
End Sub

 

[JerryKlmns]

I would change these

Dim Excel As Excel.Application
Dim Worksheet As Excel.Worksheet

to

Dim objExcel Excel.Application
Dim objBook As Excel.Workbook
Dim objSheet As Excel.Worksheet

Then

Set objExcel = CreateObject("Excel.Application")
With objExcel 
    .Visible = True
    .Workbooks.Open ExcelFile
    .WindowState = xlMaximized
End With

Set objBook = objExcel.Workbooks.Item(1)
Set objSheet = objBook.Worksheets("Switchon")
...
...
...
Set objSheet = Nothing
Set objBook = Nothing
objExcel.Quit
Set objExcel = Nothing

 

[toon10]

That's the trick.

Thanks, much appreciated