PDA

View Full Version : [SOLVED] Copy Updated Data from Closed workbook to Open workbook



alexnkc
11-23-2013, 02:30 AM
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
11-23-2013, 03:05 PM
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
11-24-2013, 12:21 AM
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
11-24-2013, 12:57 AM
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 & "\Sheet1'!N" & [B]lLastRow
.Value = Range("F4").Value


End With

End Sub

mancubus
11-24-2013, 01:25 AM
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
11-24-2013, 01:38 AM
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
11-24-2013, 02:06 AM
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
11-24-2013, 02:33 AM
Thanks mancubus.
It is working perfectly.

mancubus
11-24-2013, 03:50 AM
you are welcome.

alexnkc
11-24-2013, 06:00 AM
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
11-24-2013, 08:33 AM
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
11-24-2013, 08:40 AM
That's it. Just correct the typo in MAX function line.

alexnkc
11-24-2013, 04:52 PM
Thanks manacubus.. I learn something today... :-)