Problem To Move To 2nd Worksheet

[LadyAziera]

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:

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!

Set objSheet = objBook.Worksheets.Item(2)

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

[RobDog888]

Moved from Classic VB forum.

[RobDog888]

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

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

[LadyAziera]

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..

[LadyAziera]

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

[RobDog888]

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

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

[LadyAziera]

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

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

and also this way:

Set objSheet = objBook.Sheets.Item(2)

both of this code not succesfull..

[RobDog888]

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

[LadyAziera]

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

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