Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merge /Update Macro
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merge /Update Macro
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merge /Update Macro
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" wrote: 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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merge /Update Macro
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" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merge /Update Macro
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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merge /Update Macro
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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merge /Update Macro
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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merge /Update Macro
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 "Ananth" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merge Macro | Excel Discussion (Misc queries) | |||
need help to update macro to office 2007 macro enabled workbook | Excel Discussion (Misc queries) | |||
how do i get my mail merge to update the data source at each merge | Excel Discussion (Misc queries) | |||
Merge, update, and add only new entries into a list from other she | Excel Worksheet Functions | |||
How do you merge two spreadsheets to update data. | Excel Discussion (Misc queries) |