Importing Data from one Workbook(shared accross a network) into a Master Stat Workbook

[snyper189]

Hello All. First Time here and very limited with VBA code. I am an administrator for a Police Department and I use excel spreadsheets to keep statistics of officer's daily activities. So I made up a spreadsheet called a patrol log that they each file out electronically and save to a folder on the network called "Patrol Logs". They save them in the format "mm-dd-yy LastName". I then take the data from approximately 20 cells and type it into my stats WB.

My Stats WB has tabs for each officer. Left side has each date for the year and the top has headers for each stat I am tracking. so data for jan 1, 2015 starts at D5 and goes to AA5. and continues stepping by 1 all the way down to dec 31, 2015.

I want to make a Macro that looks at each officers tab, and each date and finds the file with that name, copies the data in about 20 cells, and inputs it into the proper row.

First question is, is this possible.
Second question is, if so, is it easy enough to program?

Could someone give me an example as to how to first, open a file based on the current sheet and a continuous set of dates. and then copy only a couple of the cells and input them into that corresponding row.

If I get that much, I can probably get the rest to fall into place.

I know that the following will give me the sheet name:
pName = ActiveWorkbook.Path
wbName = ActiveWorkbook.Name
shtName = ActiveSheet.Name

shtName = ActiveWorkbook.Worksheet.Name

But I can't figure out how to go about getting the date and then smushing the two together to even find the file.

 

[JLGWhiz]

If the dates in your master file are in 'jan 1, 2015' format then you would need to use the Format funtion to put them in the same format as the Officer's file name of 'mm-dd-yy'. You can use a variable to create the file name you want to open by concatenating the date and (if I understand you correctly) the sheet name, which should be the Officer's name. Be careful that the sheet name is identical to the Officer's file name with respect to spelling and capitilazation. So the code would look something like this.

'Assumes pPath has been established
If Right(pPath, 1) <> "\" Then pPath = pPath & "\"  'This puts a separator on the path end if none exits
Dim fName As String, dt As String, Nm As String
dt = Format(Range("A10").Value, "mm-dd-yy") 'The range is arbitrary, depending on which day you want to look at.
nm = ActiveSheet.Name 'should be the Officer's name as it appears on his original file.
fName = dt & " " & nm & ".xlsx" 'put it all together for the file name.  
Workbooks.Open pPath & fName

Your post shows a space between date and name.

 

[snyper189]

JLGWhiz, your code seems to be focused on one single day. That looks like a good starting point and you did get my file naming system correct. example: 01-30-15 Smith.
I was looking for a bigger macro, that would check for a file for each day of the year for all of my officers. If a file exists, pull the data from a named group of cells, and input them into the column that corresponds to that date.

A huge portion of time is consumed by manually entering these numbers for every shift, every officer, on every day of the year.

Thank you for the swift reply as well. Ill keep messing with my code to at least get the file names to compile correctly.

 

[JLGWhiz]

JLGWhiz, your code seems to be focused on one single day. That looks like a good starting point and you did get my file naming system correct. example: 01-30-15 Smith.
I was looking for a bigger macro, that would check for a file for each day of the year for all of my officers. If a file exists, pull the data from a named group of cells, and input them into the column that corresponds to that date.

A huge portion of time is consumed by manually entering these numbers for every shift, every officer, on every day of the year.

Thank you for the swift reply as well. Ill keep messing with my code to at least get the file names to compile correctly.

For me to compile the code for you, you will need to be more clear about what you mean in these two statements.

I want to make a Macro that looks at each officers tab, and each date and finds the file with that name, copies the data in about 20 cells, and inputs it into the proper row.

I was looking for a bigger macro, that would check for a file for each day of the year for all of my officers. If a file exists, pull the data from a named group of cells, and input them into the column that corresponds to that date.

Because to me, it looks like 20 x 365 data points to query. In other words clarify the expected results. If you are only trying to load the daily logs on a daily basis that would be one thing, but if you are trying to accumulate statistical data for year-to-date then that is something else. Also, i would need to know where the data to be copy resides. ie. Cells A1:A20 or Cells A1:B5, C6:D10, etc. and then where to, workbook and sheet name and cells or range. I assume it would be to the sheet in your consolidated filed, but no range was specified. Another thought, are there any sheets in your consolidated file that are not an officer's name?

 

[JLGWhiz]

This would be what the 20 x 365 looks like, but again, the copy and paste ranges are arbitrary since they were not specified in the post. You can try to modify them.

Sub copyStuff()
Dim wb As Workbook, sh As Worksheet, Ssh As Worksheet, fPath As String, fName As String, rng As Range, nm As String, dt As String
fPath = ThisWorkbook.Path
If Right(fPath, 1) <> "\" Then fPathe = fPath & "\"
    For Each sh In ThisWorkbook.Sheets 'Sequence through sheets in consolidated workbook.
        nm = sh.Name    'initialize name variable
        For i = 2 To sh.Cells(Rows.Count, 1).End(xlUp).Row
            dt = sh.Cells(i, 1) 'initialize date variable
            fName = dt & " " & nm & ".xls*"  'initialize file name variable
            On Error GoTo SKIP  'provide for bad name or date entries
            Set wb = Workbooks.Open(fPath & fName)  'Open the Officer's file for specified date.
            Set Ssh = wb.Sheets(1) 'Edit sheet name - initialize source sheet variable
            rng = Ssh.Range("B1:B20") 'This will be the range to copy - initialize target data variable
            rng.Copy sh.Cells(Rows.Count, 2).End(xlUp)(2) 'This will paste the copied range to the next avalable row beginning in column B
