Exporting Data From Multiple Workbooks to One Workbook

[samsilverman]

I do environmental testing for multiple hospitals and surgery centers. I've created a master workbook in excel 2007 that includes about 7 sheets. Each sheet is for account info, testing areas, billing, and reports. In the testing areas sheet, I've used a formula to compute in column F a return date for each row based on when the area in that row was tested AND based on that area's yearly schedule (quarterly, semi-annually or annually). The date tested info is in column E of each row and the yearly schedule is in column H of each row.

Every client has their own workbook created from the master workbook template. I would like to export the return dates data in column F of each workbook into a single workbook that would tell me what testing I have to do for each client in any given month so I don't have to go to each workbook and make a list manually. So, not only the date would have to show, but also all the other info in that row (acct number, location, charge, etc). BTW, the data in this testing areas sheet I want info from is not in a "table"...just in cells.

Any ideas? ray: I'm a newbie who has learned a great deal in the last month about Excel, but still has a lot to learn. I can, however, follow instructions pretty well.

Thanks!!

 

[hiker95]

samsilverman,

Sheet "TEST AREAS" before the macro:

Excel Workbook
	B	C	D	E	F	G	H
2	Work for month of	Work for Year of
3	Aug	2010
4
5	Account	Facility Name	Location	Hazardous Chemical	Next Date	Next Charge	Filename
6
7
8
9
10
11
12
13
14
15
16
TEST AREAS

After the macro, before we remove rows where date Month is not equal to cell B3:

Excel Workbook
	B	C	D	E	F	G	H
2	Work for month of	Work for Year of
3	Aug	2010
4
5	Account	Facility Name	Location	Hazardous Chemical	Next Date	Next Charge	Filename
6	0238A-1	samsilverman1	PROCEDURE ROOMS	AIRFLOWS (ROOM)	Feb-18-10	$200.00	samsilverman1_master_08.18.09.xlsx
7	0238F-1	samsilverman1	SUBSTERILE 1-2	FORMALDEHYDE	Aug-18-10	$175.00	samsilverman1_master_08.18.09.xlsx
8	0238F-2	samsilverman2	SUBSTERILE 3-MINOR	FORMALDEHYDE	Aug-18-10	$175.00	samsilverman2_master_08.18.09.xlsx
9	0238F-3	samsilverman2	PROCEDURE ROOM 2	FORMALDEHYDE	Aug-18-10	$175.00	samsilverman2_master_08.18.09.xlsx
10	0238HYD-1	samsilverman2	CLEAN UTILITY	HYDROGEN PEROXIDE	Feb-18-10	$500.00	samsilverman2_master_08.18.09.xlsx
11	0238MA-1	samsilverman3	ENTIRE FACILITY	MEDICAL PIPING SYS	Aug-18-10	$500.00	samsilverman3_master_08.18.09.xlsx
12	0238N-1	samsilverman3	SURGERY ROOMS	NITROUS OXIDE	Feb-02-10	$600.00	samsilverman3_master_08.18.09.xlsx
13	0238PE-1	samsilverman3	CLEAN UTILITY	PERACETIC ACID	Aug-18-10	$175.00	samsilverman3_master_08.18.09.xlsx
14	0238VOC-1	samsilverman3	DECONTAMINATION	VOLATILE ORGANIC COM	Aug-18-10	$175.00	samsilverman3_master_08.18.09.xlsx
15	0238VOC-2	samsilverman3	PROCEDURE ROOMS 1 & 2 SUBS	VOLATILE ORGANIC COM	Aug-18-10	$175.00	samsilverman3_master_08.18.09.xlsx
16
TEST AREAS

After the macro, after we remove rows where date Month is not equal to cell B3:

Excel Workbook
	B	C	D	E	F	G	H
