Looping through files to retrieve the count of usernames based on date and update in the Master tracker.



I have a master tracker file includes usernames(in column A) with corresponding dates at the top(starts form C) in every month as listed.There are around 20 odd office files generated daily, and users working on each file creates ID and that is recorded in column BI.So I need to track the amount of work done by each user just by taking the count of usernames in BI on that day and update the tracker by matching the corresponding date. 

I tried to loop through the files and couldnt figure further logic to record the count.

Your guidance/suggestion would be helpful.

Dim strDirContainingFiles As String, strFile As String, strFilePath As String
    Dim wbkDst As Workbook, wbkSrc As Workbook
    Dim wksDst As Worksheet, wksSrc As Worksheet
    Dim c1, c2, c3, c4 As Long 'counter for each user
    Dim lngIdx As Long, lngSrcLastRow As Long, _
    lngSrcLastCol As Long, lngDstLastRow As Long, _
        lngDstLastCol As Long, lngDstFirstFileRow As Long
    Dim rngSrc As Range, rngDst As Range, rngFile As Range

    Dim colFileNames As Collection
    Set colFileNames = New Collection
b = InputBox("Enter the folder name")
a = "F\Shopping\Specialists\Master Copy\Daily list\Split files\Archive\"
    'Set references up-front
    strDirContainingFiles = a & b
    Set wbkDst = ThisWorkbook '<~ Dst is short for destination
    Set wksDst = ActiveSheet

    'Store all of the file names in a collection
    strFile = Dir(strDirContainingFiles & "\*.xls*")
If Len(strFile) <= 0 Then
            MsgBox "Files not found"
 Exit Sub
End If
    Do While Len(strFile) > 0
        colFileNames.Add Item:=strFile
        strFile = Dir

    ''CHECKPOINT: make sure colFileNames has the file names
'    Dim varDebug As Variant
'    For Each varDebug In colFileNames
'        Debug.Print varDebug
'    Next varDebug

    'start looping through the "source" files
    For lngIdx = 1 To colFileNames.Count

        'Assign the file path
        strFilePath = strDirContainingFiles & "\" & colFileNames(lngIdx)
        'Open the workbook and store a reference to the data sheet
        Application.DisplayAlerts = False
        Set wbkSrc = Workbooks.Open(strFilePath, ReadOnly:=True, corruptload:=xlRepairFile)
        Application.DisplayAlerts = True
        Set wksSrc = wbkSrc.Worksheets("SHOPPING LISTING") ' change based on your Sheet name
        wksSrc.Columns.EntireColumn.Hidden = False


Of course, there is a difference between the concatenating ampersand (&) and the accumulating plus sign (+). Excel and VBA often forgive you for not knowing but why should you wait for either to guess your intention wrongly? Use the ampersand to concatenate strings, like "motor" & "car" = "motorcar". Use the plus sign for addition of numbers, like MONTH(A3) + 1 = 4.
Variatus (rep: 4402) May 28, '20 at 9:03 pm
Add to Discussion


Selected Answer

Yours is a big project with amy questions. In this thread I will deal with only one of them, looping through all files in a folder. Here is the basic code.

Sub Report()
    ' 039
    Const DirName       As String = "F:\Shopping\Specialists\Master Copy\Daily list\Split files\Archive\"
    Const Ext           As String = "*.xls*"    'File extension

    Dim NextFn          As String               ' name of the naext file to process

    NextFn = Dir(DirName & Ext)
    Do While NextFn <> ""
        Debug.Print NextFn                      ' for testing purposes (remove when done)

        NextFn = Dir

    MsgBox "All eligible workbooks were processed", _
           vbInformation, "Execution report"
End Sub

Observe that I added a colon after the F in the constant DirName. If F is a drive letter the colon is necessary. If it's the name of a directory, a drive letter must precede it.

Look at the extension. "*xls*" will include xlsm, xlsx, xlst, xlsa, xlst and xlsb files. The overwhelming probability is that you don't have such files in that folder. But it's even more sure that, if they exist, you don't wnat thme to be opened because they don't contain a worksheet "Shoppin Listing", and if they do, the information in it has no business in your report. I recommend to consider replacing the final * with a hard letter if possible.

It's not clear from your question what you want to extract from those files. One thing that is already very clear is that you don't need to make any columns visible because nobody will look at them. In fact, you want the entire report prepared invisibly. So, the question is whether to open the files at all.

Accordingly, the your question will be to either open and extract or extract without opening. Please make that a new thread. After that you will come to the question what to do with the information you obtain. It has no direct relationship with what we are discussing here.



Thanks Variatus. I never knew I could actually read data without opening files, I would love to learn about it.
Dr Liss (rep: 12) May 28, '20 at 11:54 pm
Depending upon what data you want to extract, what kind of search for it you need to employ, you will have to decide whether to open or not. Of course, opening each workbook will take more code and more time. A simple count should be possible without opening.
Variatus (rep: 4402) May 29, '20 at 5:39 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login