Opening an excel workbook to run a macro on

[MattyJay]

Hello,

I have a Word VBA macro that transfers data from specific cells in an open excel workbook to bookmarks in a word document.
Ideally, I would be able to press a button which opens a file dialog, allowing me to select the excel file upon which to run the macro (setting it to be the active workbook, calling my other macro, then closing the workbook should work(?))

I have a simple piece of code which brings up the file dialog, but does not open the selected file, nor can it call on my other macro.

Code: Select all

  Private Sub Browsefile()
  On Error Resume Next
    Dim strFile As String
    Set x1App = GetObject(Class:="Excel.Application")
    Dim xlwbk As Object
    With Application.FileDialog(1)
        .Filters.Clear
        .Filters.Add "Excel workbooks", "*.xls*"
        If .Show Then
            strFile = .SelectedItems(1)
        Else
            Beep
            Exit Sub
        End If
        End With
End Sub

Any assistance would be greatly appreciated.

Matt

[HansV]

You can add the following line below End With to open the workbook:

Code: Select all

    Set xlWbk = xlApp.Workbooks.Open(strFile)

The workbook will then be the active workbook in Excel.

In principle, you can call another macro simply by using its name, or its name preceded by Call. For example if you want to call OtherMacro:

Code: Select all

   OtherMacro

or

Code: Select all

    Call OtherMacro

but whether that will do what you want depends on the way that macro interacts with Excel.

[MattyJay]

Thanks for replying, Hans,

I'm not seeing anything from using 'Set x1Wbk...'
Might I have to include a line to open excel first, before loading the selected file? - the file still doesn't open if I manually launch excel, however.

[HansV]

Try this version and see if it opens the workbook that you select. If it works, you can process the workbook, or call another macro.

Code: Select all

Private Sub Browsefile()
    Dim strFile As String
    Dim xlApp As Object
    Dim xlWbk As Object
    With Application.FileDialog(1)
        .Filters.Clear
        .Filters.Add "Excel workbooks", "*.xls*"
        If .Show Then
            strFile = .SelectedItems(1)
        Else
            Beep
            Exit Sub
        End If
    End With
    On Error Resume Next
    Set xlApp = GetObject(Class:="Excel.Application")
    If xlApp Is Nothing Then
        Set xlApp = CreateObject(Class:="Excel.Application")
    End If
    On Error GoTo 0
    xlApp.Visible = True
    Set xlWbk = xlApp.Workbooks.Open(strFile)
    ' Do something with the workbook, or call another macro
    '...
End Sub

[MattyJay]

Many thanks Hans, this works perfectly!

One last question (for now):
I've set x1App.Visible to false, but still want to close the workbook, could I use:

Code: Select all

xlWbk.Close SaveChanges:=False

And if so, where do I insert this into the second macro?

[HansV]

In the version that I posted, it is xlApp (with a lower case l), not x1App (with a digit 1).

If you want to be able to refer to xlWbk in another macro, you must remove the declarations

Code: Select all

    Dim xlApp As Object
    Dim xlWbk As Object

from the BrowseFile macro, and insert the following at the top of the module:

Code: Select all

    Public xlApp As Object
    Public xlWbk As Object

This makes the variables available to all macros in your workbook.

You can then use

Code: Select all

    xlWbk.Close SaveChanges:=False

in the other macro.

[MattyJay]

After making the change to the declarations, the error message: "Invalid attribute in Sub or Function" is returned.

[Rudi]

Have you made the change everywhere...not just in the declaration.
Best to use Replace (CTRL+H) and find x1Wbk and replace with xlWbk.

[Rudi]

Note that you need to place the declaration statements outside of the Sub - End Sub statements as Hans indicated.
make sure it is on the very top of the module, above the first Sub statement.

2014-04-03_13h12_19.jpg

[MattyJay]

Hi Rudi,
My use of 1 rather than l in this thread was a typo, not one I have replicated in my code!

I had misread what Hans said and simply replaced my Dims with Publics, all is working perfectly now I have moved them.

Many thanks to both of you.

Matt