2	Work for month of	Work for Year of
3	Aug	2010
4
5	Account	Facility Name	Location	Hazardous Chemical	Next Date	Next Charge	Filename
6	0238F-1	samsilverman1	SUBSTERILE 1-2	FORMALDEHYDE	Aug-18-10	$175.00	samsilverman1_master_08.18.09.xlsx
7	0238F-2	samsilverman2	SUBSTERILE 3-MINOR	FORMALDEHYDE	Aug-18-10	$175.00	samsilverman2_master_08.18.09.xlsx
8	0238F-3	samsilverman2	PROCEDURE ROOM 2	FORMALDEHYDE	Aug-18-10	$175.00	samsilverman2_master_08.18.09.xlsx
9	0238MA-1	samsilverman3	ENTIRE FACILITY	MEDICAL PIPING SYS	Aug-18-10	$500.00	samsilverman3_master_08.18.09.xlsx
10	0238PE-1	samsilverman3	CLEAN UTILITY	PERACETIC ACID	Aug-18-10	$175.00	samsilverman3_master_08.18.09.xlsx
11	0238VOC-1	samsilverman3	DECONTAMINATION	VOLATILE ORGANIC COM	Aug-18-10	$175.00	samsilverman3_master_08.18.09.xlsx
12	0238VOC-2	samsilverman3	PROCEDURE ROOMS 1 & 2 SUBS	VOLATILE ORGANIC COM	Aug-18-10	$175.00	samsilverman3_master_08.18.09.xlsx
13
14
15
16
TEST AREAS

Will be back soon with the code.

 

[hiker95]

samsilverman,

Here you go.

The macro will check if cells B3 and C3 in sheets "TEST AREAS" in your summary workbook, contain the three character code for month (Jan, Feb,...,Dec), and year (2009, 2010...). If not, you will get a message, and the macro will terminate.

If the directory/folder "C:\EDT\YYYY\MMM\" does not exist you will get a message, and the macro will terminate.



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub GetMyData()
' hiker95, 11/11/2009
Dim MyDir As String, MyYear As String, MyMonth As String, FN As String
Dim LR As Long, LR2 As Long, a As Long
Sheets("TEST AREAS").Select
If Sheets("TEST AREAS").Range("B3") = "" Or Sheets("TEST AREAS").Range("C3") = "" Then
  MsgBox "Cell B3 'Work for month of', and/or cell C3 'Work for Year of' is/are blank - macro terminated!"
  Exit Sub
End If
Application.ScreenUpdating = False
MyYear = Sheets("TEST AREAS").Range("C3") - 1
MyMonth = Sheets("TEST AREAS").Range("B3")
MyDir = "C:\EDT\" & MyYear & "\" & MyMonth & "\"
FN = Dir(MyDir & "\*.xlsx")
With ThisWorkbook.Sheets("TEST AREAS")
  If CheckFolderExists(MyDir) = True Then
    'Do nothing - continue
  Else
    GoTo ErrorRoutine
  End If
  Do While FN <> ""
    LR = ThisWorkbook.Sheets("TEST AREAS").Cells(Rows.Count, "B").End(xlUp).Row
    If FN <> ThisWorkbook.Name Then
      With Workbooks.Open(MyDir & FN)
        With .Sheets("TEST AREAS")
          LR2 = .Cells(Rows.Count, "B").End(xlUp).Row
          .Range("B6:B" & LR2).Copy ThisWorkbook.Sheets("TEST AREAS").Range("B" & LR + 1)
          ThisWorkbook.Sheets("TEST AREAS").Range("C" & LR + 1 & ":C" & LR + 1 + LR2 - 6) = Left(FN, WorksheetFunction.Find("_", FN, 1) - 1)
          .Range("C6:C" & LR2).Copy ThisWorkbook.Sheets("TEST AREAS").Range("D" & LR + 1)
          .Range("D6:D" & LR2).Copy ThisWorkbook.Sheets("TEST AREAS").Range("E" & LR + 1)
          ThisWorkbook.Sheets("TEST AREAS").Range("F" & LR + 1 & ":F" & LR + 1 + LR2 - 6).NumberFormat = "mmm-dd-yy"
          .Range("F6:F" & LR2).Copy
          With ThisWorkbook.Sheets("TEST AREAS").Range("F" & LR + 1)
            .PasteSpecial xlPasteValues
          End With
          .Range("G6:G" & LR2).Copy ThisWorkbook.Sheets("TEST AREAS").Range("G" & LR + 1)
          ThisWorkbook.Sheets("TEST AREAS").Range("H" & LR + 1 & ":H" & LR + 1 + LR2 - 6) = FN
        End With
        .Close False
      End With
    End If
    FN = Dir
  Loop
  LR = ThisWorkbook.Sheets("TEST AREAS").Cells(Rows.Count, "E").End(xlUp).Row
  For a = LR To 6 Step -1
    If WorksheetFunction.Text(Range("F" & a), "mmm") <> Range("B3") Then Rows(a).EntireRow.Delete
  Next a
