How do I connect to a named range in Excel from Visual Basic?
First we need to add a reference to the Microsoft Excel Office library. Do this by: Then by selecting the Microsoft Excel Object library. Note 9.0 is for Excel 2000, and 10.0 is for Excel XP. Once we have added the Excel object library reference to our project, then we can refer to it in our application. Let’s say we have a spreadsheet named “Demo.xls”, located at “C:\Demo.xls”. It contains the named range “Data”. Then we add the following code in the VB project: ‘ Declarations Public appExcel As Excel.Application Public wbDemo As Workbook ‘ Load Excel Application Set appExcel = CreateObject(“Excel.Application”) ‘ Load Workbook Set wbDemo = appExcel.Workbooks.Open(C:\Demo.xls”) ‘ Map the named range into XYChart XYChart.ChartData = wbDemo.Names(“Data”).RefersToRange.Value XYChart.Refresh ‘ Close the Workbook and end Excel wbDemo.Close appExcel.Quit Set appExcel = Nothing ‘ Free unused memory Note that you must ensure that the dimensions of the named range and XYChart are consistent. Sp
