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

Question

Hi all,

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.

Answer 1

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.