Obviously you'll need to tweak the path and such, but this macro behaves as
you've described:

===========
Option Explicit

Sub ImportByDate()
Dim TheString As String, TheDate As Date
Dim ThePath As String, TheBook As Workbook
Set TheBook = ThisWorkbook

ThePath = "C:\Documents and Settings\Jerry2\Desktop\"
TheString = Application.InputBox("Enter the date:", Title:="Date as MMDDYY")

If IsDate(TheString) Then
TheDate = DateValue(TheString)
Else
MsgBox "Invalid date"
Exit Sub
End If

TheString = "Excel_" & WorksheetFunction.Text(TheDate, "MMDDYY") & ".xls"
MsgBox "Importing the file: " & ThePath & TheString

On Error Resume Next
Workbooks.Open ThePath & TheString

If Err < 0 Then
MsgBox "The Book " & TheString & ".xls could not be found.
Aborting..."
Exit Sub
End If

Sheets(1).Copy after:=TheBook.Sheets(TheBook.Sheets.Count)
ActiveSheet.Name = TheDate
Workbooks(TheString).Close False
End Sub
===========

Does that help?

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"ericlbt" wrote:

Hello,

Here is what I am trying to achieve by using a macro :
I have an Excel file open (Excel1.xls) and I want by macro to be able to
import another Excel file (Excel_ddmmyy.xls) to it.

The difficulty is that I want to import the latest version of the
"Excel_ddmmyy.xls file".
First the macro needs to search in the correct folder ("Folder") for the
latest version of "Excel_ddmmyy.xls".
There is only one sheet in that file.
Then that file has to be exported to "Excel1.xls". So "Excel1.xls" will have
a new sheet which will be a copy of "Excel_ddmmyy.xls".
Once it's done the original "Excel_ddmmyy.xls" has to be closed.

1- in "Folder" find the latest version of "Excel_ddmmyy.xls"
2- export "Excel_ddmmyy.xls" to "Excel1.xls"
3- close "Excel_ddmmyy"

Ideally the macro would launch automatically when I open "Excel1.xls".

In case it's easier, the macro could look for "Excel_ddmmyy.xls" based on
the "ddmmyy" given by me.
So if I put 010509 in "A1" then the macro will look in "Folder" for
"Excel_010509.xls".

I hope I have been clear enough (English as you can read is not my mother
tongue!).

Thank you for your help