Getopenfilename - File dialog hidden

FredrikNilsen

New Member
Joined
Jan 25, 2021
Messages
26
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Hello,

I have a code to import data to Word from Excel. This is part of the code:

VBA Code:
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?
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
One option is that you could use a file selector native within Word:
VBA Code:
Function GetWorkbookFilename() As String
    Dim dlgOpen        As FileDialog
    Dim FileName       As String
    
    Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
    
    With dlgOpen
       .Filters.Add "Excel Workbooks Only (*.xlsx)", "*.xlsx"
       .FilterIndex = 1
       .Title = "Select Excel Workbook"
       .AllowMultiSelect = False
       .Show
        If .SelectedItems.Count > 0 Then
            FileName = .SelectedItems(1)
        End If
    End With
    GetWorkbookFilename = FileName
End Function
If you add the above function to your code, you can then call it from your code like with
VBA Code:
strFile = GetWorkbookFilename

Does that work?
 
Upvote 0
Solution
I've had a look at code you posted, and I would suggest the following slight adjustments:
VBA Code:
Sub CopyExcelDataToWord()
    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim xlWs As Excel.Worksheet
    Dim strFile As String
    
    strFile = GetWorkbookFilename
    
    Set xlApp = New Excel.Application
    xlApp.Visible = False
    Set xlWbk = xlApp.workbooks.Open(strFile)
    Set xlWs = xlWbk.Sheets(1)
    
    xlWs.UsedRange.Copy
    Application.Selection.Paste
        
    'xlApp.Application.DisplayAlerts = wdAlertsNone  '<- I don't see why you're using this, also you should use FALSE rather than wdAlertsNone

    xlWbk.Close False           ' This closes the Excel workbook without saving it.
    Set xlWbk = Nothing
    xlApp.Quit                  ' This closes the Excel application
    Set xlApp = Nothing
End Sub
When you set an Application's visibility to false, it is then very easy to forget all about it. But you don't then close it down, it will continue running in the background, slowing your system down. To that end, I put some code at the end to close the workbook and the application. You may already have this, but I put it here just in case.

Also, you may already be aware of it, but there is a specific method available in Word for the pasting of Excel tables: Selection.PasteExcelTable method (Word)

Please let me know if there is anything I can do to help.
 
Upvote 0
One option is that you could use a file selector native within Word:
VBA Code:
Function GetWorkbookFilename() As String
    Dim dlgOpen        As FileDialog
    Dim FileName       As String
   
    Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
   
    With dlgOpen
       .Filters.Add "Excel Workbooks Only (*.xlsx)", "*.xlsx"
       .FilterIndex = 1
       .Title = "Select Excel Workbook"
       .AllowMultiSelect = False
       .Show
        If .SelectedItems.Count > 0 Then
            FileName = .SelectedItems(1)
        End If
    End With
    GetWorkbookFilename = FileName
End Function
If you add the above function to your code, you can then call it from your code like with
VBA Code:
strFile = GetWorkbookFilename

Does that work?
Took me a couple of minutes to understand how to call it, but yes, this works great! Thank you.
 
Upvote 0
I've had a look at code you posted, and I would suggest the following slight adjustments:
VBA Code:
Sub CopyExcelDataToWord()
    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim xlWs As Excel.Worksheet
    Dim strFile As String
   
    strFile = GetWorkbookFilename
   
    Set xlApp = New Excel.Application
    xlApp.Visible = False
    Set xlWbk = xlApp.workbooks.Open(strFile)
    Set xlWs = xlWbk.Sheets(1)
   
    xlWs.UsedRange.Copy
    Application.Selection.Paste
       
    'xlApp.Application.DisplayAlerts = wdAlertsNone  '<- I don't see why you're using this, also you should use FALSE rather than wdAlertsNone

    xlWbk.Close False           ' This closes the Excel workbook without saving it.
    Set xlWbk = Nothing
    xlApp.Quit                  ' This closes the Excel application
    Set xlApp = Nothing
End Sub
When you set an Application's visibility to false, it is then very easy to forget all about it. But you don't then close it down, it will continue running in the background, slowing your system down. To that end, I put some code at the end to close the workbook and the application. You may already have this, but I put it here just in case.

Also, you may already be aware of it, but there is a specific method available in Word for the pasting of Excel tables: Selection.PasteExcelTable method (Word)

Please let me know if there is anything I can do to help.
Thanks again. I actually had the "xlWbk.close" part in the end of the code. The xlApp.Application.DisplayAlerts = wdAlertsNone is to avoid the "You have a large amount on date on the clipboard"-message.
 
Upvote 0
Took me a couple of minutes to understand how to call it, but yes, this works great! Thank you.
I was a bit too fast there. This works when the macro is stored in the same document as the user is working on. It doesn't work if the macro is called from a template.

Edit: it doesn't work at all, suddenly, and I have no idea what I have changed right now.
 
Last edited:
Upvote 0
I was a bit too fast there. This works when the macro is stored in the same document as the user is working on. It doesn't work if the macro is called from a template.

Edit: it doesn't work at all, suddenly, and I have no idea what I have changed right now.
I used your code, and now it works. Thanks again for great help!
 
Upvote 0
LOL - I got whiplash reading your replies! HAHA
Is everything ok now?

The xlApp.Application.DisplayAlerts = wdAlertsNone is to avoid the "You have a large amount on date on the clipboard"-message.
This makes complete sense now. If you want to go crazy, you can use the following to clear the clipboard. The Public Declare section needs to go at the very top of the module, and then it is just a matter of replacing the DisplayAlerts line in your code with ClearClipboard. But your approach will work - and likely cause less headaches. I simply mention it as an option.

VBA Code:
#If VBA7 And Win64 Then
    Public Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As Long
    Public Declare PtrSafe Function EmptyClipboard Lib "user32" () As LongPtr
    Public Declare PtrSafe Function CloseClipboard Lib "user32" () As LongPtr
#Else
    Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
    Public Declare Function EmptyClipboard Lib "user32" () As Long
    Public Declare Function CloseClipboard Lib "user32" () As Long
#End If
Public Sub ClearClipboard()
    OpenClipboard (0&)
    EmptyClipboard
    CloseClipboard
End Sub
 
Upvote 0
Hello,

I have a code to import data to Word from Excel. This is part of the code:

VBA Code:
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?

I think if you inserted

AppActivate Excel.Application.Caption

before the call to GetOpenFilename, the dialog would be on top.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top