Help in Access VBA: how do i call the Open File Module into my Import Module

Question

Hi, I believe my question is very simple, but i'm an absolute noob in VBA. I have this VBA procedure in the first screenshot that imports an excel file with multiple tabs and stores all data into 1 Access table. The VBA is currently relying on a hardcoded file path to pickup the source data. I would like to replace that piece of the code calling another function (which is a standard api that open the "file open" dialog box where you can browse for the file. This is the beginning of the main VBA Module code:

Sub Import_Clmbypn()

Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim lngCount As Long
Dim objExcel As Object, objWorkbook As Object
Dim colWorksheets As Collection
Dim strPathFile As String, strTable As String
Dim strPassword As String

' Establish an EXCEL application object
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
      Set objExcel = CreateObject("Excel.Application")
      blnEXCEL = True
End If
Err.Clear
On Error GoTo 0

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = False

' Replace C:\Filename.xls with the actual path and filename
strPathFile = "C:\Users\nb652\OneDrive - CNH Industrial\1. PIP\Ad Hoc\ECONOMICS\Claims by Partnr.xlsx"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "IMP_CLAIMBYPN2"

Where it says strPathFile = "filename.xlsx", i would like to call the module ImportFile API which is stored in the same Access Database as a separate module, so i can pick/browse for a file that is going to be set as input strPathFile. It needs to the user selected filename in the string and then just continue with the module to transform/import the data. (that part is all functioning, i just need to nest the open file function inside that module) How do i have to code this? The Openfile modules goes like this:

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

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

Function LaunchCD(strform As Form) As String
    Dim OpenFile As OPENFILENAME
    Dim lReturn As Long
    Dim sFilter As String
    OpenFile.lStructSize = Len(OpenFile)
    OpenFile.hwndOwner = strform.Hwnd
    sFilter = "All Files (*.*)" & Chr(0) & "*.*" & Chr(0) & _
      "JPEG Files (*.JPG)" & Chr(0) & "*.JPG" & Chr(0)
    OpenFile.lpstrFilter = sFilter
    OpenFile.nFilterIndex = 1
    OpenFile.lpstrFile = String(257, 0)
    OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
    OpenFile.lpstrFileTitle = OpenFile.lpstrFile
    OpenFile.nMaxFileTitle = OpenFile.nMaxFile
    OpenFile.lpstrInitialDir = "D:\"
    OpenFile.lpstrTitle = "Select a file to import"
    OpenFile.flags = 0
    lReturn = GetOpenFileName(OpenFile)
        If lReturn = 0 Then
            MsgBox "A file was not selected!", vbInformation, _
              "Select a file to import"
         Else
            LaunchCD = Trim(Left(OpenFile.lpstrFile, InStr(1, OpenFile.lpstrFile, vbNullChar) - 1))
         End If
End Function

Answer 1

I recommend to use the FileDialog instead. Something like

Dim f As Object

Set f = Application.FileDialog(3)

If f.Show Then strPathFile = f.SelectedItems(1)

https://learn.microsoft.com/en-us/office/vba/api/Access.Application.FileDialog