Copy Updated Data from Closed workbook to Open workbook

[alexnkc]

Hi,


I will like to copy data from closed workbook (bbb.xls) to Open workbook (aaa.xls) without opening bbb.xls.
Code below work find for that.

Sub GetDataFrombbb()


With Range("F4")
    .Formula = "='" & ThisWorkbook.Path & "\[bbb.xls]Sheet1'!$N$11"
    .Value = Range("f4").Value
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .Borders.LineStyle = xlContinuous
    .Borders.Weight = xlThin
End With
    
End Sub

How to upgrade the code above to enable it to copy updated data from closed workbook (such as using formula that give row count from N and use that to copy the formula down)
Please help as I am no able to write out the VBA langauge.


Thanks in advance

[mancubus]

i'm not sure i understand your requirement correctly.

you want to get values from column N of Sheet1 of bbb.xls, starting at cell N11 to last nonblank cell.

if this is the case, with the method you wish to use (why not programmatically open, copy values from column N and close bbb.xls, for ex) one way to achieve this is:

Sub get_data_form_closed_wb()


    Dim StartRow As Long, LastRow As Long
    
    StartRow = 11
    
    With Cells(Rows.Count, Columns.Count)
        .FormulaArray = "=MAX(('" & ThisWorkbook.Path & "\[bbb.xls]Sheet1'!N:N<>"""")*(ROW('" & ThisWorkbook.Path & "\[bbb.xls]Sheet1'!N:N)))"
        LastRow = .Value
        .Clear
    End With
    
    With Range("F4").Resize(LastRow - StartRow + 1)
        .Clear
        .Formula = "='" & ThisWorkbook.Path & "\[bbb.xls]Sheet1'!N11"
        .Value = .Value
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Borders.LineStyle = xlContinuous
        .Borders.Weight = xlThin
    End With


End Sub

[alexnkc]

Sorry for not making the statement clearly

What I like to do is:

Book 1 (aaa.xls) – Will like to obtain last data from Book 2 (N1 and Ctrl + down) without needed to open Book 2 (not using wb.open …… wb.close)

Book 2 (bbb.xls) – Act as template and N column will always get updated data.

I try to get the work done differently without using wb.open and wb.close

[alexnkc]

Hi, I think code below maybe able to get the work done. Now I think the code is lack of how to programe the lLastRow to recognize the last row that occupied in bbb.xls, column N.

Sub GetDataFromClosedWorkbook ()

Dim lLastRow As Long




With Range("F4")
    .Formula = _
        "='" & ThisWorkbook.Path & "\[bbb.xls]Sheet1'!N" & lLastRow
    .Value = Range("F4").Value


End With
    
End Sub

[mancubus]

Sorry for not making the statement clearly

What I like to do is:

Book 1 (aaa.xls) – Will like to obtain last data from Book 2 (N1 and Ctrl + down) without needed to open Book 2 (not using wb.open …… wb.close)

Book 2 (bbb.xls) – Act as template and N column will always get updated data.

I try to get the work done differently without using wb.open and wb.close

Did you try the code i posted.?

It does not open and close the bbb.

Just gets the data from column n via formulas.

[alexnkc]

Yup.. I try it and it work well. From your code, it start to obtain data from cell N11 to last nonblank cell.
It will be best if it only copy the last nonblack cell in column N

[mancubus]

Sub get_data_form_closed_wb()


    Dim LastRow As Long
    
    
    With Cells(Rows.Count, Columns.Count)
        .FormulaArray = "=MAX(('" & ThisWorkbook.Path & "\[bbb.xls]Sheet1'!N:N<>"""")*(ROW('" & ThisWorkbook.Path & "\[bbb.xls]Sheet1'!N:N)))"
        LastRow = .Value
        .Clear
    End With
    
    With Range("F4")
        .Clear
        .Formula = "='" & ThisWorkbook.Path & "\[bbb.xls]Sheet1'!N" & LastRow
        .Value = .Value
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Borders.LineStyle = xlContinuous
        .Borders.Weight = xlThin
    End With


End Sub

[alexnkc]

Thanks mancubus.
It is working perfectly.

[mancubus]

you are welcome.

[alexnkc]

hi mancubus,

If I move the aaa.xls file into folder name aaa2 while bbb.xls file into a folder bbb2, then how to modified the VBA language below to perform the work.

Sub get_data_form_closed_wb()      
     
    Dim LastRow As Long 
     
     
    With Cells(Rows.Count, Columns.Count) 
        .FormulaArray = "=MAX(('" & ThisWorkbook.Path & "\[bbb.xls]Sheet1'!N:N<>"""")*(ROW('" & ThisWorkbook.Path & "\[bbb.xls]Sheet1'!N:N)))" 
        LastRow = .Value 
        .Clear 
    End With 
     
    With Range("F4") 
        .Clear 
        .Formula = "='" & ThisWorkbook.Path & "\[bbb.xls]Sheet1'!N" & LastRow 
        .Value = .Value 
        .HorizontalAlignment = xlCenter 
        .VerticalAlignment = xlCenter 
        .Borders.LineStyle = xlContinuous 
        .Borders.Weight = xlThin 
    End With 
     
     
End Sub

[alexnkc]

I think I solve the problem:

Sub get_data_form_closed_wb()     
     
    Dim LastRow As Long
     
    Dim FName As String
    FLocation = "C:\Users\user\Desktop\bbb2"
     
    With Cells(Rows.Count, Columns.Count)
        .FormulaArray = _
            "=MAX(('" & FLocaiton & "\[bbb.xls]Sheet1'!N:N<>"""")*(ROW('" & FLocation & "\[bbb.xls]Sheet1'!N:N)))"
        LastRow = .Value
        .Clear
    End With
     
    With Range("F4")
        .Clear
        .Formula = "='" & FLocation & "\[bbb.xls]Sheet1'!N" & LastRow
        .Value = .Value
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Borders.LineStyle = xlContinuous
        .Borders.Weight = xlThin
    End With
     
     
End Sub

[mancubus]

That's it. Just correct the typo in MAX function line.

[alexnkc]

Thanks manacubus.. I learn something today... :-)