Vba code help to pull count of rows and columns from the list of files.

[Jagdev Singh]

Hi Experts

I am trying to pull number of rows and columns for a folder. I am succeeded with pulling the name, but not able to pull the count of rows and numbers from the sheets.

Here is my current code.

Sub Example1()
Dim objFSO As Object
Dim objFolder As Object
Dim i As Integer
Dim rn As Range
Dim k As Long
Dim k1 As Long

'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.GetFolder(ActiveWorkbook.Sheets(2).Range("E8").Value)
i = 1
'loops through each file in the directory and prints their names and path
For Each Sheet In objFolder.Files
    'print file name
    Cells(i + 1, 1) = Sheet.Name
'    Set rn = Worksheets("Sheet1").UsedRange.Rows.Count
    k = rn.Rows.Count + rn.Row - 1
    'print file path
    Cells(i + 1, 2) = k
    k1 = rn.Columns.Count + rn.Column - 2
    Cells(i + 1, 3) = k1
    i = i + 1
Next Sheet


End Sub

Please let me know how to amend the code to pull the required data from the list of files.

Regards,

JD

 

[Luke M]

Your current code doesn't open the files, it's just using the File Browser to look through files and get the names. If you want to get the used range row/column counts, you'll need to open up the file so you can access the sheet object(s).

PS. In your code, this line

For Each Sheet In objFolder.Files

is a bit confusing...the variable is "Sheet", but it's actually going to be a File, as it's looping through the Files collection.

 

[Jagdev Singh]

Hi luke

Actually I was trying few options actually instead of sheet it should be object like Obj so the code will change to

For each obj in objfolder.files

And in the loop the sheet will be replaced by obj. I assume my approach was incorrect. Can we do something here.

Regards
JD

 

[Luke M]

Sorry, you can name the variable whatever you want, the code still runs (or doesn't run) regardless. I was just referring to human readability. The current problem is as I said, that you'll need to actually open each file. This should get you closer

Sub Example2()
Dim objFSO As Object
Dim objFolder As Object
Dim fPath As String
Dim wb As Workbook
Dim ws As Worksheet
Dim i As Integer
Dim xFile As Variant

'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
fPath = ActiveWorkbook.Sheets(2).Range("E8").Value

i = 2

Application.ScreenUpdating = False
Set objFolder = objFSO.GetFolder(fPath)
For Each xFile In objFolder.Files
    Set wb = Workbooks.Open(fPath & "\" & xFile.Name)
    Set ws = wb.Worksheets(1)
   
    'Record information
    With ThisWorkbook.Worksheets(1)
        .Cells(i, 1) = wb.Name
        .Cells(i, 2) = ws.UsedRange.Rows.Count
        .Cells(i, 3) = ws.UsedRange.Columns.Count
    End With
    i = i + 1
    'Close the workbook w/o saving
    wb.Close False
Next
Application.ScreenUpdating = True
End Sub

 

[Jagdev Singh]

Hi Luke

Thanks for the code! It worked like a charm!

Regards,
JD