VBA to determine last used row in a closed workbook

Hi Experts,

I'm not sure if it can be done or not, but I'm hoping to find a way in VBA to determine the last used row in a closed workbook (there's actually many workbooks and the last used row will vary from one to the next).

I appreciate any insights!

Jeff
LVL 2
Jeffrey SmithOwnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

answer_dudeCommented:
You mean without opening the workbook to look?
0
Jeffrey SmithOwnerAuthor Commented:
Thanks for posting answer_dude.

Yes, preferably without opening the workbook to look.  I know from John Walkenbach's page at:

http://spreadsheetpage.com/index.php/tip/a_vba_function_to_get_a_value_from_a_closed_file/

... that it's possible to get a value from a closed file, but the only way I know to do that is if I can specify the range where the data is located.  Since the last row (with Totals)  is variable across the files I have to deal with, I'm looking for a way to determine what that last row is.  If that's not possible, then as a fall back position, it would be acceptable to have a routine to go through a list of file names (in a defined range name that may also be variable), open the files, retrieve that data, close the file, and go to the next one until the end of the list is reached.

Hope that's clear.

Jeff
0
answer_dudeCommented:
So you're using the Excel 4 Macro language.... ;-)   Haven't seen that in a while.

http://support.microsoft.com/kb/q128185/

You could try to call expand on it by trying to use some other Excel 4 Macro commands (such as RUN to execute a macro SELECT.LAST.CELL (although the last cell is not always the last row with data).

The macro attached will find the last row in the current worksheet -- but that won't help you if the workbook is closed...  I'll keep thinking about this one...
Function FindLastRow() As Long

On Error Resume Next

    FindLastRow = 0
    If WorksheetFunction.CountA(Cells) > 0 Then
        'Search for any entry, by searching backwards by Rows.
        FindLastRow = Cells.Find(What:="*", After:=[A1], _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious).Row
    End If

End Function

Open in new window

0
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

answer_dudeCommented:
Is there some data in the last row that indicates it is the last row?  You mentioned Totals.  There doesn't happen to be a predictable word like "Totals" as part of the last row... maybe in column A?

If so, then you could use the example TestGetValue2 from the link you posted... cycle through starting at cell A1 and going down to A65536 or until you hit the word "Total" -- then you know the last row....
0
Jeffrey SmithOwnerAuthor Commented:
Yeah, there's still some stuff that only Excel4 can do (hopefully, MS won't deprecate it without giving us equivalent capabilities...).

> although the last cell is not always the last row with data

Actually, in this case, I believe the last cell will be the last row with data (it's an import from a mainframe system).  So, your idea of using RUN to execute a SELECT.LAST.CELL macro may work.  I'm trying to figure out how to do that now ... if that doesn't work, I'll come back to your "Totals" idea as the word "Totals" does exist in Column D of the last row.

Jeff
0
Jeffrey SmithOwnerAuthor Commented:
Ok, I am not having much luck with either approach.  I've attached the code I have on the first approach but the 2nd Function (Get_LastRow) has a syntax error I don't know how to fix (and I don't know if it's possible to call one Excel4 macro from another either ...).  And I'm not sure how to adapt John W's TestGetValue2 example (from the link in my 2nd post above) to this purpose.

To be more clear (and hopefully help you help me), what I want to do is this:

From a list of files that will be maintained in a defined range name (e.g. "ClientList"), I want to be able to: a) retrieve the values from columns E:N of the last row; and b) return the row number that those totals are on (because I also have to compute how many records were in the file).  As mentioned, that total row does have the label "Totals" in column D.  

There will be other processing involved for each of the files but I think I can handle that if I can get this part worked out.

Jeff


Public Function Get_eReportData(path, file, sheet, ref)
'   Retrieves a value from a closed workbook
    Dim arg As String
'   Make sure the file exists
    If Right(path, 1) <> "\" Then path = path & "\"
    If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If
'   Create the argument
    arg = "'" & path & "[" & file & "]" & "Sheet1" & "'!" & _
      Range(ref).Range("A1").Address(, , xlR1C1)
'   Execute an XLM macro
    Get_eReportData = ExecuteExcel4Macro(arg)
End Function

Public Function Get_LastRow()
Dim LastRow As Long, arg As Range
arg = SELECT.LAST.CELL()
Get_LastRow = ExecuteExcel4Macro(arg)
End Function

Sub TestGet_eReportData()
Dim arg As String

    p = "C:\eDialog EMPEC\"
    f = "Client3.xlsx"
    s = "Sheet1"
    a = "A1"
    MsgBox GetValue(p, f, s, a)
End Sub