SKIP:
            If Err.Number > 0 Then 'report any errors
                MsgBox Err.Number & ":   " & Err.Description & vbLf & "Error occurred for workbook " & fName
                Err.Clear
            End If
        Next    'increment cell for date - this must complete all dates before sheets change.
    Next    'increment sheet
End Sub

Regards, JLG

 

[snyper189]

Sub copyStuff()
Dim wb As Workbook, sh As Worksheet, Ssh As Worksheet, fPath As String, fName As String, rng As Range, nm As String, dt As String
fPath = ThisWorkbook.Path
If Right(fPath, 1) <> "\" Then fPathe = fPath & "\"
    For Each sh In ThisWorkbook.Sheets 'Sequence through sheets in consolidated workbook.
        nm = sh.Name    'initialize name variable
        For i = 2 To sh.Cells(Rows.Count, 1).End(xlUp).Row
            dt = sh.Cells(i, 1) 'initialize date variable
            fName = dt & " " & nm & ".xls*"  'initialize file name variable
            On Error GoTo SKIP  'provide for bad name or date entries
            Set wb = Workbooks.Open(fPath & fName)  'Open the Officer's file for specified date.
            Set Ssh = wb.Sheets(1) 'Edit sheet name - initialize source sheet variable
            rng = Ssh.Range("B1:B20") 'This will be the range to copy - initialize target data variable
            rng.Copy sh.Cells(Rows.Count, 2).End(xlUp)(2) 'This will paste the copied range to the next avalable row beginning in column B
SKIP:
            If Err.Number > 0 Then 'report any errors
                MsgBox Err.Number & ":   " & Err.Description & vbLf & "Error occurred for workbook " & fName
                Err.Clear
            End If
        Next    'increment cell for date - this must complete all dates before sheets change.
    Next    'increment sheet
End Sub

This almost works. I changed the cell starting points to begin at my first cell containing a date. I formatted my dates in the 01-01-15 style. I put my test sheet as the first sheet in the workbook. I added a MsgBox to tell me what the fName it is currently trying to open. Excel is still reading the date as 01/01/15 and windows does not allow files to be saved with slashes. so I get the errors.

Ideally, ill just take out the For Each sh line because I have a lot of other sheets on the workbook that don't hold officers stats. Instead, I will put a button in the top left of each officer sheet so I can run each officer individually.

 

[snyper189]

	Total Hours	Admin Time	Zone 1
01-01-15	0
01-02-15	0
01-03-15	0
01-04-15	0
01-05-15	0

The above is what my master stat wb looks like. The Total Hours column is a formula and the rest to the right are manual entries of numbers. Excel is displaying the dates in column A as 01-01-15 but it is storing them as dates in the default excel format for a date (01/01/2015 which is actually given its date code).

EDIT: Also, in your code, I see the line where you copy the data from the log file, however I do not see a line code for pasting said data into my stats wb. Am I missing something or am I just that bad with code that its built in somewhere else and I can't see it.

 

[JLGWhiz]

EDIT: Also, in your code, I see the line where you copy the data from the log file, however I do not see a line code for pasting said data into my stats wb. Am I missing something or am I just that bad with code that its built in somewhere else and I can't see it.

The copy and paste are one line 'rng.Copy' does the copying and everything after the space is the destination. Something like a function statement.

I am looking a the date problem to see what the best solution would be.

dt = sh.Cells(i, 1) 'initialize date variable

This should have been

dt = Format(sh.Cells(i, 1).Value), "mm-dd-yy") 'initialize date variable

which should make it the right format and a string.

Also looking at the type of sheets problem.

 

[JLGWhiz]

If your other sheets in the workbook do not begin with numbers, then this might work,

For Each sh In ThisWorkbook.Sheets
	If IsNumeric(Left(sh.Name, 2)) Then
		'The body of the code
	End If
Next
End Sub

 

[snyper189]

I know I am throwing a lot of replies out but I am trying to rework everything to limit the number of queries using input boxes for start date and end date.
I came up with the following:

Sub pulldata()

Dim dwb As Workbook, wb As Workbook, sh As Worksheet, Ssh As Worksheet, fPath As String, fName As String, rng As Range, nm As String, dt As String
Dim sDate As String, eDate As String
Set dwb = ActiveWorkbook

nm = ActiveSheet.Name

fPath = "R:\Patrol Logs\"
sDate = InputBox("Start Date", , "01-01-15")
eDate = InputBox("End Date", , "12-31-15")

For i = 5 To 369
    If Cells(i, 1).Text = eDate Then
    lastrow = Cells(i, 1)
    MsgBox lastrow
End If

For e = 5 To lastrow
    
    If Cells(e, 1).Text = sDate Then
        fName = sDate & " " & nm & ".xls"
        Set wb = Workbooks.Open(fPath & fName)
        Cells(e, 5).Select
    End If

Next
Next
End Sub

I can select which days I want to look for and open up the first one. Im currently working on cycling through all the files I need based on my inputs.