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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
tuk

What exact path did you use?

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

Are you using the full path with Dir?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,585
Messages
6,114,520
Members
448,575
Latest member
hycrow

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