Open in new window

0
answer_dudeCommented:
This should do it.

Look at the comments... you'll need to change where you write the totals...
Sub TestGetValue2()

    Dim wb As Workbook
    Dim rClientList As Range
    Dim eBook As Range
    Dim p, f, s As String
    Dim r As Long
    Dim c As Long
    Dim nTotColumn As Integer
    
    nTotColumn = 4 'This is where we expect to find "Totals"
        
    'I assume all files are in the same path
    p = "c:\XLFiles\Budget"
    'Assume all worksheets are named Sheet1
    s = "Sheet1"

    Set wb = ActiveWorkbook
    Set ws1 = wb.Sheets("Sheet1")

    Set rClientList = ws1.Range("ClientList")

    For Each eClient In rClientList
    
        f = eClient
        Application.ScreenUpdating = False
        'Loop through until we find "Totals"
        For r = 1 To 65536
            a = Cells(r, nTotColumn).Address
            If (GetValue(p, f, s, a) = "Totals") Then
                Cells(r, nTotColumn) = r
                For c = 5 To 14  ' E:N
                    a = Cells(r, c).Address
                    'You need to modify this to determine where
                    'exactly you want to put your total values...
                    'for now this will put it in the "Active"
                    'workbook on the same row as the closed
                    'workbook.
                    Cells(r, c) = GetValue(p, f, s, a)
                Next c
                Exit For
            End If
        Next r
        Application.ScreenUpdating = True
    
    Next eClient
    
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DaveCommented:
Jeff,
It is possible to use ADO to retrieve the last row in a closed file, but it can be flakey
If you are going to use code, then simply open the Workbook in the background and use a quick and painless Find.  The XLM approach is useful when you know where you are pulling the data from. Its not for searching, particularly with Excel 2007's 1M rows
You could even use a VBscript rather than via Excel VBA if preferred
 I will provide an example
Cheers
Dave
 
0
DaveCommented:
This code quickly find the value, and address of the last used row in column C, in the first sheet of  C:\temp\test.xls
Cheers
Dave

Sub GetROw()
    Dim wB As Workbook
    Dim ws As Worksheet
    Dim rng1 As Range
    Dim strMsg As String

    With Application
        .DisplayAlerts = False
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    Set wB = Workbooks.Open("C:\temp\test.xls")
    Set ws = wB.Sheets(1)
    Set rng1 = ws.Cells(Rows.Count, "C").End(xlUp)
    strMsg = wB.Name & vbNewLine & ws.Name & vbNewLine & " has value " & rng1.Value & " in " & rng1.Address(0, 0)
    wB.Close False

    With Application
        .DisplayAlerts = True
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    MsgBox strMsg


End Sub

Open in new window

0
Jeffrey SmithOwnerAuthor Commented:
answer_dude - Thanks for your code example, but I can't get it to work ... it goes into an endless loop (apparently) where I can't CTRL+Break to stop it and the only way out is to kill Excel.  That said, I think the loop within a loop structure you provided (if it can be made to work) is what I need to cycle through all of the 'Clients' in the ClientList Range.  I've attached my current file in case anyone can figure out why the loop won't finish (I believe it's the outer loop that doesn't finish, I don't think it ever gets to the inner loop ...).

Hi Dave & thanks for logging in on this.  Your code does work to retrieve the Row number the totals are on - Thanks!  Now, if I can only 'marry' that with a working version of answer_dude's looping structure so I can interrogate the ClientList range to cycle through all of the Client files listed therein, I think I can work out the rest of what I need to do.

Am trying to struggle through with this but would welcome any further assistance here ...

Jeff
EMPEC-WIP-2010-01-v2.xlsm
0
Jeffrey SmithOwnerAuthor Commented:
To be clear, the "TestGetValue2" code in the file attached in my last post is my current version of answer_dude's suggestion and "GetROw" is Dave's code adapted to my file.  I'm also uploading here, an example "source" file ("Client3.xlsx") that is presently the only file in the named range ClientList.

Jeff
Client3.xlsx
0
Jeffrey SmithOwnerAuthor Commented:
I should have said, too, that I think answer_dude's code for retrieving the values in E:N on the Totals row looks like it will do what I need to do, too, but the macro didn't get far enough to test that part of the code.  At any rate, I'll need to incorporate that into the mix, too, when the loop can be made to work ...

Jeff
0
answer_dudeCommented:
The "endless loop" you experienced could be that it did not find "Totals" -- is that possible?

I did it with only three sample databases and the Totals rows were less than 40 rows down... if you have files with Totals rows very far down -- thousands of rows -- then it will take some time.

Just before the For Each line, add
     Application.DisplayStatusBar = True

Right after the For r = 1 line, Add
     Application.StatusBar = "Processing row " & r

That way you can see if it's actually stalled or just going far...
0
answer_dudeCommented:
Also... if you add a "DoEvents" right before the "Next r" statement -- that might help to ensure you can break out of the code if it does do something untoward...
0
Jeffrey SmithOwnerAuthor Commented:
answer_dude - the sample 'source'  file I posted ("Client3.xlsx") has the word "Totals" in Cell D47. So, it's not that ...  If you have a look at my "EMPEC-WIP-2010-01-v2.xlsm" file attached to my  09:58 AM post, and run the  "TestGetValue2" code (you'll have to change the "p" variable to the path where you copy the "Client3.xlsx" file), you'll see what I mean about the endless loop.

