Using VB6 to write Excel Spreadsheets -

When using VB6 to write Excel Spreadsheets, I am running into a problem where I have to span one excel file opened as an object across 2 subroutines. I have a couple of subroutines that I need to call several times, to I really need to use them.

It acts as though the object won't span out of the originating procedure.

Example:
Dim oxl As Object, oWB As Object, IStartedXL As Boolean
    Set oxl = GetObject(, "excel.application")
    On Error GoTo 0
    If oxl Is Nothing Then
        Set oxl = CreateObject("excel.application")
        IStartedXL = True
        End If
    Set oWB = oxl.Workbooks.Add
    oxl.UserControl = True
   
oxl.Columns("A:A").Select
oxl.Selection.ColumnWidth = 7.5
Call TestOne()


Public Sub testone()
If NoCostError > 4 And Len(Trim(CostError(4))) > 2 Then
    Flag = 0
    For i = 1 To NoCostError
        If Trim(Len(CostError(i))) > 2 Then Flag = 1
        If Flag = 1 And Len(Trim(CostError(i))) > 0 Then
            Irow = Irow + 1
            oxl.Cells(Irow, 1).Value = CostError(i)   <==============   Error occurs here
        End If
    Next i
End If
Irow = Irow + 2
End Sub

if I put the code in the subroutine code into the calling procedure (not calling a subroutine), it works fine. Any ideas would definitely be appreciated.

Thank you,

WilsonB
wilson baileyAsked:
Who is Participating?
 
Robberbaron (robr)Connect With a Mentor Commented:
Sub Main()
	Dim oxl As Object, oWB As Object, IStartedXL As Boolean
	dim oWS as Object
    Set oxl = GetObject(, "excel.application")
    On Error GoTo 0
    If oxl Is Nothing Then
        Set oxl = CreateObject("excel.application")
        IStartedXL = True
        End If
    Set oWB = oxl.Workbooks.Add
    oxl.UserControl = True
    
	set oWS = oWB.Activesheet
	oWS.Columns("A:A").Select
	oWS.Selection.ColumnWidth = 7.5
	Call TestOne(oWS)
End Sub

Public Sub testone(oWS as object)
	If NoCostError > 4 And Len(Trim(CostError(4))) > 2 Then
		Flag = 0
		For i = 1 To NoCostError
			If Trim(Len(CostError(i))) > 2 Then Flag = 1
			If Flag = 1 And Len(Trim(CostError(i))) > 0 Then
				Irow = Irow + 1
				oWS.Cells(Irow, 1).Value = CostError(i)   
			End If
		Next i
	End If
	Irow = Irow + 2
End Sub

Open in new window

0
 
JesterTooCommented:
What specific error are you getting?  Try putting a error-handling wrapper around the statement to see if it reveals anything useful.  Also, have you tried passing the oxl object explicitly to the subroutine (maybe byref)?
0
 
wilson baileyAuthor Commented:
I just tried the bits I sent you. I would think something like the "by ref" is what I need, but I really don't know how to accomplish that.
0
 
Robberbaron (robr)Commented:
one way as a last resort is to make the oXL & oWB objects global, but definitely should be passing the parent object.

Also, your code
oxl.Cells(Irow, 1).Value 

Open in new window

doesn't seem right.

the Cells property is part of a worksheet, not an Excel object.
while it may be using ActiveWorkbook.ActiveSheet by default, it may be part of the issue.

in my opinion, it should be
oXL.ActiveWorkbook.ActiveSheet.Cells(lrow,1).Value

Open in new window

0
 
wilson baileyAuthor Commented:
Thank you for your help. On this same project, I am running into a problem when  copy use filecopy (also tried FSO) - The file copies, but gives an file access error. I'm copying to a server, by the way. Any ideas.

Thank you,

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