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?

Hornbydd

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