CODEWITHSUNDEEP
ms-accessvbams-access-2010
Rukgo
Rukgo

Reputation: 121

User chosen file directory Access VBA

I am currently importing a folder full of excel files into Access using the code below. I want to export macro to others, but with the hard coded path it would not work for others. But I am not sure how to change the path to accept a user input I would like to try and make something like a file explorer but not sure how.

Dim otable As DAO.TableDef
Dim strPathFile As String, strFile As String, strpath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

' accept excel first line as headers for tables
blnHasFieldNames = True

' Path to files
strpath = "C:\Users\MyName\Desktop\Test\"


strFile = Dir(strpath & "*.xls")

'import all files within selected folder
Do While Len(strFile) > 0
strPathFile = strpath & strFile
strTable = Left(strFile, Len(strFile) - 5)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    strTable, strPathFile, blnHasFieldNames
strFile = Dir()
Loop

Below is what I have tried changing though I am given an error of "Method 'filedialog' of object_'Application' failed" and am not sure if I am using this incorrectly.

strpath = Application.FileDialog(msoFileDialogFilePicker)

Upvotes: 1

Views: 2115

Answers (1)

Rukgo
Rukgo

Reputation: 121

Thanks to HansUp for the help on solving this.

The to select a folder and upload all files within the folder is below...

Const msoFileDialogFolderPicker As Long = 4
Dim objfiledialog As Object
Dim otable As DAO.TableDef
Dim strPathFile As String, strFile As String, strpath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

' accept excel first line as headers for tables
blnHasFieldNames = True

'select folder and set path
Set objfiledialog = Application.FileDialog(msoFileDialogFolderPicker)

With objfiledialog
.AllowMultiSelect = False
If .Show Then
 strpath = .SelectedItems(1) & Chr(92)
 End If
End With

strFile = Dir(strpath & "*.xls")

'import all files within selected folder
Do While Len(strFile) > 0
strPathFile = strpath & strFile
strTable = Left(strFile, Len(strFile) - 5)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    strTable, strPathFile, blnHasFieldNames
strFile = Dir()
Loop

Upvotes: 2

Related Questions