How do I run an Excel macro from a VB 6.0 application?

A friend of mine has asked me to modify his VB6.0 application to enable it to create an
Excel 97 spreadsheet from a text or csv file. I've created a macro in Excel that opens the file.
Here's the question: How do I run the Excel macro from VB? I have tried using a shell command
to start Excel, which it does, but it throws an "object required" error when it gets to the
macro code which I pasted into VB. That code is:
 Sub Open_csv()
    Workbooks.Open Filename:="C:\openThis.csv"
    Selection.TextToColumns Destination:=Range("A1"), DataType:= _
        xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(1, 1), Array(6, 1), _
        Array(9, 1), Array(14, 1))
End Sub

The file is where it should be, I'm not sure which object is not being found.Debug highlights
the entire block of code. I've tried the Help files but have not been able to make this work.  Any assistance would be greatly appreciated. Sorry about the low point value, I'm new
and not "Premium."

Martin
martinD28Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sbattaCommented:
Try adding Application Reference to selection.text like..

Application.Selection.Text ....
0
jbauer22Commented:
1. Make sure you have Microsoft Excel checked in the References.
2. Make sure to Declare objects.

Dim oWK as Workbooks

oWK.Open "C:\openThis.csv"
oWK.Cells(1, 1).TextToColumns etc...
0
martinD28Author Commented:
Thanks for replying! I see what you mean about referencing the application, but I still don't have it working.

I referenced the application by declaring and initializing:

Dim xlApp As String
xlApp = "C:\ProgramFiles\Microsoft Office\Office10\Excel.exe"

The rest of the code:

Dim fileName As String
fileName = "C:\openThis.csv"

Workbooks.Open fileName:=fileName, _
    xlApp.Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(10, 1), Array(15, 1), Array(18, 1)), _
        TrailingMinusNumbers:=True

It still does not work, it highlights xlApp right before the word selection and throws the error:"Expected: Named parameter"  That's one I'm not familiar with.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

martinD28Author Commented:
Thank you both for replying, I'm still trying.
I have added the Excel object library to references on your advice, ibauer22. I have declared my objects and set them, but am still not able to make this work. When I run it, I am getting an error: "ActiveX component can't create object." and the 4th line is highlighted by Debugger. Seems it doesn't like me setting WK as a new Excel Workbook. Actually, it's a .csv file that opens in Excel,not a true workbook. I am attempting to save it to a .xls in the last line of code. Commenting that 4th line predictably produces an "Object variable not set" error on the 5th line.  My code is as follows:

Dim xlApp As Excel.Application
Set xlApp = New Excel.Application
Dim WK As Workbook
Set WK = New Excel.Workbook

  WK.Open ("C:\openThis.csv")
  WK.Activate
  WK.Cells(1, 1).TextToColumns
  xlApp.Selection.TextToColumns Destination:=Range("A1"),  DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(10, 1), Array(15, 1), Array(18, 1)), _
        TrailingMinusNumbers:=True
  WK.SaveAs "C:\openThis.xls"

I am not sure how to work the Cells(1,1) reference into the statement for TextToColumns...Still a bit in the dark, would be grateful for some more light shed on the subject. Thanks!
0
jbauer22Commented:
Try this:

Private Sub Command1_Click()
   
    Dim oExcel As Excel.Application
    Dim oWB As Workbook
    Dim oWS As Worksheet
   
    Set oExcel = GetObject("", "Excel.Application")
    Set oWB = Workbooks.Open("F:\openThis.csv")
    Set oWS = oWB.Sheets("OpenThis")
    oExcel.Visible = True

    oWS.Columns(1).TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
    FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(10, 1), Array(15, 1), Array(18, 1)), _
    TrailingMinusNumbers:=True
   
    oWB.SaveAs "C:\openThis.xls"
   
End Sub


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
martinD28Author Commented:
Thanks, jbauer22. I will research the GetObject function you used here, that seems to be the key. Adding it to my code makes the difference. Give that person 75 points!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

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.