?
Solved

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

Posted on 2003-03-12
6
Medium Priority
?
614 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:martinD28
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 

Expert Comment

by:sbatta
ID: 8124346
Try adding Application Reference to selection.text like..

Application.Selection.Text ....
0
 
LVL 2

Expert Comment

by:jbauer22
ID: 8124468
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
 

Author Comment

by:martinD28
ID: 8124655
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:martinD28
ID: 8125309
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
 
LVL 2

Accepted Solution

by:
jbauer22 earned 300 total points
ID: 8128405
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
 

Author Comment

by:martinD28
ID: 8129995
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…
Suggested Courses
Course of the Month10 days, 21 hours left to enroll

770 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