Provide file path for Access import from Excel (VBA) instead of forcing user to find folder

[jwb1012]

Hello, I am running the code below (from Terry Kraft) from within excel and it forces the user to select the location of the files to import into access (opens up a box where the user navigates to the folder and clicks OK). I was wondering if anyone sees an easy way to adjust the code so I can provide the file path within cell "A1" of the "Workbook Details" in my Excel workbook - so the user can type the folder path and access will automatically import the files from this folder.

Any suggestions would be greatly appreciated.

Code that when run finds my files, imports into access database:

Sub ImportData()
Dim strPathFile As String, strFile As String, strPath As String
 Dim strTable As String, strBrowseMsg As String
 Dim blnHasFieldNames As Boolean
' Change this next line to True if the first row in EXCEL worksheet
 ' has field names
 blnHasFieldNames = True
'UPDATE FILEPATH IF MOVED OR CHANGED!!!!!
 strBrowseMsg = "C:\Users\e1234\Desktop\Import Models"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
       MsgBox "No folder was selected.", vbOK, "No Selection"
       Exit Sub
 End If
' Replace tablename with the real name of the table into which
 ' the data are to be imported
 strTable = "PURCHASING_DB"
 strFile = Dir(strPath & "\*.xls")
 Do While Len(strFile) > 0
       strPathFile = strPath & "\" & strFile
       DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
             strTable, strPathFile, blnHasFieldNames
' Uncomment out the next code step if you want to delete the
 ' EXCEL file after it's been imported
 '       Kill strPathFile
       strFile = Dir()
 Loop

End Sub

Code running in the background to allow this to work:

'************** Code Start **************
'This code was originally written by Terry Kreft.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code courtesy of
'Terry Kreft
Private Type BROWSEINFO
  hOwner As Long
  pidlRoot As Long
  pszDisplayName As String
  lpszTitle As String
  ulFlags As Long
  lpfn As Long
  lParam As Long
  iImage As Long
End Type
Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias _
            "SHGetPathFromIDListA" (ByVal pidl As Long, _
            ByVal pszPath As String) As Long
            
Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias _
            "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) _
            As Long
            
Private Const BIF_RETURNONLYFSDIRS = &H1
Public Function BrowseFolder(szDialogTitle As String) As String
  Dim X As Long, bi As BROWSEINFO, dwIList As Long
  Dim szPath As String, wPos As Integer
  
    With bi
        .hOwner = hWndAccessApp
        .lpszTitle = szDialogTitle
        .ulFlags = BIF_RETURNONLYFSDIRS
    End With
    
    dwIList = SHBrowseForFolder(bi)
    szPath = Space$(512)
    X = SHGetPathFromIDList(ByVal dwIList, ByVal szPath)
    
    If X Then
        wPos = InStr(szPath, Chr(0))
        BrowseFolder = Left$(szPath, wPos - 1)
    Else
        BrowseFolder = vbNullString
    End If
End Function
'*********** Code End *****************

 

[Russell Hauf]

I think that post #7 at the following link should do the trick:
SHBrowseForFolder default/root directory - Xtreme Visual Basic Talk

 

[Derek Brown]

Perhaps something like the following:

strPath = Worksheets("Workbook Details").Cells(1, 1)