XYEventSourceName used for creating Excel to shapefile

KyungKim

Hello,

I am stuck in the middle of the macro program that will be used for creating shapefile from Excel in ArcGIS 9.2.  The part of the macro from the beginning up to creating a XYEvent display is as below.  The runtime error says, "XYEventSourceName has not been setup correctly,"  and it yellowed the line,  "Set pXYEventSource = pName.Open" toward the bottom.  I believe this line should be equivalent to  double click on an Excel file and to select one of the resulting tables in ArcMap, the action we normally take to create XYEvent out of it.  Probably this line is not fully reflecting that action?  I would appreciate very much if any of you can help me out of this hole!

Thank you so much for reading this!

Kyung


Sub EXToShape_New()
   
    ' FROM EXCEL TO XY EVENT
   
    ' Part 1: Designate the Excel file name
   
    ' Enter an Excel file name in Input box.
     Dim strInputFileName As String
     strInputFileName = InputBox("Enter the input file name:")
   
    ' Part 2: Define the work space of the input data file in the shape file folder
   
    Dim pWorkspaceName As IWorkspaceName
    Dim pTableName As ITableName
    Dim pDatasetName As IDatasetName
   
    ' Define a workspace with path name and workspacefactory ID.
    Set pWorkspaceName = New WorkspaceName
    pWorkspaceName.PathName = "C:\MyFolder\" & strInputFileName & ".xls"
    pWorkspaceName.WorkspaceFactoryProgID = "esriDataSourcesGDB.ExcelWorkspaceFactory"
   
    ' Define the dataset as table and set its workspace to the pre-defined shape file workspace.
    Set pTableName = New TableName
    Set pDatasetName = pTableName
    pDatasetName.Name = strInputFileName
    Set pDatasetName.WorkspaceName = pWorkspaceName
   
    ' Part 3: Define an XYevent source.
   
    Dim pXYEvent2FieldsProperties As IXYEvent2FieldsProperties
    Dim pSpatialReferenceFactory As ISpatialReferenceFactory
    Dim pGeographicCoordinateSystem As IGeographicCoordinateSystem
    Dim pXYEventSourceName As IXYEventSourceName
   
    ' Create XY field properties that will go to the event.
    Set pXYEvent2FieldsProperties = New XYEvent2FieldsProperties
    With pXYEvent2FieldsProperties
        .XFieldName = "LONG"
        .YFieldName = "LAT"
        .ZFieldName = ""
    End With
   
    ' Set coordinate system by creating spatial reference factory and using its method.
    Set pSpatialReferenceFactory = New SpatialReferenceEnvironment
    Set pGeographicCoordinateSystem = pSpatialReferenceFactory.CreateGeographicCoordinateSystem(esriSRGeoCS_WGS1984)
   
    ' Create and specify the event source and setting its properties using
    ' above created XY field properties, coordinate system and table name.
    Set pXYEventSourceName = New XYEventSourceName
    With pXYEventSourceName
        Set .EventProperties = pXYEvent2FieldsProperties
        Set .SpatialReference = pGeographicCoordinateSystem
        Set .EventTableName = pTableName
    End With

    ' Part 4: Create a layer from the event source and display the layer.
   
    Dim pMxDoc As IMxDocument
    Dim pMap As IMap
    Dim pName As IName
    Dim pXYEventSource As IXYEventSource
    Dim pFLayer As IFeatureLayer
    Set pMxDoc = ThisDocument
    Set pMap = pMxDoc.FocusMap
   
    ' Open the event source by opening method of IName interface.
    Set pName = pXYEventSourceName
    Set pXYEventSource = pName.Open
   
    ' Create a new feature layer,
    ' set it to the event source, give it a name and
    ' add the layer to the active map.
    Set pFLayer = New FeatureLayer
    Set pFLayer.FeatureClass = pXYEventSource
    pFLayer.Name = "XY Events"
    pMap.AddLayer pFLayer
    pMxDoc.ActiveView.Refresh

---
---

End Sub

Hornbydd

Is your Exel document formatted correctly?

1. First row are headers
2. Headers without spaces or illegal charaters
3. Your XY fields are formatted as numbers
4. No null data

Duncan