Importing data

[rkash24]

I would like to bring in various file types, txt and xls, into Access
automatically on a specified timeframe for each. The problem is that the
filenames generated from the application we use to gather the data defaults
the date and time as an extension to the filename (ie.
filename_22080922_115030.xls). Is there a way to have Access only look at a
specified number of characters at the beginning of the filename and import it?

Thanks

 

[Ken Sheridan]

You could do it in code by getting the full name of the file by means of the
Dir() function with a wildcard. Taking the example for the
TransferSpreadsheet method from Help:

DoCmd.TransferSpreadsheet acImport, 3, _
"Employees","C:\Lotus\Newemps.wk3", True, "A1:G12"

With your naming file convention this might be:

DoCmd.TransferSpreadsheet acImport, 3, _
"Employees","C:\Lotus\Newemps_22080922_115030.wk3", True, "A1:G12"

So code to do this by means of the Newemps part of the file name only would
go like this:

Const SOURCE_FOLDER = "C:\Lotus\"
Const FILE_NAME = "Newemps"
Const FILE_EXTENSION = ".wk3"

Dim strFile as String

strFile = Dir(SOURCE_FOLDER & FILE_NAME & "*" & FILE_EXTENSION)

DoCmd.TransferSpreadsheet acImport, 3, _
"Employees", SOURCE_FOLDER & strFile, True, "A1:G12"

This does assume that there is only one Newemps<date/time extension>.wk3
Lotus file in the folder of course.

Ken Sheridan
Stafford, England

 

[John W. Vinson]

I would like to bring in various file types, txt and xls, into Access
automatically on a specified timeframe for each. The problem is that the
filenames generated from the application we use to gather the data defaults
the date and time as an extension to the filename (ie.
filename_22080922_115030.xls). Is there a way to have Access only look at a
specified number of characters at the beginning of the filename and import it?

Thanks

Sure, with a bit of code. You could use the Dir function in VBA to find all
filenames with a particular start:

Dim MyFile As String
MyFile = Dir("C:\blahblahblah\desiredfile*.xls")

will return the first file in the directory blahblahblah whose name starts
with desiredfile. You could then use the TransferSpreadsheet method to import,
since you'll have the filename in MyFile.

If the folder contains multiple files of the same name, you'll need to loop,
calling Dir multiple times until you find the desired one; you may need to
parse the date and time if you want to find the most recent one.