Gather all file names from a file path

[tuktuk]

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

 

[Norie]

Where's the UNC path?

 

[tuktuk]

i guess ii don't know what that is then. i thought that was my "theFilePath = "G:\TUKTUK\3BrandReporting\AgentLevel\36MonthHistorical\"".

tuk

 

[SydneyGeek]

UNC paths look like \\Servername\directory\directory2\file.csv

To get the UNC path for the folder, browse to it using My Network Places instead of My Computer.

Denis

 

[tuktuk]

so in my example above (or the code i mirrored) would i use:

ChDir ("\\Servername\directory\directory2\")
theFilePath = "\\Servername\directory\directory2\"

but for some reason i still cant locate the /csv

thanks
tuk

 

[Norie]

tuk

Could you please post all the code?

 

[tuktuk]

sure thing

Public Sub ImportAllFilesFromPath()

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

ChDir ("\\hqintl1.com\general\FDCC04\HOME\Dept\Me\AdHocReporting\dtf\IMPORT_Test\")
theFilePath = "\\hqintl1.com\general\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

 

[Norie]

Try using the UNC path with Dir.

ie Dir(theFilePath & "*.csv")

 

[tuktuk]

hey Norie...it worked. NICE!!!!

Now i'm attetmpting to acutally import the files from the lan.

Joe4 previously in this post told me to name the Import Spec to Mappings while importing one of the file manually.

My quesiton is can I import the first file, then run code and at the end of that code deleted the imported file.....then go to the next csv from the lan import to that table,,,,run code etc until i pass through all csvs?

i tried not setting "theFileName" in the code above and then changed the Transferstatemetn to:

"DoCmd.TransferText acImportFixed, "mappings", "ImportedFilesFormLAN", theFilePath & strfile, True"......

but that didn't work.

any ideas?

tuktuk

 

[SydneyGeek]

Check out this article. You will need to change the table, path name and import spec to suit.

Denis

 

[Norie]

Why do you not want to use the filename?

In fact you need to use something for the name of the table you are importing to.

So unless you want to import into a specific, existing table you should stick with what you've got - for now at least.

You might end up with multiple tables but at least that would be a start.

Importing the first file and then importing into the table that creates isn't totally straightforward.

It would probably be easier to create a blank table, with all correct fields etc, and import all the files into that.

Another thing you might want to consider is how are you going to identify which records come from which CSV file.

That might not be important, hard to tell without more information about the data, but in my experience it's sometimes the most important thing.