Merge /Update Macro

[Ananth]

I receive data from warehouse in charge in excel which has 52 tabs of
information. Key information is captured in the 53rd Tab called “Databaseâ€
This database has 500 rows and 26 Columns of Information. Has a header at A1
to Z1.

Column A represents Location Code (5 digit : example : "US001")

This is a monthly report submitted by Global Managers at 60 locations This
is a uniform report and the Database Tab is password protected.

As I receive the reports by mail, the 60 monthly reports are placed in a
dedicated folder : :MReports_July09

I sitting at WhqHQ, would merge all the database in a Consolidated Worksheet
manually and start analyzing., prepare reports which are all standardized.

1) I require a Merge Macro which will consolidate the “DATABASE†into one
Consolidated_DB,

2) As and when I receive the revised submission from a LOCATION, the macro
should merge the revised DATABASE and delete the old one (Key : Location Code
in Column “Aâ€)

Any help is appreciated.

 

[Otto Moehrbach]

You say:

As I receive the reports by mail, the 60 monthly reports are placed in a

dedicated folder : :MReports_July09

With the month and year in the name of this "dedicated" folder, I take it
that the folder changes for each month. Is that correct?

Will there ever be any files (workbooks, etc) in that folder that are not
the monthly reports that the macro should work with?

This "Consolidated Worksheet" into which you want to "merge" data, is that a
separate workbook? If so, in what folder will it be located?

You say you want the data from the 53rd tab of each monthly report "merged"
into the "Consolidated_DB? What is the name of this 53rd worksheet? What
do mean when you say "merge"? Do you want a simple copy/paste done? Or is
it more involved?

In item 2) of your post, you mention "the revised submission" and how it
should be merged. Do you want a search done for each Location Code, and if
found in the Consolidated_DB, delete it from the Consolidated_DB and copy
over the new one? Do you want this done one at a time or can the code
simply delete all entries having that Location Code? And how will the code
know that this one monthly report is a revised submission and not an initial
monthly report?

As you answer these questions, remember that no one reading your
post works in your office nor knows anything about your office. Please use
generic terminology whenever possible. HTH Otto

 

[Ananth]

1) Folder changes for each month : Yes
2) No other files will be in this folder.
3) Consolidated Worksheet is a Separate Workbook called :
Location_Summary_July09.xls. Located in the Same folder.
4) 53rd Tab is called “DATABASEâ€, Submissions from 60 locations that has
53rd tab “DATABASE†is to be merged into a single database in a separate
Workbook : Location_Summary_July09.xls
5) When a revised submission is rec'd, the macro should delete the existing
contents in the merged database , by deleting all the entries having the
location code and replace with contents from Database tab from the submission.
6) The database has a column for Date_update, which keeps track of version
change,
7) I have taken necessary precaution to camouflage data names and I have a
different name in office
8) I look forward to the solution
Thanks in advance

 

[Otto Moehrbach]

I'll assume that the VBA code will be located in the
Location_Summary_July09.xls workbook. I'll also assume, from what you say,
that the monthly reports will always be located in the same folder as the
Location_Summary_July09.xls workbook. If that is true, then the name of the
folder is irrelevant.

You say that the data to be copied will always be in the DATABASE sheet of
those monthly reports.

I will assume that the Location_Summary_July09.xls workbook has only one
sheet.

I will assume that all the monthly reports are .xls files.

I will write the macro to do the following. in order.

Clear (erase) the entire sheet in the Location_Summary_July09.xls workbook
from row 2 down to the last row that has data in Column A.

Loop through all the workbooks in the folder that holds the workbook that
has the VBA code, with the sole exception of the one workbook that has
"Location_Summary" as the first 16 characters in its name.

With each workbook, the code will:

Open the workbook.

Copy (How many columns?) the DATABASE sheet from row 2 down to the last
occupied cell in Column A.

Paste this into the first blank cell in Column A of the one sheet in the
Location_Summary_July09.xls workbook.

Close the monthly workbook.



I understand how you want to handle a "revised submission". But how is a
revised submission different from any other monthly report? In other words,
how do YOU know that a report is a revised submission when you are doing all
this by hand? Otto

 

[Ananth]

1) All the Assumptions are perfect (except clear/erase the database as I
would start with previous month merged database-See Option (a) )

2) The revised submission also will be in the same format. I would be
receiving this by email (after discussions /corrections) and manually placing
it in the designated folder. I would use the option (b) explained as under
for merging the database.

3) The macro should provide 2 options
(a) Batch Processing : All files in the Folder will be read and merge the
“DATABASE†Tab to a New Workbook. If the location code is present in the
merged database, those rows to be deleted first and replaced. I should have
the flexibility of using this option more than once.

