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