?
Solved

How to write in active excel file in VB6?

Posted on 2009-12-16
4
Medium Priority
?
463 Views
Last Modified: 2012-05-08
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

0
Comment
Question by:EricPelletier
  • 2
  • 2
4 Comments
 
LVL 14

Expert Comment

by:Brook Braswell
ID: 26067076
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
 

Author Comment

by:EricPelletier
ID: 26068777
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
 
LVL 14

Accepted Solution

by:
Brook Braswell earned 2000 total points
ID: 26071119
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
 

Author Closing Comment

by:EricPelletier
ID: 31667016
works great now.....

Thank you
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question