Thanks,

Jeff
0
Jeffrey SmithOwnerAuthor Commented:
I added the code you suggested and while the "DoEvents" does allow me to break out of the code (Thanks!), the macro ran through Row 500 of Column D before I killed it without ever "finding" the "Totals" value in cell D47 of the "Client3.xlsx" file.  I've attached the file with your latest code suggestions.

Not sure where to go next ...

Jeff
EMPEC-WIP-2010-01-v3.xlsm
0
Jeffrey SmithOwnerAuthor Commented:
Ah, I just checked cell cell D47 of the "Client3.xlsx" file and found a trailing space after the word "Totals".  When I deleted that, the macro stopped with the Status bar indicating "Processing Row 47".  Nothing else appeared though, like returning the values of E47:N47 ... but this may be progress ...

Jeff
0
Jeffrey SmithOwnerAuthor Commented:
Stepping thru the code, I see that the line:

Cells(r, c) = GetValue(p, f, s, a)

... is returning the value 15875128 when the "a" variable was = $E$47 and
... is returning the value 15661418 when the "a" variable was = $F$47

The difference in the value returned between the two Columns is 213710. I have no idea what these values represent ...

Jeff
0
Jeffrey SmithOwnerAuthor Commented:
Duh!  Those numbers were the totals in Cells E47 & F47 ... which is what I wanted returned ... but since I was at the top of the active sheet, I didn't see this output being posted to row 47 ... so this may be working ... at least it's closer.  Let me study this a bit more and come back.

Jeff
0
Jeffrey SmithOwnerAuthor Commented:
Ok, I'm making progress here ... and I know this question is morphing into some 'attendant' issues' that I'd be glad to come back and break these out into separate "pointer questions" when we're done so I don't shortchange any Experts ... but I'm just trying to get this done as quickly as possible right now (without getting bogged down in separate questions) at the moment).

Anyway, one of the remaining issues I have is that the Sheet name in the source files in the ClientList range will not all be the same.  Any idea how I can change the "s" variable to an object (it will always be "Sheet1" as there is only one sheet in these files)?

Jeff


0
answer_dudeCommented:
Through the Macro 4 -- not sure... optionally, you could expand the Client List to include not only the name of the workbook but also the name of the sheet (in the cell immediately to the right of the file name) - then reference it like this right after the For Each:

f = eClient
s = eClient(1,2)


0
Jeffrey SmithOwnerAuthor Commented:
Yeah, the Excel4 part of it was stumping me, too.  I don't think the expanded Client List will work either, because I won't know the name of the file in advance ...

Perhaps there's a way to automate the renaming of Sheet1 (whatever the name is, change it to "Sheet1") in the source book before the rest of the code runs?

Jeff
0
answer_dudeCommented:
You can certainly do it using brettdj's example... his point stands that the Macro function is good if you know where you're going.  If you don't .. it's a bit of a challenge because it's so limited.  In fact with brettdj's code it doesn't matter what the first sheet name is because you can use ActiveWorkBook.Sheets(1) as your reference anyway.

Why do you want to do it with the target workbook closed?  Are you worried about performance?
0
Jeffrey SmithOwnerAuthor Commented:
Actually, I think that's just become a non-issue as I am finding out that this can be controlled by humans rather than a system-generated sheet name. Let me study this some more & come back to this. Probably tomorrow. Thanks!

Jeff
0
DaveCommented:
Please let me know if I can assist further, as it stands answer_dude has been the one making progress on this so I dont want to come over the top
Regards
Dave
 
0
Jeffrey SmithOwnerAuthor Commented:
answer_dude - Thanks for all your help!  

Dave - Thanks to you, too, for posting here but answer_dude's earlier solution brought this home for me (I just had to get my ducks in a row before his solution would work).

You guys are the best!

Jeff
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.