Create Excel spreadsheet from Word using VBA-CodePudding

I'm trying to create an Excel spreadsheet and populate it with data from Word, but I can't get VBA to follow through. It launches Excel, but then it errors out.

If I try early binding, such as with this code from the Microsoft Documentation, I get a Run-time error '13': Type mismatch on the Set xlApp line.

Sub test()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
End Sub

If I try late binding, I get a Run-time error '438': Object doesn't support this property or method on the Set xlBook line

Sub test()
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
End Sub

Many people throughout StackOverflow use variations of this successfully. I can't figure out why it doesn't work for me. One user on StackOverflow reported that the problem only persisted on their Mac. If it is a platform problem, is there a way to fix it so it will work on my Mac?

CodePudding user response：

If you use the early-binding technology there is no need to use the CreateObject function in the code:

Set xlApp = CreateObject("Excel.Application")

Instead, you may use the New operator which creates a new Excel Application instance in the code:

Set xlApp = new Excel.Application

If you wanted to automate Microsoft Office Excel using early binding, you would add a reference to the "Microsoft Excel 16.0 Object Library" from the Project | References dialog, and then declare your variable as being of the type Excel.Application. From then on, all calls made to your object variable would be early bound. Read more about late and early binding technologies in the Using early binding and late binding in Automation article.

To use the late binding you must have Microsoft Excel installed on your computer and compile with Option Strict Off to allow late binding:

Sub TestLateBinding()
    Dim xlApp As Object
    Dim xlBook As Object
    Dim xlSheet As Object
    xlApp = CreateObject("Excel.Application")
    ' Late bind an instance of an Excel workbook.
    xlBook = xlApp.Workbooks.Add
    ' Late bind an instance of an Excel worksheet.
    xlSheet = xlBook.Worksheets(1)
    xlSheet.Activate()
    ' Show the application.
    xlSheet.Application.Visible = True
    ' Place some text in the second row of the sheet.
    xlSheet.Cells(2, 2) = "This is column B row 2"
End Sub

You should use early-bound objects whenever possible, because they allow the compiler to make important optimizations that yield more efficient applications. Early-bound objects are significantly faster than late-bound objects and make your code easier to read and maintain by stating exactly what kind of objects are being used. Another advantage to early binding is that it enables useful features such as automatic code completion and Dynamic Help because the Visual Studio integrated development environment (IDE) can determine exactly what type of object you are working with as you edit the code. Early binding reduces the number and severity of run-time errors because it allows the compiler to report errors when a program is compiled.

CodePudding user response：

Thank you, @jonsson. You figured it out for me. It needs the Parent with late binding.

Here is the amended code:

Sub test()
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Set xlApp = CreateObject("Excel.Application")
Set xlApp = xlApp.Parent
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
End Sub