I am just starting to get into VBA (a couple days and lots of
searching the net). I have an application that each day has report
saved based on the date.
ex. 01310701-1.dat, 01310702-1.dat

I would like to be able to have a input box asking for the date, open
any file containing that date and append them into 1 worksheet. I
have come up with the following code, from cutting and
pasting....etc......Now I have an input box, show a msg box (just so I
know it was the proper date) and its supposed to open each file in its
own worksheet (which isn't really what I want, but its a start).
However it always says no files in that directory. I believe the line
in question is FNames = Dir("varInput*.dat") where varInput is a
String that is entered as
ex. 013107

I would like to open any dat files that begin with 013107 and append
them into 1 worksheet.

Sub TestFile3()
Dim varInput As String
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
varInput = InputBox("Please Enter the Day (mmddyy)?")
SaveDriveDir = CurDir
MyPath = "c:\test"
ChDrive MyPath
ChDir MyPath
MsgBox varInput
FNames = Dir("varInput*.dat")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
Do While FNames < ""
Set mybook = Workbooks.Open(FNames)
mybook.Worksheets(1).Copy after:= _
basebook.Sheets(basebook.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = mybook.Name
On Error GoTo 0
' You can use this if you want to copy only the values
' With ActiveSheet.UsedRange
' .Value = .Value
' End With
mybook.Close False
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

Please bear with me as I am just learning this, and any help would be
greatly appreciated. Also any info on some websites that would help
in learning would be great as well.

Thanks
Rob

Hi:
You were nearly there, you have the variable as a string.

Change:

FNames = Dir("varInput*.dat")
to
FNames = Dir(varInput & "*.dat")

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"wooljr" wrote:

I am just starting to get into VBA (a couple days and lots of
searching the net). I have an application that each day has report
saved based on the date.
ex. 01310701-1.dat, 01310702-1.dat

I would like to be able to have a input box asking for the date, open
any file containing that date and append them into 1 worksheet. I
have come up with the following code, from cutting and
pasting....etc......Now I have an input box, show a msg box (just so I
know it was the proper date) and its supposed to open each file in its
own worksheet (which isn't really what I want, but its a start).
However it always says no files in that directory. I believe the line
in question is FNames = Dir("varInput*.dat") where varInput is a
String that is entered as
ex. 013107

I would like to open any dat files that begin with 013107 and append
them into 1 worksheet.

Sub TestFile3()
Dim varInput As String
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
varInput = InputBox("Please Enter the Day (mmddyy)?")
SaveDriveDir = CurDir
MyPath = "c:\test"
ChDrive MyPath
ChDir MyPath
MsgBox varInput
FNames = Dir("varInput*.dat")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
Do While FNames < ""
Set mybook = Workbooks.Open(FNames)
mybook.Worksheets(1).Copy after:= _
basebook.Sheets(basebook.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = mybook.Name
On Error GoTo 0
' You can use this if you want to copy only the values
' With ActiveSheet.UsedRange
' .Value = .Value
' End With
mybook.Close False
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

Please bear with me as I am just learning this, and any help would be
greatly appreciated. Also any info on some websites that would help
in learning would be great as well.

Thanks
Rob