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.
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
Set oWB = oxl.Workbooks.Add
oxl.UserControl = True
oxl.Selection.ColumnWidth = 7.5
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
Irow = Irow + 2
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.