(b) Interactive Processing : When this option is selected, Excel should
prompt for the file to be chosen for merging the “Database†Tab in the
revised submission. The existing rows pertaining to location code to be
deleted from the MERGED DATABASE and then replaced with the new database.

Thanks for your efforts.

 

[Otto Moehrbach]

Here is my first shot at it. Paste all the code below into a regular
module. Include the declarations at the top. Note the last declaration:

Const TheSht As String = "Otto"

Substitute the name of your sheet (the sheet in the
Location_Summary_July09.xls file) in place of Otto. Retain the quotes.

I recommend that you place 2 buttons at the top of that sheet, labeled
something like "Batch Processing" and "Interactive Processing" and assign
the appropriate macros to those buttons.

I made up some dummy files and ran this code and it appears to work as you
want. Make sure you view this post in full screen before you copy the
code. This is to avoid line wrapping in the code. HTH Otto

Option Explicit
Dim ThePath As String, TheFile As String
Dim rColA As Range, First As Range, Last As Range
Dim Dest As Range, c As Long, wb As Workbook
Dim bFileExists As Boolean, rsFullPath As String
Const TheSht As String = "Otto"

Sub BatchProcessing()
Application.ScreenUpdating = False
Set wb = ThisWorkbook
ThePath = ThisWorkbook.Path
If IsEmpty(Range("A3").Value) Then
Set Dest = Range("A3")
Set rColA = Dest
Else
Set Dest = Range("A" & Rows.Count).End(xlUp).Offset(1)
Set rColA = Range("A3", Range("A" & Rows.Count).End(xlUp))
End If
ChDir ThePath
TheFile = Dir("*.xls")
Do While TheFile <> ""
If Left(TheFile, 16) <> "Location_Summary" Then
Workbooks.Open Filename:=ThePath & "\" & TheFile
With Sheets("Database")
If Not rColA.Find(What:=.Range("A2").Value) Is Nothing Then
Set First = rColA.Find(What:=.Range("A2"),
After:=rColA(rColA.Count))
For c = 1 To 10000
If First.Offset(c).Value <> First.Value Then
Set Last = First.Offset(c - 1)
wb.Sheets(TheSht).Range(First,
Last).EntireRow.Delete
Exit For
End If
Next c
End If
.Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(,
9).Copy Dest
With wb.Sheets(TheSht)
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
Set rColA = .Range("A3", .Range("A" &
Rows.Count).End(xlUp))
End With
End With
ActiveWorkbook.Close
End If
TheFile = Dir
Loop
Application.ScreenUpdating = True
End Sub

Sub InteractiveProcessing()
Application.ScreenUpdating = False
Set wb = ThisWorkbook
ThePath = ThisWorkbook.Path
If IsEmpty(Range("A3").Value) Then
Set Dest = Range("A3")
Set rColA = Dest
Else
Set Dest = Range("A" & Rows.Count).End(xlUp).Offset(1)
Set rColA = Range("A3", Range("A" & Rows.Count).End(xlUp))
End If
TheFile = InputBox("Enter the name of the workbook from which to copy."
& Chr(13) & _
"The format must be 'FileName.xls'.")
If TheFile = "" Then
MsgBox "This program has terminated.", 16, "No Entry"
Exit Sub
End If
bFileExists = True
rsFullPath = ThePath & "\" & TheFile
bFileExists = Len(Dir$(rsFullPath))
If bFileExists = False Then
MsgBox "The file " & TheFile & " does not exist in the" & Chr(13) &
_
ThePath & " folder." & Chr(13) & _
"This program will terminate.", 16, "Entry Error"
Exit Sub
End If
Workbooks.Open Filename:=ThePath & "\" & TheFile
With Sheets("Database")
If Not rColA.Find(What:=.Range("A2").Value) Is Nothing Then
Set First = rColA.Find(What:=.Range("A2").Value)
For c = 1 To 10000
If First.Offset(c) <> First Then
Set Last = First.Offset(c - 1)
wb.Sheets(TheSht).Range(First, Last).EntireRow.Delete
Exit For
End If
Next c
End If
.Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, 9).Copy
Dest
With wb.Sheets(TheSht)
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
Set rColA = .Range("A3", .Range("A" & Rows.Count).End(xlUp))
End With
End With
ActiveWorkbook.Close
Application.ScreenUpdating = True
End Sub

 

[Ananth]

Many many thanks. I made minor corrections and made it to run to my
requirement.
It has made my life less miserable and has, overnight improved my
productivity.
I am short of words to express my gratitude and joy over your help.

Long live Otto

 

[Otto Moehrbach]

You give me a fat head with all that praise. Thanks. I like to help people
and I was able to help you so I had a good day. Otto