Gather all file names from a file path

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
hye there,

is there a way to write code in a module to import all .xls file name from a static filepath.

append to a tbl_AllxlsFiles.

it kind of a strange request but i really dont know where to start except for:

define the FilePath....

<gathere the file names>

use an insert into table stepping thru the recordset.

thanks
tuktuk
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
well this is definitely getting me close...


usign the code form the previous post i run into and error on the "Specification Name".

i've been unsuccessful on locating what that really mean. is it the table where all of my import will go to.....i actually want them to be seperate file.

thanks
Tuk
 

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
here is my adapted code
Code:
Public Sub ImportAllFilesFromPath()

Dim strfile As String
Dim theFilePath As String
Dim theFileName As String

ChDir ("G:\FDCC04\HOME\Dept\Me\AdHocReporting\dtf\IMPORT_Test\")
theFilePath = "G:\FDCC04\HOME\Dept\Me\AdHocReporting\dtf\IMPORT_Test\"
strfile = Dir("*.csv")
Do While Len(strfile) > 0
theFileName = Left(Dir("*.CSV"), Len(Dir("*.CSV")) - 4)
DoCmd.TransferText acImportFixed, "Specs", theFileName, theFilePath & strfile, True
strfile = Dir
Loop
End Sub
[\code]

i is apparently not location the file path.....that is as i step thru it give me a ".csv" that is located in MyDocument.

i still am unable to import the file it find as well.  error "you cannont import this file"

thanks
tuk
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

i've been unsuccessful on locating what that really mean. is it the table where all of my import will go to.....i actually want them to be seperate file.
Try manually importing one file using the Import Wizard. When you get to the last step, instead of clicking "Finish", click "Advanced" and then select the "Save As" option, and name this importing "mapping" (also known as an "Import Specification"). Remember this name.

Now, when importing all the files, this is the Import Specification you want to use. So put this name in for that argument.
 

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
well im still trying to figure out how to import all files form a file path.

i'm now having issues on the "Do While Len(strfile) > 0" portion of my code above.

that is, i am nowign using a different database. is there a Refernce that needs to be set to location the "*.csv" file on the lan.

as i step thru it is not recognizing the cvs file that are located on the defined file path.

do i need the ChDir statement?

thanks
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If this is a network try using the full UNC path for the folder.

If you use the correct path you shouldn't need ChDir.
 

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
yes it is a network.

so i defined my "theFilePath" to the full UNC path....but still it doesn't recognixe the .csv's.

does it matter is my UNC path (ie folder names) contain numbers? does the code have to be a Public/Private sub ....maybe a Function.....im streaching here?

tuk
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
tuk

What exact path did you use?

When you change *.csv to *.* are any files found?

Are you using the full path with Dir?
 

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
here is my complete code:

Code:
Public Sub ImportAllFilesFromPath()

Dim strfile As String
Dim theFilePath As String
Dim theFileName As String

theFilePath = "G:\TUKTUK\3BrandReporting\AgentLevel\36MonthHistorical\"
strfile = Dir("*.csv")

Do While Len(strfile) > 0
theFileName = Left(Dir("*.CSV"), Len(Dir("*.CSV")) - 4)
DoCmd.TransferText acImportDelim, "Import Specification", theFileName, theFilePath & strfile, True

strfile = Dir
Loop
End Sub

well when i changed the *.csv to *.* files are found but i they are not part of my defined path. it actually is going to a different drive.

see above for the full path with Dir...not sure i guess. i really dont understatn Dir fcn i guess.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,255
Messages
5,600,554
Members
414,388
Latest member
Pkmep4

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
Top