Can I the find last used row from a closed workbook?

MPW

Well-known Member
Joined
Oct 7, 2009
Messages
537
Office Version
  1. 365
Platform
  1. Windows
I have over 1,100 old workbooks with macros. They are all built from a template, but vary in how many rows they contain.

In essence, I would like to duplicate the worksheet data (without the VBA code gumming up the works). Turning them into simple workbooks with no vba.
I tried doing this by looping/opening each workbook, but it is extremely slow and some of the vba has been corrupted so they only open manually.

I think I can use "ExecuteExcel4Macro" to accomplish this, even though I have never used it before, but I need to reset the range for each workbook.

So, without opening each workbook
1. I would like to find the last used row
2. Create and duplicate the values on the worksheet.
3. Any tips on using ExecuteExcel4Macro would also be welcomed. I know that this was used when the world was young, but unless there is a better method I will go with it.

Thanks
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Does each workbook only contain one sheet?
Is the name of that sheet common across all the workbooks? What is that name?
Are all the workbook in .xlsm format?
 

MPW

Well-known Member
Joined
Oct 7, 2009
Messages
537
Office Version
  1. 365
Platform
  1. Windows
Sorry Yongle, We had a global weather event here. Had to leave early.

Most of the workbooks have at least 2 worksheets. Some have 3.
The name of the sheet should globally be the same ie Sheets("Script").
Most are xls, however about 100 of them are xlsm.

I think this answers you questions. Thanks for the look.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Here is a simple way to get the last row in sheet Script in closed file XXX.xlsm if column A contains no empty cells

CountA function works on a closed file

So this method can be used to get a count of the cells containing values in column A
VBA Code:
ActiveCell.Formula = "=COUNTA('C:\Folder\SubFolder\[XXX.xlsm]Script'!$A:$A)"

That would only work if there are no empty cells in column A inside the data range
Is there a value in EVERY cell in column A in your data ?
 

MPW

Well-known Member
Joined
Oct 7, 2009
Messages
537
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Actually, it would be column B and yes there would be empty cells. The info always should start in row 57, but occasionally there is data above 57. Normally I use something like this:
VBA Code:
Lastrow = Sheets("Script").Cells(Rows.Count, 2).End(xlUp).Row
I am guessing that that will not work in this situation. I could always pad the counta by 57 to compensate so that shouldn't be an issue.

Would it be easier to convert all of these workbooks by just pasting the only the values of the "Script" sheet into a new blank workbook? That would remove any troublesome formulas and VBA code that are slowing down my process. What do you think?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
In that case try this formula on some of your closed files to see if the correct last row is returned consistently

=LOOKUP(2,1/('C:\Folder\SubFolder\[XXX.xlsm]Script'!$B:$B<>""),ROW('C:\Folder\SubFolder\[XXX.xlsm]Script'!$B:$B))

I think it would require VBA dumping formula to cell to make it evaluate - not a problem
 
  • Like
Reactions: MPW

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Would it be easier to convert all of these workbooks by just pasting the only the values of the "Script" sheet into a new blank workbook? That would remove any troublesome formulas and VBA code that are slowing down my process. What do you think?
I thought you were trying to avoid opening the workbooks because "it is extremely slow and some of the vba has been corrupted so they only open manually"
 

MPW

Well-known Member
Joined
Oct 7, 2009
Messages
537
Office Version
  1. 365
Platform
  1. Windows
Yes I do want to avoid opening the workbooks, if possible. I was wondering if this would be possible to do without opening the workbooks.
I have data in the 1st 14 columns. If these columns could be captured and transferred it would stream line any future use of this info.

I will try to adapt your LOOKUP formula.
 

MPW

Well-known Member
Joined
Oct 7, 2009
Messages
537
Office Version
  1. 365
Platform
  1. Windows
Thanks Yongle, the Lookup gave me what I was looking for. This is the code that I have been using:
VBA Code:
    Dim oFSO As Object
    Dim oFolder As Object
    Dim oFile As Object
    Dim wb As Workbook
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder("C:\quikbreath")
    Cells.Clear
    For Each oFile In oFolder.Files
        i = i + 1
        Cells(i, 1).Select
        DoEvents
        Cells(i, 1) = oFile.Name
        Cells(i, 2) = "=LOOKUP(2,1/('" & oFolder & "\[" & oFile.Name & "]Script'!$B:$B<>""""),ROW('" & oFolder & "\[" & oFile.Name & "]Script'!$B:$B))"
    Next oFile

My next step is to create a new workbooks based on the original names and populate them with ("A57:N~").

Do you recommend trying to capture the whole array or moving it cell by cell? Cell by cell is pretty straight forward, but slow, not sure how to capture all of it at once.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
This is how I would do it, because it is simple and fairly efficient
This formula is applied to relevant cells to get the underlying values in ONE hit
='C:\Folder\SubFolder\[XXX.xlsm]Script'!A57

LastRow is the row determined by the other formula
ws is the sheet to return value from Script in XXX.xlsm
I assume that the values are to be placed in columns A:N starting at cell A1
Rng is the range being extracted from the source file which is Range("A57:N" & LastRow)
Rich (BB code):
    Set column widths in sheet ws - I would do this before placing values in cells
    Set rng = ws.Range("A1:N1").Resize(LastRow - 56)
    rng.Formula = "='C:\Folder\SubFolder\[XXX.xlsm]Script'!A57"
    rng.Value = rng.Value
    Format values in rng (avoid formatting whole columns)
 
  • Like
Reactions: MPW
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,394
Messages
5,636,050
Members
416,894
Latest member
Hari1992

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top