How to write in active excel file in VB6?

Hi Experts,
I took a working VBS code to make a VB6 module that should create and write excel file.
I have a Sub that create the file which is working and a another Sub that should write in the created excel file.

But the wrting part is not working. Can someone help me having the right code?
I need to keep both Sub separated.

Thank you
'Create and format the excel file
Sub CreateExcelFile()

Dim objXL As Object
Dim ObjWb As Object

Set objXL = CreateObject("Excel.Application")
Set ObjWb = objXL.Workbooks.Add
    objXL.Visible = True
    
    'Set column width.
    objXL.Columns(1).ColumnWidth = 15
    objXL.Columns(2).ColumnWidth = 35
    objXL.Columns(3).ColumnWidth = 16.5
    objXL.Columns(4).ColumnWidth = 26
    objXL.Columns(5).ColumnWidth = 12
    objXL.Columns(6).ColumnWidth = 55
    
    ''Set column headers
    objXL.Cells(1, 1).Value = "Computer Name"
    objXL.Cells(1, 2).Value = "Operating System"
    objXL.Cells(1, 3).Value = "Ping Status"
    objXL.Cells(1, 4).Value = "Up Time"
    objXL.Cells(1, 5).Value = "LogFile Exist"
    objXL.Cells(1, 6).Value = "Exit code"
    
    ''Format text (bold)
    objXL.Range("A1:F1").Select
    objXL.Selection.Font.Bold = True
    objXL.Selection.Interior.ColorIndex = 1
    objXL.Selection.Interior.Pattern = 1 ''xlSolid
    objXL.Selection.Font.ColorIndex = 2
    ''Left Align text
    objXL.Columns("B:B").Select
    objXL.Selection.HorizontalAlignment = &HFFFFEFDD '' xlLeft
End Sub

'Add info in excel file
Sub AddToExcelFile(strName, strCode, strOS, myLog, strUptime)
   Dim strLog
   Dim n
   Dim myCell
   Dim objXL As Object
   Set objXL = GetObject(, "Excel.Application")
   
    myCell = 5  'The start column for logfile
    
    objXL.Cells(intIndex, 1).Value = strName
    objXL.Cells(intIndex, 2).Value = strOS
    objXL.Cells(intIndex, 3).Value = strCode
    objXL.Cells(intIndex, 4).Value = strUptime
    strLog = Split(myLog, ",")
    For n = 0 To UBound(strLog)
        objXL.Cells(intIndex, myCell).Value = strLog(n)
        myCell = myCell + 1
    Next
    
    intIndex = intIndex + 1
    objXL.Cells(intIndex, 1).Select
End Sub

Open in new window

EricPelletierAsked:
Who is Participating?
 
Brook BraswellApplication Development ManagerCommented:
in that module would be fine it would be global for the entire project.

You should also add the variable intIndex as global and set it when you run then createsheet

Global objXL as Object
Global objWb as Object
Global intIndex as single

'Create and format the excel file
Sub CreateExcelFile()

Set objXL = CreateObject("Excel.Application")
Set ObjWb = objXL.Workbooks.Add
    objXL.Visible = True
    
    'Set column width.
    objXL.Columns(1).ColumnWidth = 15
    objXL.Columns(2).ColumnWidth = 35
    objXL.Columns(3).ColumnWidth = 16.5
    objXL.Columns(4).ColumnWidth = 26
    objXL.Columns(5).ColumnWidth = 12
    objXL.Columns(6).ColumnWidth = 55
    
    ''Set column headers
    objXL.Cells(1, 1).Value = "Computer Name"
    objXL.Cells(1, 2).Value = "Operating System"
    objXL.Cells(1, 3).Value = "Ping Status"
    objXL.Cells(1, 4).Value = "Up Time"
    objXL.Cells(1, 5).Value = "LogFile Exist"
    objXL.Cells(1, 6).Value = "Exit code"
    
    ''Format text (bold)
    objXL.Range("A1:F1").Select
    objXL.Selection.Font.Bold = True
    objXL.Selection.Interior.ColorIndex = 1
    objXL.Selection.Interior.Pattern = 1 ''xlSolid
    objXL.Selection.Font.ColorIndex = 2
    ''Left Align text
    objXL.Columns("B:B").Select
    objXL.Selection.HorizontalAlignment = &HFFFFEFDD '' xlLeft
    intindex = 2
