Getopenfilename - File dialog hidden

[fenilsen]

I have a code to import data to Word from Excel. The user has an open document in Word, and wants to import data from the selected Excel workbook. This is part of the code in Word:

    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim xlWs As Excel.Worksheet
    Dim strFile As String
        
    
    strFile = Excel.Application.GetOpenFilename(FileFilter:="Excel Workbooks Only (*.xlsx),*.xlsx", FilterIndex:=1, Title:="Select Excel Workbook")
    Workbooks.Open (strFile)
    
    Set xlApp = Excel.Application
    xlApp.Visible = False
    Set xlWbk = Excel.ActiveWorkbook
    
    Set xlWs = xlWbk.Sheets(1)
    
    xlWs.UsedRange.Copy
        
    Selection.Paste
    xlApp.Application.DisplayAlerts = wdAlertsNone

When the user activates the macro, the file dialog is hidden behind the Word window, and has to use Alt-tab to navigate to it. Any ways to avoid this?

[Paul_Hossler]

I usually do something like that this way

Option Explicit


Sub test()


    Dim xlApp As Object
    Dim xlWbk As Object
    Dim xlWs As Object
    Dim strFile As String
        
        
    Set xlApp = CreateObject("Excel.Application")
    
    strFile = xlApp.GetOpenFilename(FileFilter:="Excel Workbooks Only (*.xlsx),*.xlsx", FilterIndex:=1, Title:="Select Excel Workbook")
    xlApp.Workbooks.Open (strFile)
    Set xlWbk = xlApp.activeworkbook
    xlApp.Visible = False
    
    Set xlWs = xlWbk.Sheets(1)
    
    xlWs.UsedRange.Copy
        
    Selection.Paste


    xlApp.Application.DisplayAlerts = wdAlertsNone
    xlApp.Quit
End Sub

[fenilsen]

I usually do something like that this way

Thanks, but it didn't help. The dialogue is still hidden behind the main window.

Maybe I should had included this: in this situation, the macro is called from a template, not the current document. If the template is open (or you are working on a document with the macro) and the VBA window is open, it works. If you start from a new file and just call the macro from a template, it's hidden.

[snb]

Avoid redundant object variables:

Sub M_snb()
  With Application.FileDialog(3)
    .InitialFileName = "G:\OF\*.xlsx"
    If .Show Then
      With GetObject(.SelectedItems(1))
        .Application.DisplayAlerts = False
        .sheets(1).usedrange.Copy
        ActiveDocument.Content.Paste
        .Close 0
      End With
    End If
  End With
End Sub

[fenilsen]

Thanks, this works, but only in the document with the macro. It doesn't work when the macro is called from a template.

Correction: it worked the first time, but now it doesn't work at all. "Run-time error. Call was rejected by callee".

[snb]

What is 'this' ?

The Code I posted is independent of where it has been stored.

[Paul_Hossler]

Maybe I should had included this: in this situation, the macro is called from a template, not the current document.

Yes, that would make a difference

Try this in the DOTM and see if it works

I saved the DOTM and then FIle, New to create a DOCX

There's no error checking or handling canceling and the code could be cleaned up if you want

Option Explicit


Sub test()


    Dim xlApp As Object
    Dim xlWbk As Object
    Dim xlWs As Object
    Dim strFile As String
        
    ActiveWindow.Activate
        
    Set xlApp = CreateObject("Excel.Application")
    
    strFile = xlApp.GetOpenFilename(FileFilter:="Excel Workbooks Only (*.xlsx),*.xlsx", FilterIndex:=1, Title:="Select Excel Workbook")
    xlApp.Workbooks.Open (strFile)
    Set xlWbk = xlApp.activeworkbook
    xlApp.Visible = False
    
    Set xlWs = xlWbk.Sheets(1)
    
    xlWs.UsedRange.Copy
        
    Selection.Paste
    
    xlApp.Application.DisplayAlerts = wdAlertsNone
    xlApp.Quit
End Sub