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

[MPW]

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

 

[Yongle]

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]

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]

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

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]

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:

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]

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

 

[Yongle]

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]

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]

Thanks Yongle, the Lookup gave me what I was looking for. This is the code that I have been using:

    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]

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)

    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)