Dialog for user to select a worksheet?

Jess31 used Ask the Experts™
In Access VBA I use Application.FileDialog to select a file but how can I select a workbook and then the specific worksheet?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

your question is so vague.
what do you intend to do after selecting the excel file?
 - open the excel file than select the specific worksheet
- do you want to import the content of the worksheet?
   docmd.transferspreadsheet acimport, "tablename", strexcelfile, true, "NAMEOFSHEET!"
Top Expert 2016

You could use a listbox

Private Sub UserForm_Initialize()
Dim aSheets
Dim strSheets As String
For Each xlSheet In ActiveWorkbook.Worksheets

    strSheets = strSheets & ";" & xlSheet.Name
Next xlSheet

strSheets = Right(strSheets, Len(strSheets) - 1)
aSheets = Split(strSheets, ";")
ListBox1.List = aSheets
End Sub

Open in new window

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

I use a technique similar to that used by Rgonzo1971
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

On the other side of things, ...Notice that here you are simply simulating the Access built in Import wizard
(Select the file, ...then select the sheet.)
Browse to fileSelect File dialog boxSelect Sheet
So instead of going through all this code, ...why not just use the wizard?:

The advantages are:
1. Only one line of code is needed:
    DoCmd.RunCommand acCmdImportAttachExcel
2. With the wizard you can select things like:
a. Append, of Import, or Link
b. First Row contains field names
c. You can rename fields
d. you can change datatypes
e. Let Access add a primary Key
f. the ability to move back and forth through the wizard

Why reinvent the wheel?


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial