Getting counts from a bunch of files

Sandeep Warrier

Well-known Member
Joined
Oct 31, 2008
Messages
2,679
Hey All,

I have a folder that contains about 200 Excel Files. I would like to have the total count of blank cells in column BH and the total count of the cells containing http in the same column. To get the blanks, only the cells within the used range need to be looked at.

Any ideas on how to go about it?

Thanks,
Sandeep.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sandeep,

I have some code that has been tested, and is ready to go.

What is the full path to the 200 workbooks?

Is there only one worksheet in each workbook?

If not, is the worksheet name in the 200 workbooks the same? If so, what is the worksheet name?


What is the sheetname in the workbook that will be receiving the counts?


Have a great day,
Stan
 
Upvote 0
Hi Sandeep,

The following code will pop a message of the count of Excel file in the default location and the count of blanks in the column BH Along with the count of Hyperlinked cells in the file opened. However might require some changes to the code. Please mention the details asked by Stan.

Code:
This_workbook = ActiveWorkbook.Name
    Dim x As Integer
    With Application.FileSearch
        .LookIn = ThisWorkbook.Path
        .Filename = "*.xls"
        If .Execute > 0 Then
            MsgBox .FoundFiles.Count & " workbooks were found."
            For x = 1 To .FoundFiles.Count
                Workbooks.Open Filename:=.FoundFiles(x)
                New_File = ActiveWorkbook.Name
 ' Expecting your range in col A contains the lastCell with the data in the sheet
                Last_Row = Range("A65536").End(xlUp).Row
                If Last_Row < 2 Then GoTo Line1
                On Error Resume Next
                    Blank_Cells = Range("BH2:BH" & Last_Row).SpecialCells(xlCellTypeBlanks).Count
                    Linked_Cells = Range("BH2:BH" & Last_Row).Hyperlinks.Count
                    MsgBox Blank_Cells
                    MsgBox Linked_Cells
Line1:
                    Application.DisplayAlerts = False
                    Workbooks(New_File).Close
                    Application.DisplayAlerts = True
                    Workbooks(This_workbook).Activate
            Next x
        Else
            MsgBox "No workbooks were found."
        End If
    End With
 
Last edited:
Upvote 0
Hey Stan

The full path is \\Indis01old\Quest Databases\Regulatabase Data\

Most of the sheets contain more than 1 sheet. The required sheets name is Compiled Data. I would like to create a new workbook that has all the counts in it.


Hey pavin

Linked_Cells = Range("BH2:BH" & Last_Row).Hyperlinks.Count would count all cells with hyperlinks.

The range contains some hyperlinks to files stored on the server as well as those pointing to a webpage. I need counts of only those pointing to webpages and hence the count of those cells containing http.
Also your code requires the result workbook to be in the same path as the others. This is not possible. However I can change the code to look in a particular folder.



Thanks a lot for your replies.

Regards,
Sandeep.
 
Upvote 0
Hey Sandeep,

You can use this instead, if you want to check for http

Linked_Cells = Application.WorksheetFunction.CountIf(Range("BH2:BH" & Last_Row),"\\http.*")

Hope this helps.
 
Upvote 0
Sandeep,

Here you go.

Each of the four test files contain two worksheets with different start and end rows, similar to:


Excel Workbook
BH
1
2
3http://www.mrexcel.com/forum/showthread.php?#38;t=380869
4http://www.mrexcel.com/forum/showthread.php?#38;t=380869
5http://www.mrexcel.com/forum/showthread.php?#38;t=380869
6http://www.mrexcel.com/forum/showthread.php?#38;t=380869
7http://www.mrexcel.com/forum/showthread.php?#38;t=380869
8http://www.mrexcel.com/forum/showthread.php?#38;t=380869
9http://www.mrexcel.com/forum/showthread.php?#38;t=380869
10http://www.mrexcel.com/forum/showthread.php?#38;t=380869
11http://www.mrexcel.com/forum/showthread.php?#38;t=380869
12
13
14
15http://www.mrexcel.com/forum/showthread.php?#38;t=380869
16http://www.mrexcel.com/forum/showthread.php?#38;t=380869
17http://www.mrexcel.com/forum/showthread.php?#38;t=380869
18http://www.mrexcel.com/forum/showthread.php?#38;t=380869
19http://www.mrexcel.com/forum/showthread.php?#38;t=380869
20
21http://www.mrexcel.com/forum/showthread.php?#38;t=380869
22http://www.mrexcel.com/forum/showthread.php?#38;t=380869
23http://www.mrexcel.com/forum/showthread.php?#38;t=380869
24http://www.mrexcel.com/forum/showthread.php?#38;t=380869
25http://www.mrexcel.com/forum/showthread.php?#38;t=380869
26http://www.mrexcel.com/forum/showthread.php?#38;t=380869
27http://www.mrexcel.com/forum/showthread.php?#38;t=380869
28http://www.mrexcel.com/forum/showthread.php?#38;t=380869
29http://www.mrexcel.com/forum/showthread.php?#38;t=380869
30
31
32
33http://www.mrexcel.com/forum/showthread.php?#38;t=380869
34http://www.mrexcel.com/forum/showthread.php?#38;t=380869
35http://www.mrexcel.com/forum/showthread.php?#38;t=380869
36http://www.mrexcel.com/forum/showthread.php?#38;t=380869
37http://www.mrexcel.com/forum/showthread.php?#38;t=380869
Sheet1



