Importing and merging Excel files by using a macro
 

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

Importing and merging Excel files by using a macro
 

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

Importing and merging Excel files by using a macro
 

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")
============
--
"Actually, I *am* a rocket scientist." -- JB

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


"JBeaucaire" wrote:

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

Importing and merging Excel files by using a macro
 

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" wrote:

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")
============
--
"Actually, I *am* a rocket scientist." -- JB

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


"JBeaucaire" wrote:

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

Importing and merging Excel files by using a macro
 

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.


"ericlbt" wrote:

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.