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

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167
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:

Code:
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:
'************** 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 *****************
 

Derek Brown

Well-known Member
Joined
Dec 26, 2005
Messages
2,390
Perhaps something like the following:
Code:
strPath = Worksheets("Workbook Details").Cells(1, 1)
 

Forum statistics

Threads
1,081,545
Messages
5,359,444
Members
400,528
Latest member
Ratish52

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top