How do I reference to another open or closed workbook and pull values back in VBA - Excel 2007

Question

Basically I need to gather a fair few figures from another workbook  Which is found and can be opened by a UserForm  therefore the location and names are variable   I need to use VBA for this as I also need to populate a chart with this data  I would prefer to not have to open the other workbook in order to do it  but if it is far easier then its OK   The UserForm is done and works fine  and I have the code I need to populate the chart  however I cant get VBA to pull back the data from the other workbook and assign it to the variables that I need it to   Any ideas on how I can get VBA to do this  It would be greatly appreciated   Thanks   Rob

User · Accepted Answer

You will have to open the file in one way or another if you want to access the data within it. Obviously, one way is to open it in your Excel application instance, e.g.:-

(untested code)

Dim wbk As Workbook
Set wbk = Workbooks.Open("C:\myworkbook.xls")

' now you can manipulate the data in the workbook anyway you want, e.g. '

Dim x As Variant
x = wbk.Worksheets("Sheet1").Range("A6").Value

Call wbk.Worksheets("Sheet2").Range("A1:G100").Copy
Call ThisWorbook.Worksheets("Target").Range("A1").PasteSpecial(xlPasteValues)
Application.CutCopyMode = False

' etc '

Call wbk.Close(False)

Another way to do it would be to use the Excel ADODB provider to open a connection to the file and then use SQL to select data from the sheet you want, but since you are anyway working from within Excel I don't believe there is any reason to do this rather than just open the workbook. Note that there are optional parameters for the Workbooks.Open() method to open the workbook as read-only, etc.

[vba] How do I reference to another (open or closed) workbook, and pull values back, in VBA? - Excel 2007

Examples related to vba

Examples related to excel