?
Solved

Tweaking the 'Open' Dialog Box using VBA

Posted on 2013-12-09
6
Medium Priority
?
3,199 Views
Last Modified: 2013-12-10
Dear Experts:

Below code snippet activates the open dialog box prompting the user to select an excel file.

I would like to tweak this snippet as follows:

(1) the filter should include xls, xlsx as well as xlsm - files
(2) The inital open dialog is to point to the following path as a constant: 'C:\MyFile\Data'

Help is much appreciated.

Thank you very much in advance.

Regards, Andreas

Sub Import_worksheet()
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook

' active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook

' get the customer workbook
filter = "Excel files (*.xlsx),*.xlsx"
'The open dialog is to point to the following path as a constant: C:\MyFile\Data
caption = "Please Select an input file "
customerFilename = Application.GetOpenFilename(filter, , caption)

If customerFilename = False Then
    MsgBox "No File specified.", vbExclamation, "ERROR"
    Exit Sub
    
Else
'Filter should be only xls and xlsx files
' If Cancel is pressed then abort the whole macro

Set customerWorkbook = Application.Workbooks.Open(customerFilename)
.......

Open in new window

0
Comment
Question by:AndreasHermle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 52

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 600 total points
ID: 39706173
Hi,

pls try

Declare Function SetCurrentDirectory Lib "kernel32" Alias "SetCurrentDirectoryA" (ByVal lpPathName As String) As Long


Sub Import_worksheet()
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook

' active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook

' get the customer workbook
SetCurrentDirectory "C:\MyFile\Data"
filter = "Excel files 97-2003(*.xls),*.xls,Excel files (*.xlsx), *.xlsx,Excel files with Macro (*.xlsm), *.xlsm"
'The open dialog is to point to the following path as a constant: C:\MyFile\Data
caption = "Please Select an input file "
customerFilename = Application.GetOpenFilename(filter, , caption)

Open in new window

regards
0
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 800 total points
ID: 39706176
...or without the need for the "SetCurrentDirectory" routine:

filter = "Excel files (*.xls; *.xlsm; *.xlsx),*.xls;*.xlsm;*.xlsx"

ChDrive "C:"

ChDir "C:\MyFile\Data"

caption = "Please Select an input file "

customerFilename = Application.GetOpenFilename(filter, , caption)

Open in new window



Alternatively, using a different approach:

  Application.FileDialog(msoFileDialogOpen).ButtonName = "&Open"
  Application.FileDialog(msoFileDialogOpen).initialFilename = "C:\MyFile\Data"
  Application.FileDialog(msoFileDialogOpen).Filters.Clear
  Application.FileDialog(msoFileDialogOpen).Filters.Add "Excel files (*.xls; *.xlsm; *.xlsx)", "*.xls;*.xlsm;*.xlsx", 1
  Application.FileDialog(msoFileDialogOpen).Title = "Please Select an input file"
  Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
  
  If Not (Application.FileDialog(msoFileDialogOpen).Show) Then
     MsgBox "No File specified.", vbExclamation, "ERROR"
     Exit Sub
  Else
     customerFilename = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
'    ...
  End If

Open in new window


BFN,

fp.
0
 
LVL 15

Assisted Solution

by:unknown_routine
unknown_routine earned 600 total points
ID: 39706200
Dim fileDialog As Office.fileDialog
   Dim varFile As Variant


   ' Set up the File Dialog. '
   Set fileDialog = Application.fileDialog(msoFileDialogFilePicker)

   With fileDialog

      ' Allow user to make multiple selections in dialog box '
      .AllowMultiSelect = True

      ' Set the title of the dialog box. '
      .Title = "Please select one or more files"

      ' Clear out the current filters, and add our own.'
      .Filters.Clear
      .Filters.Add "Excel Files", "*.xls"
      .Filters.Add "Excel 2010 Files", "*.xlsx"
      .Filters.Add "Excel xlsm Files", "*.xlsm"
      .InitialFileName = "C:\MyFile\Data\"
      ' Show the dialog box. If the .Show method returns True, the '
      ' user picked at least one file. If the .Show method returns '
      ' False, the user clicked Cancel. '
      If .Show = True Then
       'more cod here
      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:AndreasHermle
ID: 39706424
Dear all,

thank you very much for the overwhelming support. I will give it a try soon and then let you know.

Regards,  Andreas
0
 

Author Closing Comment

by:AndreasHermle
ID: 39708152
Dear all,

all of the approaches work just fine and I can learn from all of them.

Thank you very much for your great and professional support, I really appreciate it.

Regards, Andreas
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39708767
You're very welcome, Andreas.

Thanks for closing the question so promptly.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question