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

[K Raghavender rao]

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

 

[Luke M]

Check out this thread:
http://stackoverflow.com/questions/...ows-for-a-certain-criteria-in-closed-workbook
and this one:
http://www.ozgrid.com/forum/showthread.php?t=56413

for the code to access said information from closed files.

 

[SirJB7]

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:

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]

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.

 

[anupamtiwari05]

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]

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]

@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]

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]

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

 

[vighnesh3008]

Can i get the same for any type of excel file extension ASAP. Need to calculate the number of rows in almost more than 850 excel files.

 

[vletm]

vighnesh3008
You should reread Forum Rules ... ASAP

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

... and You should noticed that You've already lost time,
because You've not followed those basic rules.