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]

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.

Looks like a totally different concept. When you decide how you want to go, I suggest you start a new thread.
Regards, JLG

 

[snyper189]

I went back to use your code to merge the two together in a way. When attempting to copy the data for the file 01-01-15 Name I get a Run-time error '91' Object variable or With block variable not set.

Also, anytime there is a file that doesn't exist, the macro stops looking and gives me a Run-Time error 1004.

 

[snyper189]

I started a new thread here: http://www.mrexcel.com/forum/excel-...opy-macro-existing-files-copy-stats-file.html

Started it because I got the macro to work but have one error with the copy and paste cell referencing. In case others were trying to do that same thing. would hate to see someone else left hanging.

 

[snyper189]

I managed to get the macro working with the following code. It cycles through each sheet and only tries to open files that exist based on the sheet name and date. The only problem is that the data I need to copy is scattered throughout and not in a nice neat range. This makes the copy paste a little slow. I have no idea how to go about cleaning that up.

Sub ImportLogs()
Dim swb As Workbook, sh As Worksheet, Ssh As Worksheet, fPath As String, fName As String, ffName As String, twb As Workbook, nm As String, dt As String
fPath = "R:\Patrol Logs\Approved\"
'Set twb = ThisWorkbook

sDate = InputBox("Start Date")

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 = sDate To sh.Cells(Rows.Count, 1).End(xlUp).Row
            dt = Format((sh.Cells(i, 1).Value), "mm-dd-yy") 'initialize date variable
            fName = dt & " " & nm & ".xls"  'initialize file name variable
            ffName = fPath & fName  'sets full file name including
            '---Check whether file exists---
            If Dir(ffName) <> "" Then
                'If Cells(i, 4).Value <> "" Then
                '    sh.Range(Cells(i, 4), Cells(i, 42)).ClearContents
                'End If
                Set swb = Workbooks.Open(ffName)  'Open the Officer's file for specified date.
                Set Ssh = swb.Sheets("Officer Log") 'Edit sheet name - initialize source sheet variable
                '-------copy and paste each data set------------------
                Ssh.Range("E50").Copy
                sh.Cells(i, 4).PasteSpecial xlPasteValues
                Ssh.Range("M50").Copy
                sh.Cells(i, 5).PasteSpecial xlPasteValues
                Ssh.Range("U50").Copy
                sh.Cells(i, 6).PasteSpecial xlPasteValues
                Ssh.Range("H6").Copy
                sh.Cells(i, 7).PasteSpecial xlPasteValues
                Ssh.Range("H9").Copy
                sh.Cells(i, 8).PasteSpecial xlPasteValues
                Ssh.Range("H10").Copy
                sh.Cells(i, 9).PasteSpecial xlPasteValues
                Ssh.Range("S6:S12").Copy
                sh.Cells(i, 10).PasteSpecial xlPasteValues, Transpose:=True
                Ssh.Range("T6:T12").Copy
                sh.Cells(i, 17).PasteSpecial xlPasteValues, Transpose:=True
                Ssh.Range("Z6:Z10").Copy
                sh.Cells(i, 24).PasteSpecial xlPasteValues, Transpose:=True
                Ssh.Range("AA6:AA10").Copy
                sh.Cells(i, 29).PasteSpecial xlPasteValues, Transpose:=True
                Ssh.Range("S13").Copy
                sh.Cells(i, 34).PasteSpecial xlPasteValues
                Ssh.Range("Z11").Copy
                sh.Cells(i, 35).PasteSpecial xlPasteValues
                Ssh.Range("Z12").Copy
                sh.Cells(i, 36).PasteSpecial xlPasteValues
                Ssh.Range("Z13").Copy
                sh.Cells(i, 37).PasteSpecial xlPasteValues
                swb.Close
            Else
                'MsgBox (ffName & " does not exist.")
            End If
        Next    'increment cell for date - this must complete all dates before sheets change.
    Next    'increment sheet
End Sub