Диалог выбора файла access
Диалог выбора файла Вариант 1
Пример кода кнопки для выбора графического файла для объекта картинка
Private Sub Btn_Path_Click()
Dim FName As String
Dim result As Integer
With Application.FileDialog(1)
.Title = "Select picture"
.InitialFileName = "C:\" 'default path Путь по умолчанию
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Picture files", "*.bmp; *.jpg", 1
result = .Show
If result = 0 Then Exit Sub
FName = Trim(.SelectedItems.Item(1))
End With
On error resume next
me.imageObj.Picture = FName 'pic object Контрол формы
End Sub
Диалог выбора файла Вариант 2 ( by АлексейЕ )
Пример выбора файла Аксесс
Public Sub test_dialog2()
Dim strFile As String, strFilter As String
strFilter = "MS Access Database (*.mdb)|*.mdb|Add-ins (*.mda)|*.mda|MDE-Files (*.mde)|*.mde|All Files (*.*)|*.*||"
WizHook.Key = 51488399
WizHook.GetFileName 0, "AppName", "DlgTitle", "", strFile, "c:\", strFilter, 0, 0, 0, True
MsgBox strFile
End Sub
Вариант 3 (WinApi)
Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenFilename As OPENFILENAME) As Long
'Ввод имени файла
Public Function InputFile(ByVal strTitle As String, ByVal strFilter As String, Optional strInitialDir As String) As String
Dim lngReturn As Long
Dim intLocNull As Integer
Dim strTemp As String
Dim ofnFileInfo As OPENFILENAME
Dim strFileName As String
strFileName = String(256, 0)
With ofnFileInfo
.lStructSize = Len(ofnFileInfo)
.lpstrFile = strFileName
.lpstrFileTitle = String(256, 0)
.lpstrInitialDir = strInitialDir
.hwndOwner = Application.hWndAccessApp
.lpstrFilter = strFilter
.nFilterIndex = 1
.nMaxFile = Len(strFileName)
.nMaxFileTitle = ofnFileInfo.nMaxFile
.lpstrTitle = strTitle
.flags = &H1000 Or &H800
.hInstance = 0
.lpstrCustomFilter = String(255, 0)
.nMaxCustFilter = 255
.lpfnHook = 0
End With
lngReturn = GetOpenFileName(ofnFileInfo)
If lngReturn = 0 Then
strFileName = ""
Else
strTemp = Trim(ofnFileInfo.lpstrFile)
intLocNull = InStr(strTemp, Chr(0))
If intLocNull Then
strTemp = Left(strTemp, intLocNull - 1)
End If
strFileName = strTemp
End If
InputFile = strFileName
End Function
Пример вызова диалога выбора файла реализован в примере с всплывающим календарем .
Примечания:
Вариант № 1 не работает если аксесс запущен с опцией /runtime
Вариант №2 странно ведет себя с сетевым путем если этот путь не был предварительно открыт из проводника
Диалог выбора папки
Dim WSHShell, folder
On Error Resume Next
Set WSHShell = CreateObject("Shell.application")
Set folder = WSHShell.browseforfolder(0, "Выбор папки", 0, "C:\")
If Not Err.Number = 91 Then MsgBox folder.Title
Set WSHShell = Nothing
Пример диалога выбора / создания папки
Dim WSHShell As Object, objFolder As Object
Dim P1, P2
'Некоторые значения констант:
' P1=0 - отображаются Рабочий стол, Мой компьютер, Сеть и "Корзина"
' P1=1 - "Корзина" не отображается
' P1=2 - "Корзина" отображается, в "Моем компьютере" выводится дополнительно "Панель Управления"
' P2 определяет верхний уровень отображения. Его можно задать как строку символов
' Пример - "C:\". Или числом. Проверено для ХР
' Р2=0 - Рабочий стол P2=10 - Корзина
' P2=1 - Интернет Explorer (недопустимо) P2=11 - Главное меню
' P2=2 - Программы Р2=12 - Рабочий стол
' P2=3 - Панель управления (недопустимо) Р2=13 - Моя музыка
' P2=4 - Принтеры и факсы (недопустимо) Р2=14 - Мои видеозаписи
' P2=5 - Мои Документы Р2=15 - Рабочий стол
' P2=6 - Избранное Р2=16 - Рабочий стол
' P2=7 - Автозагрузка Р2=17 - Мой Компьютер
' P2=8 - недавние Документы Р2=18 - Сетевой окружение
' P2=9 - SendTo Р2=19 - NetHood
' Р2=20 - Fonts Р2=21 - Templates
' Более подробную информацию об объекте можно найти в документации (EN)
On Error GoTo fnGetFolder_Error
Set WSHShell = CreateObject("Shell.application")
Set objFolder = WSHShell.BrowseForFolder(0, "Выбор папки", P1, P2)
fnGetFolder = objFolder.self.Path
' имя папки содержится в objFolders.Title
Set WSHShell = Nothing
Set objFolder = Nothing
On Error GoTo 0
Exit_fnGetFolder:
Exit Function
Set WSHShell = Nothing
Set objFolder = Nothing
Select Case Err.Number
Case 91
fnGetFolder = ""
Resume Exit_fnGetFolder
Case Else
MsgBox "Ошибка " & Err.Number & " (" & Err.Description & ") в процедуре fnGetFolder"
Resume Exit_fnGetFolder
End Select
How would I go about showing an open file (or file select) dialog in access 2007 VBA?
I have tried using Application.GetOpenFileName as I would in Excel, but this function doesn't exist in Access.
Диалог выбора файла Вариант 2 ( by АлексейЕ )
Пример выбора файла Аксесс
Public Sub test_dialog2()
Dim strFile As String, strFilter As String
strFilter = "MS Access Database (*.mdb)|*.mdb|Add-ins (*.mda)|*.mda|MDE-Files (*.mde)|*.mde|All Files (*.*)|*.*||"
WizHook.Key = 51488399
WizHook.GetFileName 0, "AppName", "DlgTitle", "", strFile, "c:\", strFilter, 0, 0, 0, True
MsgBox strFile
End Sub
Open file example
The dialog below will ask the user to select a file to open:
Select Multiple Files With File Dialog
The original code from when I first built this post allowed users to select multiple files. Since VBA doesn’t do objects/arrays like other programming languages, it is difficult to make the function return an array or object. So, I built my example in a Public Sub and marked up the area that returns selected filenames with a loop. My assumption is that if you need to select multiple files in VBA, you will probably have an idea for how to implement. If you run into a wall, add a comment and we can work on a solution.
As always, please comment!
Select multiple files
Quite common is a scenario when you are asking the user to select one or more files. The code below does just that. Notice that you need to set AllowMultiSelect to True.
Select folder – msoFileDialogFilePicker
Selecting a folder is more simple than selecting files. However only a single folder can be select within a single dialog window.
Save file – msoFileDialogSaveAs
Saving a file is similarly easy, and also only the buttons are differently named.
The save file dialog will in fact not save any files! It will just allow the user to select a filename for the file. You need to open the files for reading / writing yourself. Check out my post on how to write files in VBA
Application FileDialog function
Before we start let’s understand the Application.FileDialog function.
The Application.FileDialog has the following syntax:
Parameter
MsoFileDialogType
An enumeration defining the type of file dialog to open. It has the following values:
Value | Description |
---|---|
msoFileDialogOpen | Open dialog box |
msoFileDialogSaveAs | Save As dialog box |
msoFileDialogFilePicker | File picker dialog box |
msoFileDialogFolderPicker | Folder picker dialog box |
Вариант 3 (WinApi)
Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenFilename As OPENFILENAME) As Long
'Ввод имени файла
Public Function InputFile(ByVal strTitle As String, ByVal strFilter As String, Optional strInitialDir As String) As String
Dim lngReturn As Long
Dim intLocNull As Integer
Dim strTemp As String
Dim ofnFileInfo As OPENFILENAME
Dim strFileName As String
strFileName = String(256, 0)
With ofnFileInfo
.lStructSize = Len(ofnFileInfo)
.lpstrFile = strFileName
.lpstrFileTitle = String(256, 0)
.lpstrInitialDir = strInitialDir
.hwndOwner = Application.hWndAccessApp
.lpstrFilter = strFilter
.nFilterIndex = 1
.nMaxFile = Len(strFileName)
.nMaxFileTitle = ofnFileInfo.nMaxFile
.lpstrTitle = strTitle
.flags = &H1000 Or &H800
.hInstance = 0
.lpstrCustomFilter = String(255, 0)
.nMaxCustFilter = 255
.lpfnHook = 0
End With
lngReturn = GetOpenFileName(ofnFileInfo)
If lngReturn = 0 Then
strFileName = ""
Else
strTemp = Trim(ofnFileInfo.lpstrFile)
intLocNull = InStr(strTemp, Chr(0))
If intLocNull Then
strTemp = Left(strTemp, intLocNull - 1)
End If
strFileName = strTemp
End If
InputFile = strFileName
End Function
Пример вызова диалога выбора файла реализован в примере с всплывающим календарем.
Примечания:
Вариант № 1 не работает если аксесс запущен с опцией /runtime
Вариант №2 странно ведет себя с сетевым путем если этот путь не был предварительно открыт из проводника
Пример диалога выбора / создания папки
Dim WSHShell As Object, objFolder As Object
Dim P1, P2
'Некоторые значения констант:
' P1=0 - отображаются Рабочий стол, Мой компьютер, Сеть и "Корзина"
' P1=1 - "Корзина" не отображается
' P1=2 - "Корзина" отображается, в "Моем компьютере" выводится дополнительно "Панель Управления"
' P2 определяет верхний уровень отображения. Его можно задать как строку символов
' Пример - "C:\". Или числом. Проверено для ХР
' Р2=0 - Рабочий стол P2=10 - Корзина
' P2=1 - Интернет Explorer (недопустимо) P2=11 - Главное меню
' P2=2 - Программы Р2=12 - Рабочий стол
' P2=3 - Панель управления (недопустимо) Р2=13 - Моя музыка
' P2=4 - Принтеры и факсы (недопустимо) Р2=14 - Мои видеозаписи
' P2=5 - Мои Документы Р2=15 - Рабочий стол
' P2=6 - Избранное Р2=16 - Рабочий стол
' P2=7 - Автозагрузка Р2=17 - Мой Компьютер
' P2=8 - недавние Документы Р2=18 - Сетевой окружение
' P2=9 - SendTo Р2=19 - NetHood
' Р2=20 - Fonts Р2=21 - Templates
' Более подробную информацию об объекте можно найти в документации (EN)
On Error GoTo fnGetFolder_Error
Set WSHShell = CreateObject("Shell.application")
Set objFolder = WSHShell.BrowseForFolder(0, "Выбор папки", P1, P2)
fnGetFolder = objFolder.self.Path
' имя папки содержится в objFolders.Title
Set WSHShell = Nothing
Set objFolder = Nothing
On Error GoTo 0
Exit_fnGetFolder:
Exit Function
Set WSHShell = Nothing
Set objFolder = Nothing
Select Case Err.Number
Case 91
fnGetFolder = ""
Resume Exit_fnGetFolder
Case Else
MsgBox "Ошибка " & Err.Number & " (" & Err.Description & ") в процедуре fnGetFolder"
Resume Exit_fnGetFolder
End Select
Open file – msoFileDialogOpen
Opening files is much more simple as it usually involves a single file. The only difference between the behavior between Selecting and Opening files are button labels.
Reader Interactions
Диалог выбора папки
Dim WSHShell, folder
On Error Resume Next
Set WSHShell = CreateObject("Shell.application")
Set folder = WSHShell.browseforfolder(0, "Выбор папки", 0, "C:\")
If Not Err.Number = 91 Then MsgBox folder.Title
Set WSHShell = Nothing
Not the answer you're looking for? Browse other questions tagged vba ms-access or ask your own question.
Linked
Related
Hot Network Questions
To subscribe to this RSS feed, copy and paste this URL into your RSS reader.
Site design / logo © 2022 Stack Exchange Inc; user contributions licensed under cc by-sa. rev 2022.5.9.42071
In a recent project, I was tasked with designing a program in Access where an end user could import a spreadsheet, run a sub routine in VBA and export the results.
As part of the user experience, I wanted the end user to be able to select the input spreadsheet with the file dialog tool.
UPDATE 05/22/2015: The function below contains an error handler and works well if your input has the potential to throw an error. For simplicity-sake, I have included a more compact version (short-circuited) under this one:
Select files – msoFileDialogFilePicker
The msoFileDialogFilePicker dialog type allows you to select one or more files.
Select single files
The most common select file scenario is asking the user to select a single file. The code below does just that:
FileDialog Filters
One of the common problems with working with the Application.FileDialog is setting multiple file filters. Below some common examples of how to do this properly. To add a filter for multiple files use the semicolor ; :
Be sure to clear your list of filters each time. The FileDialog has its nuisances and often filters are not cleared automatically. Hence, when creating multiple dialogs you might see filters coming from previous executed dialogs if not cleared and re-initiated properly.
The open file dialog will in fact not open any files! It will just allow the user to select files to open. You need to open the files for reading / writing yourself. Check out my posts:
Диалог выбора файла / папки
выглядит это примерно так:
Compact version of File Dialog Code
Please note, you still need to include a reference to the Microsoft Office 14.0 Object Library. I have tried every trick I could find to implement late binding (bind in code without reference). I have found that you simply can’t implement without reference (early binding). If anyone out there can prove me wrong on this, please do comment.
FileDialog properties
Property | Description |
---|---|
AllowMultiSelect | Allow to select more than one file or folder |
ButtonName | Text displayed on the action button of a file dialog box |
DialogType | Change the MsoFileDialogType (see above) |
Filter | Set a file filter to filter file types user can select |
InitialFileName | The initial path to be opened e.g. C:\ |
InitialView | The initial file view. Can be one of the following: |
- msoFileDialogViewDetails
- msoFileDialogViewLargeIcons
- msoFileDialogViewList
- msoFileDialogViewPreview
- msoFileDialogViewProperties
- msoFileDialogViewSmallIcons
- msoFileDialogViewThumbnail
- msoFileDialogViewWebView
Select folder example
The dialog below will ask the user to select a folder:
The msoFileDialogFolderPicker dialog allows you to only select a SINGLE folder and obviously does not support file folders
Диалог выбора файла Вариант 1
Пример кода кнопки для выбора графического файла для объекта картинка
Private Sub Btn_Path_Click()
Dim FName As String
Dim result As Integer
With Application.FileDialog(1)
.Title = "Select picture"
.InitialFileName = "C:\" 'default path Путь по умолчанию
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Picture files", "*.bmp; *.jpg", 1
result = .Show
If result = 0 Then Exit Sub
FName = Trim(.SelectedItems.Item(1))
End With
On error resume next
me.imageObj.Picture = FName 'pic object Контрол формы
End Sub
Comments
when I try to use this function i get the error user defined type not defined for the line:
Dim fd As FileDialog.
I do have the office 14 objects in my list of references.
Can you help please?
Thanks.
Ryan McCormick says
Double check your line “Set fd = Application.FileDialog(msoFileDialogFilePicker)”
I am receiving a compile error (user-defined type not defined) related to the Dim fd As FileDialog line. Also- I’m interested in learning how to apply this code functionally. I’m looking to have this code by driven by a button on a form.
I need to be able to select one worksheet from the selected excel file to be imported into a specified table in my database.
Finally, I need to run an append query off of the table into which the excel data is imported and later run a separate query that deletes all of the data in the same table. Generally, I can do all of this using the macro-builder. However I don’t know how to achieve this when using a function that I coded with VBA, Thanks in advance.
Ryan McCormick says
In regards to your first question – did you add a reference to the Microsoft Office Object library? Without the reference, the functionality does not exist as there is no reference to it. You can find references in the VBA script area: Tools>References. Check the box next to the correct Office Object Library (14 if Access 2010, 15 if Access 2013).
Your next question about importing – it looks like you posted a comment on that and it worked well correct?
Ryan,
Thanks for this, I was having a hard time finding a way to make this work. I ended up using your original code and that is working well. Is it possible to filter the results in the dialog so that I only see .xls (or .xlsx) files? I am also getting an error if I press cancel in the file open dialog box.
Ryan, thanks for having this posted, I was able to work through the code and fix my other issues. Not bad for a biologist pretending to be an Access database designer. .:)
Ryan McCormick says
Very cool! I am happy that you were able to make it work! I just noticed your other comment where you were getting an error when pressing cancel. When clicking cancel, the result returned is null and normally creates an error when moving to the next step of loading the file name (in this case your file name/path is value 0). An “if” statement might work for you to handle the 0 in this case:
If Format(FileName) = vbNullString Then
'Do Nothing because returned value is zero
Else
'Execute code because returned filename value not zero
'Returned value should be the selected file name/path
End If
Spot on with this write-up, I absolutely believe that this amazing site needs a lot more
attention. I’ll probably be returning to read more, thanks for the info!
Hi,
How can I select multiple sheets in a workbook to import multiple tables in in access useing above procedure.
Thanks for these two functions. I did a bit of searching before finding this page. Now, my search is over.
I’m having a similar problem trying to create a button for a user menu which will allow the user to browse to the file for IMPORT. Currently i’m using the Macros within ACCESS to just have the file already in the proper place and named the proper name but that’s a bit cumbersome, but for now “its state of the art”. haha! but I need to be able to tackle this somehow. Bottom line is I have a “Particular Table” that needs this “Import” on a regular basis, but if I could just allow the user to see a Browse window then that would make it easier. My IMPORT goes right into a table called 2410 MASTER which I have to reference in ACCESS as [2410 MASTER] or course. Reason I need users to IMPORT this is because the users also routinely clear out a previous data set so that a fresh set of records can be IMPORTED for processing. ACCESS is just wonderful for all we do, but i’d sure like to have a Browse window for IMPORTING any selected Excel File regardless of its name into my [2410 MASTER] table. The reason is the user always preps the Excel file before hand and Lord knows where on earth it is or what its named by the time its ready for IMPORTING. I appreciate your genius! hahaha! I really do!
Ryan McCormick says
I wrote a post that may help you with the file select>import method:
Import Excel File to Access 2010, 2013 With File Dialog in VBA
Also, I just added a couple of sample files to the article for you to download. You can find the link at the bottom of the article.
The most important part of doing regular imports is having clean data and clean field names in your excel file (field names that match the target table in access). I have done a lot of projects like this, please let me know if you have any questions.
Bless you, brother. can’t wait to try this. I’ll let you know
Thanks Ryan!
i’m new at any level of VBA and I do my darndest to use SQL or any of my Excel knowledge or my database experience from years and years, but i’m at a zero level with vba.. lol… I’ve got a routine that already deletes my data in the table already (function on a menu, lol, for users) but I need this IMPORT button to execute what you have here. i’m guessing that I just create this function under BUILD or do I just make this as a MACRO and then the last question I just need to find the line in your code where it knows where to IMPORT the file into what table. I think I see where that is. i’m going to try to tackle this this morning. wish you were at the coffee pot and could follow me back and look over my shoulder. lol i’ll keep you posted.. here goes!
Rats! I think I see my problem. I’m using Access 2007 so I can’t set those objects from the Object Library
i’m still having trouble… I finally got Access 2013 and I tried to add the two pieces of code (vba) but i’m getting errors. I hit control g and it brings up the vba things and I check on the object library and its 15.0 already checked… then while in vba window I added another module called module1 and added the FileDialogue code… not sure if that’s what i’m supposed to do or where.. also I created a button just like you had.. the command button part cmdLoad and put the latter code there under Build.. i’m just lost, but i’m sure i’m close.. when I click the button I get Compile Error: User-Defined Type not defined then the VBA window top 2 lines of the code “function line.. and Dim line”
Ryan, I still get the Compile error: User-defined type not defined.. geeze.. I’ve downloaded your zip db and it works great, but I can’t replicate it in my db. I tell you what though.. I transferred your form, table to my db and I get the same error.. however.. if I just launch your db it works fine. lol.. the error I get then highlights the Function selectFile() Dim fs As FileDialog, filename As String…
geeze.. I know i’m close, but…
Ryan McCormick says
Did you add the Microsoft Object Library as a reference? Open your VBA environment, click on the tools menu and select references. Check the box next to the Microsoft Office XX.0 Object Library, save and see if that works.
Ryan McCormick says
Oops, I just noticed your comment from the 14th. On second thought, you might want to check any of the data types you defined in the process. Check variables (example Dim myName as String) where you could be mixing a variant or something.
Often in VBA we need to ask the users to select files or directories before we execute the actual functionality of our macro. Welcome to the VBA Open file dialog post. Today we will learn how to use the Application.FileDialog, to understand the various msoFileDialogFilePicker file dialog picking options and how to properly manage these dialogs.
Here is a simple example of a VBA File Dialog:
Related
Properties and functions
5 Answers 5
My comments on Renaud Bompuis's answer messed up.
Actually, you can use late binding, and the reference to the 11.0 object library is not required.
The following code will work without any references:
As the sample says, just make sure you have a reference to the Microsoft Access 12.0 Object Library (under the VBE IDE > Tools > References menu).
Addition to what Albert has already said:
This code (a mashup of various samples) provides the ability to have a SaveAs dialog box
This is just code for the same answer that everybody else has given. It's more verbose and shows more options, but it's just a repeat of existing answers.
@David-W-Fenton No, it's not. It returns a filename and allows you to set a filter, which neither of the other answers do. It's free of Office dependencies and can be pasted directly into any form or module, so it's plug and play. This is the better answer, and as for the other answers your critique is even more apt for people who copy/paste from documentation.
I have a similar solution to the above and it works for opening, saving, file selecting. I paste it into its own module and use in all the Access DB's I create. As the code states it requires Microsoft Office 14.0 Object Library. Just another option I suppose:
I agree John M has best answer to OP's question. Thought not explictly stated, the apparent purpose is to get a selected file name, whereas other answers return either counts or lists. I would add, however, that the msofiledialogfilepicker might be a better option in this case. ie:
Note: the value of varfile will remain the same since multiselect is false (only one item is ever selected). I used its value outside the loop with equal success. It's probably better practice to do it as John M did, however. Also, the folder picker can be used to get a selected folder. I always prefer late binding, but I think the object is native to the default access library, so it may not be necessary here
Save file example
The dialog below will ask the user to select a path to which a files is to be saved:
Читайте также: