VBA Code Help Count Cells in a Different Workbook

[jdyer]

I'm looking for help writing some VBA code. I have a spreadsheet has the complete file paths to different workbooks. I'm trying to write VBA code that will reference the file path on my spreadsheet and return the number cells in a particular column from that workbook using the =CountA() formula. Any help you could give would be appreciated as I am still a novice with VBA.

 

[bertie]

Hi and welcome to the forum.

I have assumed your file paths are laid out something like:

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 167px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>c:\Temp\Book1.xlsx</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>c:\Temp\Book2.xlsx</TD></TR></TBODY></TABLE>


And that your CountA is performed on the first worksheet of each workbook, Worksheets(1).

Try this in the ThisWorkbook module, I have highlighted where you may need to edit:

[COLOR=darkblue]Sub[/COLOR] CountPopulatedCells()
   [COLOR=darkblue]Dim[/COLOR] wb [COLOR=darkblue]As[/COLOR] Workbook
   [COLOR=darkblue]Dim[/COLOR] rng [COLOR=darkblue]As[/COLOR] Range
   [COLOR=darkblue]Dim[/COLOR] numCells [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
 
   [COLOR=green]'set error trap for file doesn't exist[/COLOR]
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
 
   [COLOR=green]'loop through the file paths[/COLOR]
   [COLOR=darkblue]Set[/COLOR] rng = Sheets("[COLOR=red]Sheet1[/COLOR]").Range("[COLOR=red]A1[/COLOR]")
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rng = ""
 
      [COLOR=green]'open the workbook[/COLOR]
      [COLOR=green]'count the populated cells in sheet1[/COLOR]
      [COLOR=darkblue]Set[/COLOR] wb = Workbooks.Open(rng.Value)
      [COLOR=darkblue]With[/COLOR] wb.[COLOR=red]Worksheets(1)[/COLOR]   [COLOR=green]'assumed first worksheet has to be processed[/COLOR]
         numCells = Application.WorksheetFunction.CountA(.Range("[COLOR=red]A:A[/COLOR]"))
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
      wb.Close SaveChanges:=[COLOR=darkblue]False[/COLOR]
 
      [COLOR=green]'output results to column B[/COLOR]
      rng.Offset(, 1).Value = numCells
 
      [COLOR=green]'get next file[/COLOR]
      [COLOR=darkblue]Set[/COLOR] rng = rng.Offset(1, 0)
      [COLOR=darkblue]Set[/COLOR] wb = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Loop[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[jdyer]

Thank you so much, that is exactly what I was looking for.