Importing data loop

[FinanceGuy007]

Hi Guys,

I am using the transferSpreadsheet command in Access to import multiple tables (> 60 files) from a folder containing Excel files (each files has 1-3 imports to one table in Access). I can import files individually but I am trying to create a loop but I am not that familiar with the VB syntax to execute this.

This is what I need help with:

My filepath has multiple xlsx files in the following naming convention that I am importing into my access table:


100 - Maintenace.xlsx
101 - Sales.xlsx
102 - Human Resources Local.xlsx
201 - Manufacturing.xlsx

How to I write a loop to start at 100 (the first 3 characters of EVERY filename will ALWAYS be numerical) and loop through 999 and use as part of the transferSpreadsheet command (instead of importing each file individually by naming in my routine).

Private Sub transferPandL()



Dim fname As String




Fname = dir(H:\Budget2018\Templates & left(fname,3)??? (stuck here)




While fname >= 100 And <999 ??? (stuck here)








DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "PandLTemplate", fname, True, TablePandL




End Sub

 

[ranman256]

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 = pvDir & oFile.Name
    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