Getting counts from a bunch of files

Sandeep Warrier

Well-known Member
Joined
Oct 31, 2008
Messages
2,672
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.
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Joined
Jul 30, 2006
Messages
3,656
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
 

pavin

Active Member
Joined
Jan 20, 2009
Messages
308
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:

Sandeep Warrier

Well-known Member
Joined
Oct 31, 2008
Messages
2,672
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.
 

pavin

Active Member
Joined
Jan 20, 2009
Messages
308

ADVERTISEMENT

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.
 
Joined
Jul 30, 2006
Messages
3,656
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
 
Joined
Jul 30, 2006
Messages
3,656
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
 

Sandeep Warrier

Well-known Member
Joined
Oct 31, 2008
Messages
2,672
Hey Stan...

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

Regards,
Sandeep.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,574
Members
414,390
Latest member
plimbu

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
Top