Open an Excel file from Microsoft Word

[yanlok1345]

Hi everyone,

I found a Word macro that can open an excel file from the Microsoft word:

Code: Select all

Sub openworkbook()

Dim xlApp As Object
Dim xlBook As Object
Const strWorkBookName As String = "D:\engine.xlsx"

    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err Then
        Set xlApp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
    Set xlBook = xlApp.Workbooks.Open(Filename:=strWorkBookName)
    xlApp.Visible = True
lbl_Exit:
    Set xlApp = Nothing
    Set xlBook = Nothing
    Exit Sub
End Sub

But this is a little bit slow. it takes 20 seconds. Therefore, I make a faster version:

Code: Select all

Sub OpenExcelFile()

    Dim objExcel As Object
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True
    objExcel.Workbooks.Open "D:\engine.xlsx"
    
End Sub

My question is: How to make this macro open an excel file that can pop out in front of the users? And is there any issues in my faster version of word macro?

Many thanks for your help!

[HansV]

Keep in mind that your version will create a new, separate instance of Excel, even if Excel is already active on the user's computer.

S2481.png

On the left: Excel is already active before running the macro.
On the right: after running the macro, two instances of Excel are running.

The macro should open the workbook in front of other applications.

[yanlok1345]

Keep in mind that your version will create a new, separate instance of Excel, even if Excel is already active on the user's computer.


S2481.png


On the left: Excel is already active before running the macro.
On the right: after running the macro, two instances of Excel are running.

The macro should open the workbook in front of other applications.

Thanks for your reminder. How about the following version?

Code: Select all

Sub Openworkbook()
    
    Dim ExcelApp As Object
    Dim ExcelWorkbook As Object
    
    Set ExcelApp = CreateObject("Excel.Application")
    ExcelApp.Visible = True

    Set ExcelWorkbook = ExcelApp.Workbooks.Open("D:/engine.xlsx")

    Set ExcelWorkbook = Nothing
    Set ExcelApp = Nothing
    
End Sub

[HansV]

That has the same problem. The original version checks whether Excel is already running. If so, it uses that instance of Excel. It only creates a new instance of Excel if Excel is not running yet.

[yanlok1345]

That has the same problem. The original version checks whether Excel is already running. If so, it uses that instance of Excel. It only creates a new instance of Excel if Excel is not running yet.

But why the original version run so slow? I tested the second and thrid version, they are much faster to open the excel file i want. And no problems caused. I don't see the same screen in my task manager as printscreened by yours. Or Do you have a better solution of it? Many thanks for your reply!

[HansV]

Apparently, it takes time to check whether Excel is already running, although 20 seconds is excessive - it takes less than 1 second on my PC.

[yanlok1345]

Apparently, it takes time to check whether Excel is already running, although 20 seconds is excessive - it takes less than 1 second on my PC.

After testing, I now know the difference between the original, second and third versions, which is the same as you said.

As I need to use this macro on the PC(using Hard disk to run windows), it runs so slow.

But when I run the original macro on the PC using SSD to run the windows, it takes 1 second to finish.

Is there any ways to ensure that the macro open excel file using the same instance but not opening new instance when keep clicking the macro?

[HansV]

The first version of the macro takes care of that.

[snb]

I'd simply use:

Code: Select all

Sub M_snb()
  With getobject("D:\engine.xlsx")
      .Windows(1).Visible = True
      .sheets(1).Activate
      .sheets(1).Cells(1).Select
  End With
End Sub

[Charles Kenyon]

The following may be of use to you.
I have not used them but saved them in my code snippets when I saw them online.

' Pragya Chalise
' https://stackoverflow.com/questions/637 ... from-excel
' I put this in my normal template because I like the code. I do not understand all of it.
' Two of the procedures modify the Text right-click menu in Word to give access to an Excel handling macro in that Text right-click menu.
' I suspect that the ones dealing with the Text right-click menu should also go into the This Document module.
'
'
' ==========================================================
'
'

Code: Select all

Private Function FileOpenDialogBox()

    'Display a Dialog Box that allows to select a single file.
    'The path for the file picked will be stored in fullpath variable
    With Application.FileDialog(msoFileDialogFilePicker)
        'Makes sure the user can select only one file
        .AllowMultiSelect = False
        'Filter to just the following types of files to narrow down selection options
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        'Show the dialog box
        .Show

        'Store in fullpath variable
        fullpath = .SelectedItems.Item(1)
        FileOpenDialogBox = fullpath
    End With
    'MsgBox FileOpenDialogBox
