Opening an Excel Spreadsheet Using VBA

[BruceNielsen]

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?

[DuncanHornby · Accepted solution]

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

[BruceNielsen]

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

[DuncanHornby]

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...

[BruceNielsen]

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