Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 621
  • Last Modified:

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
0
martinD28
Asked:
martinD28
  • 3
  • 2
1 Solution
 
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
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.

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

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now