End Function

'
' ==========================================================
'

Code: Select all

Sub WorkOnAWorkbook()

    Dim oXL    As Excel.Application
    Dim oWB    As Excel.Workbook
    Dim oSheet As Excel.Worksheet
    Dim oRng   As Excel.range
    Dim ExcelWasNotRunning As Boolean
    Dim WorkbookToWorkOn As String, msg1 As String
    Dim val1, val2 As String
    'specify the workbook to work on
    WorkbookToWorkOn = FileOpenDialogBox

    'If Excel is running, get a handle on it; otherwise start a new instance of Excel
    On Error Resume Next
    Set oXL = GetObject(, "Excel.Application")

    If err Then
        ExcelWasNotRunning = True
        Set oXL = New Excel.Application
    End If

    On Error GoTo Err_Handler

    'If you want Excel to be visible, you could add the line: oXL.Visible = True here; but your code will run faster if you don't make it visible

    'Open the workbook
    Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn)

    'Process each of the spreadsheets in the workbook
    For Each oSheet In oXL.ActiveWorkbook.Worksheets
        'put guts of your code here
        ' msg = msg & oSheet.Range("A1").Value

        If oSheet.Name = "Sheet1" Then
            lastrow = oSheet.Cells(oSheet.Rows.Count, "A").End(xlUp).Row
            For i = 1 To lastrow
                '                    MsgBox "last used row in col  A is " & lastrow
                val1 = oSheet.range("A" & i).Value          'value of the bookmark
                val2 = oSheet.range("B" & i).Value

                ActiveDocument.Bookmarks.Add Name:=val1, range:=Selection.range
                'update bookmark if bookmark exists
                If ActiveDocument.Bookmarks.Exists(val1) = True Then
                    UpdateBookmark (val1), (val2)
                    'MsgBox i
                    j = j + 1                     'counts number of bookmarks updated
                ElseIf ActiveDocument.Bookmarks.Exists(val1) = False Then
                    k = k + 1                     'gives total of bookmarks not found
                End If

            Next i
        End If
        'get next sheet
    Next oSheet
    'Exit Sub
    'MsgBox msg, , msg1
    If ExcelWasNotRunning Then
        oXL.Quit
    End If
    'Make sure you release object references.
    Set oRng = Nothing
    Set oSheet = Nothing
    Set oWB = Nothing
    Set oXL = Nothing

    'quit
    Call update_all_bookmarks    'update all bookmarks

    MsgBox j & " Bookmarks updated!."
    Exit Sub

Err_Handler:
    MsgBox WorkbookToWorkOn & " caused a problem. " & vbNewLine & err.Description, vbCritical, _
           "Error: " & err.Number
    If ExcelWasNotRunning Then
        oXL.Quit
    End If

End Sub

'
' ==========================================================
'

Code: Select all

Private Sub UpdateBookmark(BookmarkToUpdate As String, TextToUse As String)
    Dim BMRange As range
    Set BMRange = ActiveDocument.Bookmarks(BookmarkToUpdate).range
    BMRange.Text = TextToUse
    'ActiveDocument.Bookmarks.Add BookmarkToUpdate, BMRange
End Sub

'
' ==========================================================
'

Code: Select all

Private Sub update_all_bookmarks()
    ' select the document and update the macro
    With Selection
        .WholeStory
        .Fields.Update
        .MoveLeft Unit:=wdCharacter, Count:=1
    End With
End Sub

'
' ==========================================================
'

Code: Select all

Sub RightClickMenu()
    Dim MenuButton As CommandBarButton
    With CommandBars("Text")
        Set MenuButton = .Controls.Add(msoControlButton)
        With MenuButton
            .Caption = "Update from excel"
            .Style = msoButtonCaption
            .OnAction = "WorkOnAWorkbook"
        End With
    End With
End Sub

'
' ==========================================================
'

Code: Select all

Sub ResetRightClick()
   Application.CommandBars("Text").Reset
End Sub

'
'
' ==========================================================
'
'
'
' The following goes to the MyDocument for these to work:

Code: Select all

Private Sub Document_Close()
    ResetRightClick
End Sub

Code: Select all

Private Sub Document_Open()
    Call RightClickMenu
End Sub