Hi all,I am trying to figure out how to highlight/select an Excel table on the Source tab (using VBA) that has been programatically added to the map document through a form. The goal is then to take the selected table and automatically export it to a file GDB (the equivalent of right-clicking on the table>Data>Export)...but first the table must be selected in the TOC! I've tried embedding it directly into the button_click code, I've tried adding it as a called sub-routine, I have found several posts on the forums and items in the EDN that refer to this process, but none of it has quite gotten me there yet. Pardon my newbie-ness and any blatant errors I am making :confused:. Thanks in advance for any help you can give me!Working code that adds an Excel table to map document and switches TOC view to source tab:Private Sub CommandButton1_Click()
If txtExcel.Text = "" Then
MsgBox "Please put The Excel Files"
Exit Sub
ElseIf List3.ListCount = 0 Then
MsgBox "You have not selected any fields to display.", vbExclamation, "No Fields Available"
Exit Sub
End If
Dim myPath As String
myPath = txtExcel.Text 'excel path location
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = " & myPath & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES;"""
Dim pPropset As IPropertySet
Set pPropset = New PropertySet
pPropset.SetProperty "CONNECTSTRING", connStr
'++ connect to database
Dim pWorkspace As IFeatureWorkspace
Dim pWorkspaceFact As IWorkspaceFactory
Set pWorkspaceFact = New OLEDBWorkspaceFactory
Set pWorkspace = pWorkspaceFact.Open(pPropset, 0)
If pWorkspace Is Nothing Then Exit Sub
For i = 0 To List1.ListCount - 1 'select sheet
If List1.Selected(i) = True Then
Dim sheet As String
sheet = (List1.List(i))
End If
Next
Dim pTable As ITable
Set pTable = pWorkspace.OpenTable(sheet)
If pTable Is Nothing Then
MsgBox "The table was not found"
Exit Sub
End If
'++ Create a table collection and assign the new table to it
MsgBox "Table added", vbInformation
Dim pStTab As IStandaloneTable
Dim pStTabColl As IStandaloneTableCollection
Dim pMap As IMap
Dim pMxDoc As IMxDocument
Set pMxDoc = ThisDocument
Set pMap = pMxDoc.ActiveView.FocusMap
Set pStTab = New StandaloneTable
Set pStTab.Table = pTable
Set pStTabColl = pMap
If Not TypeOf pTable Is IStandaloneTable Then
Set pStTab.Table = pTable
pStTab.Name = Replace$(pStTab.Name, "$", "", 1, -1, vbTextCompare)
End If
pStTabColl.AddStandaloneTable pStTab
pMxDoc.UpdateContents
'Switch TOC view to Source Tab
Call SetContentsView("Source")
End Sub
One failed attempt at setting the table as the selected/highlighted item:Dim pContView As IContentsView
Set pContView = pMxDoc.ContentsView(1) ' Source view
Set pContView.ContextItem = pStTab '--->Get "Object Required" error message
pMxDoc.UpdateContents
Another failed attempt at setting the table as the selected/highlighted item:Referred to:http://forums.esri.com/thread.asp?t=67188&f=992&c=93andhttp://edndoc.esri.com/arcobjects/9.0/ComponentHelp/esriArcMapUI/IContentsView_SelectedItem_Example....Dim pStandaloneTable As IStandaloneTable
Dim pEnumStandaloneTable As IEnumStandaloneTable
Dim pContView As IContentsView
Set pMxDoc = Application.Document
Set pMap = pMxDoc.FocusMap
Set pContView = pMxDoc.ContentsView(1) ' Source view
Set pEnumStandaloneTable = pMap.Layers(, True) '--->Results in Type Mismatch Error (Run-time error 13)
pEnumStandaloneTable.Reset
Set pStandaloneTable = pEnumStandaloneTable.Next
Do Until pStandaloneTable Is Nothing
If pStandalone.Name = pStTable.Name Then ' this is the layer I want to activate/select
pContView.ContextItem = pStandaloneTable
pMxDoc.UpdateContents
Exit Do
End If
Set pStandaloneTable = pEnumStandaloneTable.Next
Loop
Thank You in Advance,Patricia PendletonCal State University, NorthridgeDept. of GeographyCenter for Geographical Studies