importing files with a macro

[JT]

I want to import a number of files into Excel with a macro
but I'm not sure where to start. I want to kick off the
macro in 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.

 

[steve]

this is not what your asking for directly, but it dose
the same thing (except it pulls the last file it finds
instead of all of them that meat your critiera, and that
the files are txt files not xls) this is something that i
set up in the past you will have to sort through it and
adjust it to fit your needs, i dont have the time to mess
with it right now. you will have to add the code that
opens the files and imports the data you want

Const uardbalData As String = "Y:\Data Files\"
Const uardbalSearch As String = "Y:\temp\*uardbal*.txt"
Const uardbalPath As String = "Y:\temp\"
Dim uardbalArray() As String
Dim uardbalFile As String


Num = 1
uardbalFile = Dir(uardbalSearch)
Do While uardbalFile <> ""
If Num = 1 Then
ReDim uardbalArray(1 To 1) As String
Else
ReDim Preserve uardbalArray(1 To Num) As String
End If
uardbalArray(Num) = uardbalFile
Num = Num + 1
uardbalFile = Dir
Loop
For Num = 1 To UBound(uardbalArray)
If uardbalFile < uardbalArray(Num) Then
uardbalFile = uardbalArray(Num)
End If
Next Num
FileCopy uardbalPath & uardbalFile, uardbalData &_
uardbalFile
Application.DisplayAlerts = False

 

[Tom Ogilvy]

xls files are opened in excel, not imported. Define import.

You can gather the filenames using the dir command

Dim wkbk as Workbook, fName as String
fName = Dir("C:\Myfolder\Unit Cost*.xls")
if fName <> "" then
do
set wkbk = workbooks.Open("C:\Myfolder\" & fName)
wkbk.worksheets(1).copy After:=thisworkbook.worksheets( _
ThisWorkbook.worksheets.count)
wkbk.close Savechanges:=False
fName = dir()
loop until fName = ""
end if