Before the macro - please setup sheet "Compiled Data" like the next screenshot:


Excel Workbook
ABC
1File NameBH blanksBH http
2
3
4
5
6
7
8
9
10
11
Compiled Data



After the macro:


Excel Workbook
ABC
1File NameBH blanksBH http
2A1234.xls728
3A1234.xls728
4A1235.xls728
5A1235.xls728
6A1236.xls728
7A1236.xls728
8A1237.xls1128
9A1237.xls1128
10
11
Compiled Data



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).

Press and hold down the 'ALT' key, and press the 'F11' key.

On the 'Insert' menu, click 'Module'.

Copy the below code, and paste it into the Module (on the right pane).


Code:
Option Explicit
Sub CountsFromFiles()
Dim NR As Long, wksFR As Long, wksLR As Long, b As Long, h As Long
Dim MyDir As String, FN As String
Dim wks As Worksheet
Dim rng As Range
Application.ScreenUpdating = False

'***** For runtime *****
MyDir = "\\Indis01old\Quest Databases\Regulatabase Data\"
'***** For runtime *****


'***** For testing *****
'MyDir = "c:\TestData\"
'***** For testing *****


FN = Dir(MyDir & "\*.xls")
NR = ThisWorkbook.Sheets("Compiled Data").Cells(Rows.Count, 1).End(xlUp).Row + 1
Do While FN <> ""
  If FN <> ThisWorkbook.Name Then
    With Workbooks.Open(MyDir & FN)
      For Each wks In ActiveWorkbook.Worksheets
        With wks
          .Select
          ThisWorkbook.Sheets("Compiled Data").Range("A" & NR) = FN
          If .Cells(1, "BH") <> "" Then
            wksFR = 1
          Else
            wksFR = .Cells(1, "BH").End(xlDown).Row
          End If
          wksLR = .Cells(Rows.Count, "BH").End(xlUp).Row
          Set rng = wks.Range(.Cells(wksFR, "BH"), Cells(wksLR, "BH"))
          b = Application.WorksheetFunction.CountBlank(rng)
          h = Application.WorksheetFunction.CountIf(rng, "*http*")
          ThisWorkbook.Sheets("Compiled Data").Range("B" & NR) = b
          ThisWorkbook.Sheets("Compiled Data").Range("C" & NR) = h
        End With
        NR = NR + 1
      Next wks
      .Close False
    End With
  End If
  FN = Dir
Loop
Range("A1:C" & NR).Columns.AutoFit
Application.ScreenUpdating = False
End Sub


Then run the "CountsFromFiles" macro.


Have a great day,
Stan
 
Upvote 0
Sandeep,

This may be better for you.

After the new macro:


Excel Workbook
ABCD
1File NameSheet NameBH blanksBH http
2A1234.xlssurvey728
3A1234.xlsSheet1728
4A1235.xlssurvey728
5A1235.xlsSheet1728
6A1236.xlssurvey728
7A1236.xlsSheet1728
8A1237.xlssurvey1128
9A1237.xlsSheet11128
10
Compiled Data




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).

Code:
Option Explicit
Sub CountsFromFiles()
Dim NR As Long, wksFR As Long, wksLR As Long, b As Long, h As Long
Dim MyDir As String, FN As String
Dim wks As Worksheet
Dim rng As Range
Application.ScreenUpdating = False

'***** For runtime *****
MyDir = "\\Indis01old\Quest Databases\Regulatabase Data\"
'***** For runtime *****

'***** For testing *****
'MyDir = "c:\TestData\"
'***** For testing *****

FN = Dir(MyDir & "\*.xls")
NR = ThisWorkbook.Sheets("Compiled Data").Cells(Rows.Count, 1).End(xlUp).Row + 1
Do While FN <> ""
  If FN <> ThisWorkbook.Name Then
    With Workbooks.Open(MyDir & FN)
      For Each wks In ActiveWorkbook.Worksheets
        With wks
          .Select
          ThisWorkbook.Sheets("Compiled Data").Range("A" & NR) = FN
          ThisWorkbook.Sheets("Compiled Data").Range("B" & NR) = wks.Name
          If .Cells(1, "BH") <> "" Then
            wksFR = 1
          Else
            wksFR = .Cells(1, "BH").End(xlDown).Row
          End If
          wksLR = .Cells(Rows.Count, "BH").End(xlUp).Row
          Set rng = wks.Range(.Cells(wksFR, "BH"), Cells(wksLR, "BH"))
          b = Application.WorksheetFunction.CountBlank(rng)
          h = Application.WorksheetFunction.CountIf(rng, "*http*")
          ThisWorkbook.Sheets("Compiled Data").Range("C" & NR) = b
          ThisWorkbook.Sheets("Compiled Data").Range("D" & NR) = h
        End With
        NR = NR + 1
      Next wks
      .Close False
    End With
  End If
  FN = Dir
Loop
Range("A1:C" & NR).Columns.AutoFit
Application.ScreenUpdating = False
End Sub


Have a great day,
Stan
 
Upvote 0
Hey Stan...

Thanks for your code.. I will try it tomorrow morning... its EOD here :).. time for some (y)

Regards,
Sandeep.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top