Macro to extract data from another workbook

[Barryj]

I need to extract data from a workbook called Social Club, the sheets name in this workbook is RESULTS the range is B7:E36 and also R7:U36

I need this copied to a workbook called Social Results and into a sheet called Final Results.

The data will go in the same ranges as the sheet it extracts it from, but the sheet it extracts it from needs to be activated to run a macro contained in it.

Thanks for any replies.

[XLGibbs]

Hi Barry,

see if the KB entry offers any assistance, post back if any issues...as provided lucas

http://vbaexpress.com/kb/getarticle.php?kb_id=832

[Barryj]

I have tried to utilise the code as you suggested but with no luck, any mor suggestions will be greatfully accepted.

Thanks again.

[lucas]

Try this:


Option Explicit
Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
Application.ScreenUpdating = False ' turn off the screen updating
Set wb = Workbooks.Open("f:\Temp\data.xls", True, True)
' the sheet in this workbook to copy to
With ThisWorkbook.Worksheets("target")
' read data from the source workbook
'the range to copy to in this workbook-name of sheet to copy FROM-range in closed workbook to copy
.Range("B10", "C20").Formula = wb.Worksheets("Sheet1").Range("A10", "B20").Formula

End With
wb.Close False ' close the source workbook without saving any changes
Set wb = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
End Sub

[Barryj]

With this macro what do I rename to get it to work I have tried to rename some lines but get a 400 error.

Am I on the right track here with what I need to do.

Option Explicit
Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
Application.ScreenUpdating = False ' turn off the screen updating
Set wb = Workbooks.Open("c:\Temp\data.xls", True, True)
' the sheet in this workbook to copy to
With ThisWorkbook.Worksheets("Final Results")
' read data from the source workbook
'the range to copy to in this workbook-name of sheet to copy FROM-range in closed workbook to copy
.Range("B10", "C20").Formula = wb.Worksheets("Social Results").Range("A10", "B20").Formula

End With
wb.Close False ' close the source workbook without saving any changes
Set wb = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
End Sub

[lucas]

be sure the path to the Social Club.xls is correct in the code below....
Option Explicit
Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
Application.ScreenUpdating = False ' turn off the screen updating
'set the path to the Social Club.xls
Set wb = Workbooks.Open("f:\Temp\Social Club.xls", True, True)
' the sheet in this workbook to copy to
With ThisWorkbook.Worksheets("Final Results")
' read data from the source workbook
'the range to copy to in this workbook-name of sheet to copy FROM-range in closed workbook to copy
.Range("B7", "E36").Formula = wb.Worksheets("RESULTS").Range("B7", "E36").Formula
.Range("R7", "U36").Formula = wb.Worksheets("RESULTS").Range("R7", "U36").Formula

End With
wb.Close False ' close the source workbook without saving any changes
Set wb = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
End Sub

attache zip with your named files with your named sheets, you still have to make sure the path is correct...hope this helps

[Barryj]

Lucas, I am getting a runtime 1004 error on lineSet wb = Workbooks.Open("f:\Temp\Social Club.xls", True, True)

Thought maybe the f was the drive it was placed on the computer and changed that to c, d, e but did not make any difference, any thoughts.

[lucas]

Hey Barry,
Thats why I mentioned making sure the path to the social club.xls was correct. What is happening is the macro is looking for the workbook but cannot find it.

If you have a C: drive and you put the social Club.xls in a directory named Temp then the path would be


Set wb = Workbooks.Open("C:\Temp\Social Club.xls", True, True)

If it is not located in C:\Temp\

you will have to change this line in the code to reflect where the file is located on your hard drive....hope you understand.

If you put it in MyDocuments the path(If your not wierd like me and have a normal C hard drive) would be:


Set wb = Workbooks.Open("C:\Documents and Settings\Main\My Documents\Social Club.xls", True, True)


best way to get the path to where the file is located is to locate it in explorer and right click on the file, click on properties and look for the path. I have explorer set up to show the path at the top of the page...

I hope this helps because I'm positive that the path to the file is your problem....Let me know if you need further assistance.

[Barryj]

Lucas, solved the path problem, thanks for the info, but if I wish to use this macro to get information from another workbook that has multiple sheets in it do I have to modifiy it to look through the workbook to find the sheet required, or if I change the file name and destination name will it also work.

[lucas]

Barry, I'm not sure if I understand your question. You can copy this and modify it to get more info from other workbooks and different sheets. Might need to have a macro for each inport but they could all be called by another routine if you wanted to run them all at the same time. Could you give me a little more detail like your first post and maybe we can figure something out.

[mdmackillop]

Hi Barry,
If you can create a list of workbook, worksheet names and destinations, Steve's code can be adapted to use this source information to pull in data from wherever you specify.
If you're looking to get data from a large number of sources, it might be quicker using code which does not require the books to be opened. (see http://www.vbaexpress.com/kb/getarticle.php?kb_id=454)
Regards
MD

[lucas]

Thats a good script and another advantage is it use thisworkbookpath which means that just so all of the files are in the same folder you don't have to worry about the path...