End With
Range("D3").Select
Application.ScreenUpdating = True
Exit Sub
ErrorRoutine:
Application.ScreenUpdating = True
MsgBox "The following path " & MyDir & " was not found - macro terminated!"
End Sub

Function CheckFolderExists(strPath As String) As Boolean
On Error Resume Next
Err.Clear
ChDir strPath
If Err.Number = 0 Then CheckFolderExists = True
End Function

Then run the "GetMyData" macro.

 

[samsilverman]

It didn't seem to work. I got an error box with "Run-time error - '9': Subscript out of Range" message in it.

That being said, your last explanation makes me wonder if I am explaining this whole thing correctly. So, here's another shot.

When I service a client I produce a workbook from a template copy. The template is called Master Database. When I service the client for the first time, it gets saved with that client's info as "clientname_master_mm.dd.yy.xlsx". Let's say I service Client A on Jan 2, 2009. I would enter in the data and then I would save the workbook as "ClientA_master_01.02.09.xlsx" in the folder C:\EDT\2009\JAN. I then service the client again on April 4 and July 10 of 2009. ClientA gets a new workbook for each visit saved as "ClientA_master_04.04.09.xlsx" and "ClientA_master_07.10.09.xlsx" saved in folders C:\EDT\2009\APR and C:\EDT\2009\JUL. Now, it is time to service Client A in October of 2009 and I want to search through it's workbooks to see what is due.

