Previous Topic Next Topic

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

Closed
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 - Apr 16, 2015 at 05:54 PM
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 - Apr 17, 2015 at 03:33 PM
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 response

Answer 1 / 1
MaxStart Posts 339 Registration date Tuesday March 3, 2015 Status Moderator Last seen July 3, 2015 69
Apr 17, 2015 at 02:26 AM
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
0
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 150
Apr 17, 2015 at 01:45 PM
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
0
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 150
Apr 17, 2015 at 03:33 PM
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.
0

Similar discussions

help me to add particular cells
mathewmunna -
mathew munna -

2 responses
Ig stories from close friends to public
Darren -
zara -

31 responses
If a cell contains any text then add a value
cbrizz -
Pharmanumbers -

20 responses
Excel - IF function with multiple words
Nick -
user -

14 responses
If cell = date return yes or no.
JD -
ndwebb -

5 responses