martinD28
asked on
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
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
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...
2. Make sure to Declare objects.
Dim oWK as Workbooks
oWK.Open "C:\openThis.csv"
oWK.Cells(1, 1).TextToColumns etc...
ASKER
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
The rest of the code:
Dim fileName As String
fileName = "C:\openThis.csv"
Workbooks.Open fileName:=fileName, _
xlApp.Selection.TextToColu
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.
I referenced the application by declaring and initializing:
Dim xlApp As String
xlApp = "C:\ProgramFiles\Microsoft
The rest of the code:
Dim fileName As String
fileName = "C:\openThis.csv"
Workbooks.Open fileName:=fileName, _
xlApp.Selection.TextToColu
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.
ASKER
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.TextToColu
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!
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.TextToColu
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
Application.Selection.Text