So, the Macro in the Summary Workbook (which I would call 'Monthly Due.xlsx") would preferably look through all of Client A's workbooks in C:\EDT and return only the work that is due in October 2009. It would search for and copy all October 2009 dates it found in Column F of the Test Areas sheet of any Client A's workbooks found in C:\EDT. I, of course, would want it to do this for all my clients I need to service in OCT 2009. I also would want to repeat this action every month I visit my clients.

To cut down the number of workbooks the summary workbook's macro would have to look through, I would move any folders out of C:\EDT that were older than a year. A year back of client workbooks from the target month is the limit of what I need the Macro to search. For instance, I would have serviced Client A in Oct of 2008 and there may have been an area that wasn't due until Oct 2009. But, there would never be anything serviced in Oct 2008 that would be due AFTER Oct 2009. Most areas are due again in a 3, 6 or 12 months. A very few areas are tested once and have no return date assigned.

I hope I have explained this all correctly. I certainly do not want to wear out your gracious assistance. Again, thank you for your patience. I certainly don't want this coming across on the forum as correcting you...just trying to re-explain.

I have also posted a diagram on my website if it would help to visualize. The address is http://edtpro.com/databasemacro.htm

 

[hiker95]

samsilverman,

See my Private Message to you (top right hand corner of MrExcel, Welcome, samsilverman., "Private Messages:".

 

[samsilverman]

bump

 

[hiker95]

samsilverman,

The more I read, the more I get confused.

When I service a client I produce a workbook from a template copy. The template is called Master Database. When I service the client for the first time, it gets saved with that client's info as "clientname_master_mm.dd.yy.xlsx". Let's say I service Client A on Jan 2, 2009. I would enter in the data and then I would save the workbook as "ClientA_master_01.02.09.xlsx" in the folder C:\EDT\2009\JAN. I then service the client again on April 4 and July 10 of 2009. ClientA gets a new workbook for each visit saved as "ClientA_master_04.04.09.xlsx" and "ClientA_master_07.10.09.xlsx" saved in folders C:\EDT\2009\APR and C:\EDT\2009\JUL. Now, it is time to service Client A in October of 2009 and I want to search through it's workbooks to see what is due.

Excel Workbook
	B	C	D	E	F	G	H
2	Work for month of	Work for client		Monthly Due.xlsm
3	Aug	clienta
4
5	Account	Facility Name	Location	Hazardous Chemical	Next Date	Next Charge	Filename
6
TEST AREAS

The client's name you would enter into cell C3 would have to match the client named files in all the folders/sub-folders in C:\EDT :
clienta_master_01.02.09.xlsx
clienta_master_04.04.09.xlsx
clienta_master_07.10.09.xlsx


Then, after all the informaiton is pulled into Monthly Due.xlsm, into worksheet TEST AREAS, the macro could remove all the entries where column F, Next Date does not contain cell B3's information Aug.

Is the above scenario correct?

 

[samsilverman]

I don't think it is correct yet. I feel embarrassed for not being able to communicate it well. But, since you are still trying, I will too. I'll be as specific as I can. Sorry for the length, but skip any narrative that isn't helpful. (There is a summary at the end in RED if you want to see if that makes it click).

I have about 50 clients (hospitals, surgery centers, rehab centers). Some of these clients are serviced quarterly, some semi-annually and some annually. A very few are one time clients or infrequent clients. Most of my clients are seen quarterly for different areas that need testing.

Recently I created what I call a Master Database in Excel. It is a workbook with several worksheets (Account, Test Areas, Billing, Generic Ventilation, Generic Chemical, Patient Rooms and Isolation Rooms). There is also a hidden sheet with lists for formulas and drop down lists.

Because certain state and federal regulations require it, I will see my clients in the same month of each quarter. So, if I see ClientA in Jan, I would return in Apr, Jul and Oct of that year as well. The schedule for the next year would be the same. Most of the clients follow that same formula. If I only see ClientB semi-annually, it would still be in the same month position of the quarter (ie Jan, Jul). This is the same for almost all clients, no matter which months they are seen. It follows the same pattern for any regularly serviced client.

Now, when I go to see ClientA for the first time (say on Jan 10th), I want to open up the Master Database workbook and start filling in information in the appropriate sheets. Of course, the Master Database is just my template workbook I use for all my clients, so I am going to save it (after putting in the data) as "ClientA_Master_01.10.2009.xlsx" and save it to C:\EDT\2009\JAN. This workbook now contains the address, contact info, and any test data I read when I serviced them. When I go back the next time (say on Apr 7th), I will use that new file as my template for ClientA because it already has the account info, test areas and other data there. But, this time I will save the file as "ClientA_Master_04.07.2009.xlsx"and save it to C:\EDT\2009\MAY. This will happen for every client I service. I still have the master database file to start up any new client, since that file is a template.

After all that, the C:\EDT\2009 folder would have 12 months of folders each with different client's master workbooks for those months inside. For the most part month FEB's folder contents will look like MAY, AUG and NOV. JAN's folder contents will look a lot like APR, JUL and OCT. MAR's folder contents will look a lot like JUN, SEP and DEC. This is because I see mostly the same clients at about the same time every quarter. About the only difference you’ll see from folder to folder is the date part of the filename.

Now, say it's mid December 2009 and I am wanting to plan my calendar and route for January 2010. I have to know what is due in January 2010 for ClientA (and any other client). But, that information is in all the client workbooks (in the Test Areas sheet) I produced for the last 12 months.

Some clients I serviced in Jan of 2009 may have some annual test areas that are not due until Jan 2010. I need to know what client that is, the area being tested, the chemical, the charge, etc. (basically most of the info in the test areas sheet in a given row where column F for that row reads Jan-2010). That same client may have other areas that I serviced in Apr, Jul and Oct of 2009 that are also now due in Jan 2010. So, when I call them to schedule a day and time to come by, I'm going to need to know what and how much is due to be serviced for that client. And, I need to know that about everyone else I need to visit in Jan 2010.

Therefore, if I have multiple workbooks for clients ("ClientName_Master_MM.DD.YYYY.xlsx") saved in different month folders (JAN-DEC) in the location C:\EDT\YYYY, I need a macro that will go into the folder C:\EDT and search through all workbooks up to 12 months back (based on an input date...Jan 2010). In other words, if I am searching for what work I need to do in Jan 2010, it would not have to search through workbooks older than "ClientName_Master_01.01.2009.xlsx".

I was thinking this could be mined from the TEST AREAS sheet of every workbook in C:\EDT. Using the image below, I thought it would look through every workbook for a cell (pink) in column F that had a date of (in this example) Jan-2010. Then it would return all the pertinent info adjacent to that found cell (yellow) to what I think you refer to as a SUMMARY WORKBOOK. That summary workbook would be titled "Monthly Due" for my purposes.

Excel Workbook
	A	B	C	D	E	F	G	H	I
5		Account	Location	Hazardous Chemical	Current Date	Next Date	Next Charge	Yearly Schedule
6		0238A-1	PROCEDURE ROOMS	AIRFLOWS (ROOM)	January 10, 2009	Jan-2010	$200.00	ANNUAL
7		0238F-1	SUBSTERILE 1-2	FORMALDEHYDE	January 10, 2009	Jan-2010	$175.00	ANNUAL
8		0238F-2	SUBSTERILE 3-MINOR	FORMALDEHYDE	January 10, 2009	Jan-2010	$175.00	ANNUAL
9		0238F-3	PROCEDURE ROOM 2	FORMALDEHYDE	January 10, 2009	Jan-2010	$175.00	ANNUAL
10		0238HYD-1	CLEAN UTILITY	HYDROGEN PEROXIDE	January 10, 2009	Jul-2009	$500.00	SEMI-ANNUAL
11		0238MA-1	ENTIRE FACILITY	MEDICAL PIPING SYS	January 10, 2009	Jan-2010	$500.00	ANNUAL
12		0238N-1	SURGERY ROOMS	NITROUS OXIDE	January 10, 2009	Apr-2009	$600.00	QUARTERLY
13		0238PE-1	CLEAN UTILITY	PERACETIC ACID	January 10, 2009	Jan-2010	$175.00	ANNUAL
14		0238VOC-1	DECONTAMINATION	VOLATILE ORGANIC COM	January 10, 2009	Jan-2010	$175.00	ANNUAL
15		0238VOC-2	PROCEDURE ROOMS 1 & 2 SUBS	VOLATILE ORGANIC COM	January 10, 2009	Jan-2010	$175.00	ANNUAL
TEST AREAS

The pink cells are what the macro is looking for because I need to know what is due during the month of Jan 2010. It then copies the pink and yellow cells to the summary workbook called "Monthly Due" and closes all the workbooks. The result would look like the image below (or something close to it...without the highlighted cells). The image only shows two clients for space.

Excel Workbook
	A	B	C	D	E	F	G	H
1
2		Work for MONTH of	JAN
3		Work for YEAR of	2010
4
5		Account	Facility Name	Location	Hazardous Chemical	Next Date	Next Charge
6		0238A-1	ClientA	PROCEDURE ROOMS	AIRFLOWS (ROOM)	Jan-2010	$200.00
7		0238F-1	ClientA	SUBSTERILE 1-2	FORMALDEHYDE	Jan-2010	$175.00
8		0238F-2	ClientA	SUBSTERILE 3-MINOR	FORMALDEHYDE	Jan-2010	$175.00
9		0238F-3	ClientA	PROCEDURE ROOM 2	FORMALDEHYDE	Jan-2010	$175.00
10		0238MA-1	ClientA	ENTIRE FACILITY	MEDICAL PIPING SYS	Jan-2010	$500.00
11		0238PE-1	ClientA	CLEAN UTILITY	PERACETIC ACID	Jan-2010	$175.00
12		0238VOC-1	ClientA	DECONTAMINATION	VOLATILE ORGANIC COM	Jan-2010	$175.00
13		0238VOC-2	ClientA	PROCEDURE ROOMS 1 & 2 SUBS	VOLATILE ORGANIC COM	Jan-2010	$175.00
14
15		0240A-1	ClientB	PROCEDURE ROOMS	AIRFLOWS (ROOM)	Jan-2010	$150.00
16		0240A-2	ClientB	DECONTAMINATION	AIRFLOWS (ROOM)	Jan-2010	$50.00
17		0240A-3	ClientB	ENDOSCOPY	AIRFLOWS (ROOM)	Jan-2010	$100.00
18		0240N-1	ClientB	SURGERY ROOMS	NITROUS OXIDE	Jan-2010	$600.00
MONTHLY DUE

The Facility Name in Column C would come from the filename the data came from or it could come from cell C5 of the account sheet. An added bonus would be a city and state designation for each client so I could filter the monthly due info by city or state. That data is in cells C7 and E7 respectively in each workbook's account sheet.

So, in my mind, the “Monthly Due” workbook goes into C:\EDT via a macro and looks for all workbooks with a date in the filename going back 12 months from a particular month and year that the user can input each time.. It opens those workbooks and looks in Column F of the TEST AREAS sheet for a particular month and year. If it finds that month and year, it copies the adjacent data in columns B, C, D, F and G of the TEST AREAS sheet and the data in cells C5, C7 and E7 from the ACCOUNT sheet to the appropriate cells of the “Monthly Due” workbook. It also enters a blank row after each client’s data so it is easier to read. It then closes all the workbooks except the “Monthly Due” workbook. (The underlined is a late addition and WISH. I also added it so you would scream out loud and curse my name.) <o></o>

OK. I think that’s about as good as I can explain it. Sorry if I’ve caused you sleepless night, hives or any other psycho-somatic episodes. And again, you are an Excel Saint for sticking with me this far. Even if I don’t get a solution, you have humbled me with your patience and perseverance.