importing files with a macro

  • Thread starter Thread starter JT
  • Start date Start date
J

JT

I want to import a number of files into an Access database
with a macro but I'm not sure where to start. I want to
kick off the macro in Access (or even Excel). The macro
should go to another drive and a specific folder (based on
date) and import any file with a name that begins with
Unit Cost.

The file names will be like Unit Cost 1.xls, Unit Cost
3.xls, Unit Cost 7.xls. The will be in sequential order
but the number at the end of the file (1, 3, 7) will not
be one after another. There could (and probably will) be
gaps in the numbers.

Once the macro gets to the correct folder, it should read
down the list of files and inport each one that begins
with Unit Cost.

Thanks for the help.
 
Hi,

It looks like you should write some code to solve your
problem. Something like:

Public Function ImportFromFolder(ByVal vsFolderName As
String, ByVal vsFilePrefix As String, ByVal
vsFileExtension As String) As Integer
On Error GoTo Import_Error
Dim nFiles As Integer
Dim sWildCard As String
Dim sFileName As String
DoCmd.Hourglass True
' prepare wildcard
vsFolderName = Trim(vsFolderName)
If Right(vsFolderName, 1) <> "\" Then vsFolderName =
vsFolderName & "\"
sWildCard = vsFolderName & vsFilePrefix & "*"
vsFileExtension = Trim(vsFileExtension)
If Len(vsFileExtension) > 0 Then sWildCard = sWildCard
& "." & vsFileExtension
sFileName = Dir(sWildCard)
Do While Len(sFileName) > 0
' perform import of your files here for sFileName
sFileName = Dir
Loop
Import_Exit:
DoCmd.Hourglass False
ImportFromFolder = nFiles
Exit Function
Import_Err:
MsgBox Err.Description
Resume Import_Exit
End Function

Hope this helps
 
Back
Top