troubleshooting Question

How to write in active excel file in VB6?

Avatar of EricPelletier
EricPelletierFlag for Canada asked on
Visual Basic Classic
4 Comments1 Solution540 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros