• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how-to-count-the-number-of-rows-without-opening-an-excel-file

K Raghavender rao

New Member
Hi Friends,

I have 394 excel sheet or CSV files in one folder in data is in same format in all excels or CSV..now i want to know in each excel sheet or CSV file how rows contain and i want that inform in macro sheet .

Thanks,
KRRA
 

SirJB7

Excel Rōnin
Hi, K Raghavender rao!
Actually is very difficult to achieve that without opening the file, both solutions are tricky since some how the do open it, even not directly with the Excel application but from a running instance, so here it goes another one:
Code:
Option Explicit

Sub IDidNotLikeAnyOfTheOtherTwoSolutionsButNeitherLikedTooMuch()
    ' constants
    Const ksFileName = "how-to-count-the-number-of-rows-without-opening-an-excel-file (for K Raghavender rao at chandoo.org).csv"
    ' declarations
    Dim I As Long, J As Integer, A As String
    ' start
    J = FreeFile()
    Open ThisWorkbook.Path & Application.PathSeparator & ksFileName For Input As #J
    ' process
    I = 0
    Do Until EOF(J)
        Line Input #J, A
        I = I + 1
    Loop
    ' end
    Close #J
    Debug.Print I
End Sub
Regards!
 

K Raghavender rao

New Member
Well i am what i am looking for is... code should ask the path address in combo box then we will update the path with "\". once we enter path it should read the each excel sheet and open new excel worksheet and it should save as "Count and Row" in the Column A should be header as "Sheet Name"and Column B should be header as "No of Rows"...... infact i found one code but some small modifications need to be need to below things

1. Combo Box
2. macro shouldn't open the file or it shouldn't open the file and close it and then movie to next one

i am attaching the file for your reference.
 

Attachments

Hi All,

I have come up with the below formula that can give us the coun of number of used rows in a worksheet without opening the file:

=MAX(ROW('Z:\Anupam\Desktop\[Excel_Test.xlsx]Sheet25'!$A$1:$Z$65530)*('Z:\Anupam\Desktop\[Excel_Test.xlsx]Sheet25'!$A$1:$Z$65530<>""))

But the issue is we need to provide the exact path alongwith the workbook name, sheet name and the range in the formula.

However I have a thought in my mind but I don't know it how it can be feasible.

If we write a macro which loop through all the files in the Folder and insert the filename alongwith the path in a sheet in Thisworkbook.

Now if the sheet name is same in all the files then we can provide reference of the cell which has the file name and path information into the formula and replace the sheet name as well with the new name that is fixed for all files.

I have not tested this yet however just gave a thought to it but I will try it out by today or tomorrow and will post the code if find success in it.

The above idea may sound weird but we can try atleast.

Thanks & Regards,
Anupam
 

Jason F

New Member
Hi SirJB7,

Your code is pretty much exactly what I need - I have a lengthy excel file and I want to know how many rows it has without actually opening it.
The problem is, when I try to use your macro with an excel file instead of .csv file, it always just returns 1. Do you know why this might be the case? I've been troubleshooting using an .xls file consisting of 6 rows and 2 columns filled with text, but nothing I do seems to work.

Thanks,
Jason
 

SirJB7

Excel Rōnin
@Jason F
Hi!

Would you please start a new topic instead of writing on another user's one? It might be seen as hijacking. More indeed when it's such and old topic. If needed you could add a reference in your new one.

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.

Regards!

PS: Please don't answer here at this thread.
 

asdff

New Member
Well i am what i am looking for is... code should ask the path address in combo box then we will update the path with "\". once we enter path it should read the each excel sheet and open new excel worksheet and it should save as "Count and Row" in the Column A should be header as "Sheet Name"and Column B should be header as "No of Rows"...... infact i found one code but some small modifications need to be need to below things

1. Combo Box
2. macro shouldn't open the file or it shouldn't open the file and close it and then movie to next one


i am attaching the file for your reference.
Can you make it to count from column number 419
 

Nikhil95

New Member
Well i am what i am looking for is... code should ask the path address in combo box then we will update the path with "\". once we enter path it should read the each excel sheet and open new excel worksheet and it should save as "Count and Row" in the Column A should be header as "Sheet Name"and Column B should be header as "No of Rows"...... infact i found one code but some small modifications need to be need to below things

1. Combo Box
2. macro shouldn't open the file or it shouldn't open the file and close it and then movie to next one


i am attaching the file for your reference.
Can i get the same for any type of excel file extension
 
Top