Need help with importing data into table, run a query, & then repeat for each Excel file in folder

[UPSDuder]

Working with Access 2013
I have searched far and low and cannot find syntax that I can get to work, I have attempted to piece together code to accomplish the task of opening up each excel file within a folder and replace the data in a table with data from that excel file, run a query and then repeat for each file.

I get the error code 1004 after it opens Excel, but not my desired workbook. I have to be close because the error message correctly calls out the file I am trying to open.
"Sorry, we couldn't find Nike.xlsx. Is it possible it was moved, renamed or deleted?"

Errors out on the code in red

Sub ImportfromPath()
Dim directory As String, filename As String
Dim Mywb As Workbook
Dim app As New Excel.Application
Set app = CreateObject("Excel.Application")
app.Visible = True

directory = "C:\Users\mcz0pjy\Desktop\Tracker\Templates\"
filename = Dir(directory & "*.xl??")
Do While filename <> ""
filename = filename
      Set wb = app.Workbooks.Open(filename)
      Debug.Print app.Version
      Set xlwrksht = wb.Worksheets("Accounts")
      
    'Ensure Workbook has opened before moving on to next line of code
      DoEvents
       'clearing/reseting tables
         DoCmd.OpenTable "Accounts", acViewNormal, acEdit
         DoCmd.runSQL "Delete*From [Accounts]"
         DoCmd.OpenTable "Results", acViewNormal, acEdit
         DoCmd.runSQL "Delete*From [Results]"
         'Count Rows of data in Excel file
         lastrow = xlwrksht.Cells(xlwrksht.Rows.Count, 1).End(xlUp).Row
         'Import data from Excel
         strExcelPath = filename
         Call DoCmd.TransferSpreadsheet(acImport, _
         acSpreadsheetTypeExcel12, "Accounts", strExcelPath, _
         True, "Accounts!A:A" & lastrow)
        'Save and Close Workbook
         wb.Close SaveChanges:=True
             'Ensure Workbook has closed before moving on to next line of code
              DoEvents
             'Refresh Data
              DoCmd.SelectObject acTable, "Accounts"
              DoCmd.Requery
              DoCmd.GoToRecord acDataTable, "Accounts", acLast
                 'Run the Dir QRY to populate info for selected accounts
                  Call RunDIR
filename = Dir()
Loop
app.Quit
End Sub

[ranman256]

use FileSystemObjects….
usage:
ImportAllFilesInDir "c:\folder"

'------------
Public Sub ImportAllFilesInDir(ByVal pvDir)
'------------
Dim vFil, vTargT
Dim i As Integer
Dim sTbl As String, sSql As String
Dim FSO
Dim oFolder, oFile
DoCmd.SetWarnings false
On Error GoTo errImp
If Right(pvDir, 1) <> "\" Then pvDir = pvDir & "\"
sTbl = "tData"
Set db = CurrentDb
Set FSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = FSO.GetFolder(pvDir)
For Each oFile In oFolder.Files
    vFil =  oFile
    If InStr(sfile, ".xls") > 0 Then      'ONLY DO EXCEL FILES
          DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, sTbl, vFil, True
    end if   
Next
Set FSO = Nothing
Set oFile = Nothing
Set oFolder = Nothing
DoCmd.SetWarnings True
Exit Sub
errImp:
MsgBox Err.Description, vbCritical, "clsImport:ImportData()" & Err
End If
Exit Sub
Resume
End Sub

[UPSDuder]

use FileSystemObjects….
usage:
ImportAllFilesInDir "c:\folder"

I actually found that you have answered very similar questions on this board before. I am new to vba and Cannot quite read your code, as you can see mine is very basic. I cannot decipher how I am to call out my directory "ImportAllFilesInDir "c:\folder"" is not in the code where I can just replace the path.

[Micron]

It's not obvious how you're initiating the code in the first place. Maybe that's why it wasn't addressed, or maybe it comes down to a responder's style. Your code could start with a button click event and deal with everything there, or your initiating code could pass the directory to the procedure given because it is required as written: ByVal pvDir

Thus maybe you have
Private Sub SomeButton_Click()

then you call the other sub and pass the directory name, which you DO in fact have. You've declared a variable for it in your posted code. Either assign it to a variable and pass the variable, or just pass the directory, as in

ImportAllFilesInDir("C:\Users\mcz0pjy\Desktop\Trac ker\Templates")

simply using the sub name will cause it to run. Note that the provided code defines a table to import to a specific table (whatever sTable is) AND it will only handle xls files as written (not xlsx, xlsm, etc)

Furthermore, re your posted code,
- you don't have to open a table to delete from it in code any more than you would have to open a table in the navigation pane before running some query on it. You don't do that now, right?

- you don't have to open a workbook to use transferspreadsheet method. You must have pieced together automation code that's only needed to work on open workbooks. Often that's because transferspreadsheet method can mess up data formatting between the two apps, but it's not the only reason.

-your initial problem looks like the use of ?? as they're not being interpreted as wild cards, which is probably what you expected. You have to use more code in an IF block that uses some form of OR logic or an expression to look at the file extension. Not that I would stick with your first procedure (because of the unnecessary automation).

Long winded answer, but hopefully you picked up some knowledge crumbs. One might be the file dialog (msoFiledialogFolderPicker) and it's cousin...