Importing data loop

FinanceGuy007

New Member
Joined
Sep 20, 2017
Messages
6
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
usage:
ImportAllFilesInDir "c:\folder"

Code:
'------------
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top