Importing and merging Excel files by using a macro

[ericlbt]

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

 

[JBeaucaire]

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?

 

[JBeaucaire]

Well, change the last three lines of code to this so that it names the
imported sheet properly:

============
Sheets(1).Copy after:=TheBook.Sheets(TheBook.Sheets.Count)
Workbooks(TheString).Close False
TheBook.Sheets(TheBook.Sheets.Count).Name = WorksheetFunction.Text(TheDate,
"MMDDYY")
============

 

[ericlbt]

Hi,
Thank you very much.
The macro is working very well.

It would be perfect if the imported file/sheet could replace the old one and
not be added to the existing ones.
04.04.09 -> one sheet
04.05.09 -> one sheet after the 04.04.09 one
What I would love:
04.05.09 -> one sheet (no more 04.04.09)

Thank you.

Have a nice day.

 

[JBeaucaire]

Well, that's doable, just add these three lines of code at the bottom. It
will delete next to last sheet in the book, which should be the last sheet
you imported, right?

==========
Application.DisplayAlerts = False
TheBook.Sheets(TheBook.Sheets.Count - 1).Delete
Application.DisplayAlerts = True
==========

Does this help?

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