ich habe eine .docm Datei in die eine Exceltabelle eingefügt ist.
Kann mir jemand verraten, wie ich über den VBA-Code diese Tabelle ansprechen kann?
Danke im Voraus.
Sub Excel_in_CD()
Dim my_doc As Document
Dim obj_xls_Sheet As Object
Dim obj_OLE As Object
Dim str_TextInExcel As String
' Word
Set my_doc = ActiveDocument
' Ole Objekt in Word
With ActiveDocument.ActivePage.Shapes.Item(1).OLE
.Activate
' Excel
With .ActiveSheet
str_TextInExcel = .Range("A1").Value
End With
End With
MsgBox str_TextInExcel
Set my_doc = Nothing
End Sub
Es wird der Laufzeitfehler 438 ausgegeben.
'Erstellt unter Word 2010/Excel 2010
'Im Word VBA-Editor muss unter "Extras" der Verweis auf die _
Microsoft Excel x.y Object Library _
gesetzt werden.
Sub Excel_Object_ansprechen()
Dim objInlineShape As InlineShape
Dim objShape As Shape
Dim wdDoc As Document, varWerte()
Set wdDoc = ActiveDocument
'Inline-Shape-Objekte abarbeiten
Set objInlineShape = wdDoc.InlineShapes(2) 'Inline-Shape mit Verknüpfung auf Zellbereich in _
Exceltabelle
ReDim varWerte(1 To 3)
Call fncExcelObject_bearbeiten(objExcel:=objInlineShape, objType:=objInlineShape.Type, _
intCount:=1, varWerte:=varWerte)
MsgBox "Wert 1: " & varWerte(1) & vbLf & _
"Wert 2: " & varWerte(2) & vbLf & _
"Wert 3: " & varWerte(3), vbOKOnly, "Inline-Shape"
'Shape-Objekte abarbeiten
Set objShape = wdDoc.Shapes(1) 'Shape als eingebettetes Excel-Objekt
ReDim varWerte(1 To 5) '3 Werte zurückgeben
Call fncExcelObject_bearbeiten(objExcel:=objShape, objType:=objShape.Type, _
intCount:=101, varWerte:=varWerte)
MsgBox "Wert 1: " & varWerte(1) & vbLf & _
"Wert 2: " & varWerte(2) & vbLf & _
"Wert 3: " & varWerte(3) & vbLf & _
"Wert 4: " & varWerte(4) & vbLf & _
"Wert 5: " & varWerte(5), vbOKOnly, "Shape"
End Sub
Public Function fncExcelObject_bearbeiten(objExcel As Object, objType As Long, _
ByVal intCount As Integer, Optional varWerte) As Boolean
'objExcel = Excel-Objekt in Word-Dokument, das bearbeitet/ausgelesen werden soll
Dim objExcelApp As Excel.Application
Dim objExcelWkb As Excel.Workbook
Dim objExcelWks As Excel.Worksheet, bolExit As Boolean
objExcel.OLEFormat.DoVerb
Set objExcelApp = GetObject(Class:="Excel.Application")
Select Case objType
Case wdInlineShapeEmbeddedOLEObject, msoEmbeddedOLEObject
Set objExcelWkb = objExcelApp.Workbooks(objExcelApp.Workbooks.Count)
bolExit = fncExit(objApp:=objExcelApp, strWkb:=objExcelWkb.Name)
Select Case intCount
Case 101 'Shape-Object
Set objExcelWks = objExcelWkb.Worksheets(1)
With objExcelWks
varWerte(1) = .Range("B2").Text
varWerte(2) = .Range("B3").Text
varWerte(3) = .Range("B4").Text
varWerte(4) = .Range("A1").Text
varWerte(5) = .Range("D7").Text
End With
End Select
Case wdInlineShapeLinkedOLEObject, msoLinkedOLEObject
Set objExcelWkb = objExcelApp.Workbooks(objExcel.LinkFormat.SourceName)
bolExit = fncExit(objApp:=objExcelApp, strWkb:=objExcelWkb.Name)
Select Case intCount
Case 1 'Inline-Shape-Object
Set objExcelWks = objExcelWkb.Worksheets(Left(objExcel.OLEFormat.Label, _
InStr(1, objExcel.OLEFormat.Label, "!") - 1))
With objExcelWks
varWerte(1) = .Range("A1").Text
varWerte(2) = .Range("A2").Text
varWerte(3) = .Range("A3").Text
End With
End Select
objExcelApp.DisplayAlerts = False
objExcelWkb.Save
objExcelWkb.Close
objExcelApp.DisplayAlerts = True
End Select
If bolExit = True Then objExcelApp.Quit
End Function
Public Function fncExit(objApp As Excel.Application, strWkb As String) As Boolean
'Prüft, ob neben der Mappe strWkb noch ander Mappen in Excel eingeblendet sind
Dim varObject As Excel.Window
fncExit = True
If objApp.Visible = True Then
For Each varObject In objApp.Windows
If varObject.Visible = True _
And InStr(varObject.Caption, strWkb) = 0 Then
fncExit = False
Exit For
End If
Next
End If
End Function