Get value of last cell in column B from a closed workbook

[Closed]
Report
Posts
1004
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 27, 2020
-
Posts
1004
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 27, 2020
-
Oh Dear - back again...

Im trying to improve a macro I have been working on. It works as far as it goes, but I realsed that data needed to be inputted into my workbook is already on another (closed) workbook. I thought that it would be great if I could just lift the data from the closed workbook and use it in the new active workbook.

I have had some success in this, but my method involves copying lots of cells in the column into my active workbook. Wouldnt it be great if I could get the value of the last cell in column B of the closed workbook (which just happens to have the total of the number of cells I want to copy - its part of that workbooks job to find this data) and use this as the range I need?

In short the question is ....

How can I get value of last cell in column B from a closed workbook without opening it?

What I have is ...

Sub lastcell()
' Get list from closed Spreadsheet and insert them into Column B

Dim mydata, path, folder, file, sheet, rng As String
Dim LastRow As Long

'Closed Workbook location, Workbook name & range to copy
path = "F:\Spreadsheet\"
folder = "4April 2015\"
file = "Packing List.xlsx"
sheet = "Sheet1"
rng = "$B$4:$B$90"

mydata = "='" & path & folder & "[" & file & "]" & sheet & "'!" & rng

'link to worksheet
With ThisWorkbook.Worksheets("Macros").Range("B4:B90")
.Formula = mydata
'convert formula to text
.Value = .Value

End With

End Sub


As always I value the help I get from you guys and would return the favour ... if I could.

Thanks in advance for any offerings

--

1 reply

Posts
341
Registration date
Tuesday March 3, 2015
Status
Moderator
Last seen
July 3, 2015
68
VBA does not include a method to retrieve a value from a closed file. You can, however, take advantage of Excel's ability to work with linked files.
just like what you did, and I didn't !!!

There's a small trick which I prefer,
it's to open the file get the value and close it without having to see it being opened at all.
it goes like this:
Sub go()
Dim SRS As Workbook, FilePath As String, I As Integer, Cell As Range
FilePath = ThisWorkbook.Path & "\Book1.xlsx"

Application.ScreenUpdating = False

Workbooks.Open (FilePath)
Set SRS = Workbooks("Book1.xlsx")
i = 1
    With SRS
        Do Until SRS.Sheets(1).Cells(i, 2).Value = ""
            I = I + 1
        Loop
        I = I - 1
        Set Cell = SRS.Sheets(1).Cells(i, 2)
        MsgBox Cell.Value
        .Close
    End With

Application.ScreenUpdating = True

End Sub
Posts
1004
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 27, 2020
138
Thanks Max.

Im not sure I like opening up the closed workbook, but until I can find better, then I think this is what I will have to do.

I have found a different solution - much shorter, and not quite what I need yet, but at least gives me access to the data I need.

Here it is for those who want to try the same way ...

Sub lastcell()
Application.ScreenUpdating = False
Workbooks.Open ("PATH\FILENAME.xlsx") ' I hate doing this

Dim lastrow As Long
Dim data As String

lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
data = Cells(lastrow, 2)
Workbooks("FILENAME.xlsx").Close

Application.ScreenUpdating = True

End Sub
Posts
1004
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 27, 2020
138
AH - so close ....

Ok , so I got the value that I need from the above code, but ...

"data" is a number which is a count of the cells in column B of the closed workbook (calculated in that workbook).

What I need to do is incorporate this number into the range that I want copied from the closed workbook into the open workbook. Thus the code I have now is:

Sub lastcell()
Application.ScreenUpdating = False
Workbooks.Open ("PATH\FILENAME.xlsx") ' I hate doing this

Dim lastrow As Long
Dim data As String

lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
data = Cells(lastrow, 2)
Workbooks("FILENAME.xlsx").Close

Application.ScreenUpdating = True

' Thats got the number I need to set the range for the next bit in a variable called "lastrow" and the cell with that number in it in a variable called "data"



Dim closedPath, closedFolder, closedFile, closedSheet, mydata As String

closedPath = "F:\Spreadsheet\"
closedFolder = "4April 2015\"
closedFile = "Packing List.xlsx"
closedSheet = "Sheet1"
closedRng = Range(Cells(4, 2), Cells(lastrow, 2))

mydata = "='" & closedPath & closedFolder & "[" & closedFile & "]" & closedSheet & "'!" & closedRng

'link to worksheet
With ThisWorkbook.Worksheets("Macros").Range(Cells(4, 2), Cells(lastrow, 2))
.Formula = mydata
'convert formula to text
.Value = .Value

End With

End Sub


It goes horridly wrong at the mydata line with a run time error 13 - type mismatch.
Any ideas please?

Also how do I make the line above as absolute values - like ($B$4:$B$lastrow)

It seems so simple a task, but I just cant fathom it!
Thanks in advance for your suggestions.