Solved

Using VB6 to write Excel Spreadsheets -

Posted on 2016-11-25
5
15 Views
Last Modified: 2016-11-30
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
0
Comment
Question by:wilson bailey
  • 2
  • 2
5 Comments
 
LVL 21

Expert Comment

by:JesterToo
Comment Utility
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
 

Author Comment

by:wilson bailey
Comment Utility
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
 
LVL 32

Expert Comment

by:Robberbaron (robr)
Comment Utility
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
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:wilson bailey
Comment Utility
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Welcome back!  My apologies for taking so long to write part two of this series; it's been a long time coming!  As I promised in Part 1, this article will focus on how to locate those elusive AD properties that you are searching for.  Why is this us…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now