Problem To Move To 2nd Worksheet

I have a problem with my coding when wants to move all the data to the second worksheet. I try to change the setting to make the output in 2nd worksheet, but it didnt work.

this is my code:

VB Code:

Set objExcel = CreateObject("excel.application") 'Starts the Excel Session
Set objBook = objExcel.Workbooks.Add             'Add a Workbook
Set objSheet = objBook.Worksheets.Item(1)        'Select a Sheet
objExcel.Application.Visible = True
 
 
objExcel.Application.Cells(1, 1).Value = "Kod Kategori BA"
objExcel.Application.Cells(1, 2).Value = "No.BA"
 
 
With objExcel
            
            .Application.Cells(n, 1).Value = ((Trim(DataHRMIS.Recordset.Fields("BACatCd").Value)))
            .Application.Cells(n, 2).Value = ((Trim(DataHRMIS.Recordset.Fields("BANo").Value)))
 
end with

i try to change here, but it still dont work!

VB Code:

Set objSheet = objBook.Worksheets.Item(2)

how can i fix this? anyone plz help me...tq

Moved from Classic VB forum. :)

Your not using the book or sheet objects and running everything off ogf the application object which is incorrect. ;)

VB Code:

Set objExcel = CreateObject("excel.application") 'Starts the Excel Session
    Set objBook = objExcel.Workbooks.Add             'Add a Workbook
    Set objSheet = objBook.Sheets.Item(2)        'Select second Sheet
    
    objExcel.Visible = True
    
    objSheet.Cells(1, 1).Value = "Kod Kategori BA"
    objSheet.Cells(1, 2).Value = "No.BA"
    
    With objSheet
        .Cells(n, 1).Value = ((Trim(DataHRMIS.Recordset.Fields("BACatCd").Value)))
        .Cells(n, 2).Value = ((Trim(DataHRMIS.Recordset.Fields("BANo").Value)))
    End With
    
    Set objSheet = Nothing
    objBook.Close True
    Set objBook = Nothing
    objExcel.Quit
    Set objExcel = Nothing

Thanks RobDog8888!

I've tried the code already but it didnt work! it still display all the data in sheet 1.
No changes from the previous one..

hello guyz..is there anybody can help me on this? my system still cannot give output to the 2nd worksheet....:(

Are you just trying to add data to "Sheet2" or a different workbook?

VB Code:

Set objSheet = objBook.Sheets.Item("Sheet2") 'Set to second Sheet2 or #2

actually, i need to do both..But first, i try it to add data to sheet 2 within same work book.. i try both

VB Code:

Set objSheet = objBook.Sheets.Item("Sheet2")

and also this way:

VB Code:

Set objSheet = objBook.Sheets.Item(2)

both of this code not succesfull..:(

It really should be working but could you post the code you are using?

hey Rob, I got it already.
I cannot use objSheet to declare it!
i used this way :

VB Code:

Set objExcel = CreateObject("excel.application") 'Starts the Excel Session
Set objBook = objExcel.Workbooks.Add             'Add a Workbook
objExcel.Sheets("Sheet1").Select
objExcel.Sheets("Sheet1").Name = "Maklumat BA"

This code works to me!
But how can I format the cell to be in Text?
It because, when i send the data from DB to excel, excel cannot read '07'..
it only can recognize 7..

Anyway, thanks to you to be so concern! TQ