topic Opening an Excel Spreadsheet Using VBA in ArcObjects SDK Questions

Opening an Excel Spreadsheet Using VBA

BruceNielsen — Fri, 25 Jan 2013 18:30:19 GMT

I need to open an Excel Spreadsheet in a VBA script; I don't have access to Visual Studio, yet. When I did this before in Arc 9.x & Visual Studio 2005, I would use Process.Start(<spreadsheet path>). Is there a similar command in VBA for ArcGIS 10.0, or do I have to use the VBA Shell function?

Re: Opening an Excel Spreadsheet Using VBA

DuncanHornby — Sat, 26 Jan 2013 12:53:07 GMT

Bruce,

You use automation to open and drive Excel from ArcMap in VBA, below is some code to get you going:

    ' Create some Excel objects     Dim EXCELApplication As Object     Dim aWorkBook As Object     Dim theSheets As Object     Dim aSheet As Object          ' Start Excel Application and create Workbook     Set EXCELApplication = CreateObject("Excel.Application")     If EXCELApplication Is Nothing Then         Exit Sub     End If     With EXCELApplication         .Visible = True         .SheetsInNewWorkbook = 2         .DisplayStatusBar = True         .StatusBar = "Please be patient, transfer in progress, Excel will minimise when completed..."         .Workbooks.Add         Set aWorkBook = .Workbooks(1)     End With     With aWorkBook         .Title = "Sites Downstream of input site"         .Subject = "List of downstream sites"         .Comments = "This Excel file contains a list of all sites downstream of the sites in the input layer."     End With          ' Initialise worksheets     Set theSheets = aWorkBook.Sheets     theSheets.Item(1).Name = "Downstream sites"    ' Blah blah blah

Duncan

Re: Opening an Excel Spreadsheet Using VBA

BruceNielsen — Sun, 27 Jan 2013 01:25:55 GMT

Thanks, Duncan. But what if I want to open an existing spreadsheet?

Re: Opening an Excel Spreadsheet Using VBA

DuncanHornby — Sun, 27 Jan 2013 09:59:43 GMT

Bruce,You can do this easily you just call the appropriate method on the application object. So the first thing you need to do is search the help file for Excel and you will discover all the functions you need. I don't have them at hand as I am accessing this forum from a tablet...

Re: Opening an Excel Spreadsheet Using VBA

BruceNielsen — Sat, 11 Dec 2021 11:44:19 GMT

Thanks for pointing me in the right direction. Here's the code I ended up using:

Dim EXCELApplication As Object
Set EXCELApplication = CreateObject("Excel.Application")
EXCELApplication.workbooks.Open <spreadsheet name>
EXCELApplication.Visible = True