End Sub

'Add info in excel file
Sub AddToExcelFile(strName, strCode, strOS, myLog, strUptime)
   Dim strLog
   Dim n
   Dim myCell
    myCell = 5  'The start column for logfile
    
    objXL.Cells(intIndex, 1).Value = strName
    objXL.Cells(intIndex, 2).Value = strOS
    objXL.Cells(intIndex, 3).Value = strCode
    objXL.Cells(intIndex, 4).Value = strUptime
    strLog = Split(myLog, ",")
    For n = 0 To UBound(strLog)
        objXL.Cells(intIndex, myCell).Value = strLog(n)
        myCell = myCell + 1
    Next
    
    intIndex = intIndex + 1
    objXL.Cells(intIndex, 1).Select
End Sub

Open in new window

0
 
Brook BraswellApplication Development ManagerCommented:
Eric - you need to set your objects in a way that one sub can see the object also...

Try this instead...

Global objXL as Object
Global objWb as Object

'Create and format the excel file
Sub CreateExcelFile()

Set objXL = CreateObject("Excel.Application")
Set ObjWb = objXL.Workbooks.Add
    objXL.Visible = True
    
    'Set column width.
    objXL.Columns(1).ColumnWidth = 15
    objXL.Columns(2).ColumnWidth = 35
    objXL.Columns(3).ColumnWidth = 16.5
    objXL.Columns(4).ColumnWidth = 26
    objXL.Columns(5).ColumnWidth = 12
    objXL.Columns(6).ColumnWidth = 55
    
    ''Set column headers
    objXL.Cells(1, 1).Value = "Computer Name"
    objXL.Cells(1, 2).Value = "Operating System"
    objXL.Cells(1, 3).Value = "Ping Status"
    objXL.Cells(1, 4).Value = "Up Time"
    objXL.Cells(1, 5).Value = "LogFile Exist"
    objXL.Cells(1, 6).Value = "Exit code"
    
    ''Format text (bold)
    objXL.Range("A1:F1").Select
    objXL.Selection.Font.Bold = True
    objXL.Selection.Interior.ColorIndex = 1
    objXL.Selection.Interior.Pattern = 1 ''xlSolid
    objXL.Selection.Font.ColorIndex = 2
    ''Left Align text
    objXL.Columns("B:B").Select
    objXL.Selection.HorizontalAlignment = &HFFFFEFDD '' xlLeft
End Sub

'Add info in excel file
Sub AddToExcelFile(strName, strCode, strOS, myLog, strUptime)
   Dim strLog
   Dim n
   Dim myCell
    myCell = 5  'The start column for logfile
    
    objXL.Cells(intIndex, 1).Value = strName
    objXL.Cells(intIndex, 2).Value = strOS
    objXL.Cells(intIndex, 3).Value = strCode
    objXL.Cells(intIndex, 4).Value = strUptime
    strLog = Split(myLog, ",")
    For n = 0 To UBound(strLog)
        objXL.Cells(intIndex, myCell).Value = strLog(n)
        myCell = myCell + 1
    Next
    
    intIndex = intIndex + 1
    objXL.Cells(intIndex, 1).Select
End Sub

Open in new window

0
 
EricPelletierAuthor Commented:
ok but now the question is... Do I have to define global in that module or in the main module? I call the sub from another module.
As example, I have the main module that call the create Sub to create the Excel file and come back doing some other things before calling the other Sub to write the info in that file.

So my question is if I define it as global in the module will it be define for all the rest of the active process or only for the time that I call the Sub?

I tried your Solution by putting it Global in the module, the sub, the main module, on form load,etc etc and it is still doesn't work. The Excel file is always created but can't write the cells. I presume that I'm missing an object. Maybe getting the workbook, activesheet or something like that when calling the write Sub. Do I need to get other objects to be able to write the file? Do I need to save the file and use the filename to make sure it is writing at the right place?

Please help.... :)
0
 
EricPelletierAuthor Commented:
works great now.....

Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.