Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1648to1652
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

VBA Bookmarker

VBA Bookmarker
16.10.2018 11:55:57
Molle
Hallo Zusammen,
ich probiere mich gerade an einer Erstellung eines Berichtes, was auch eigentlich gut funktioniert. Nur hier treten nun zwei Probleme auf.
Mit Bookmarks kann ich einzelne Zellen ansprechen, aber wie kann ich z.B. ("A1:B25") ansprechen.
Wenn ich dies in meinen Code eingebe funktioniert dies einfach nicht. Nachfolgend mein Beispiel:
.ActiveDocument.Bookmarks("GFShare").Range.Text = Sheets("Tabelle 1").Range("A1:B25")
Mein zweites Problem ist, wie kriege ich es hin, Grafiken bzw. Diagramme, welche in Excel vorhanden sind an mein Textmarker in Word zu senden?
Ich hoffe die sind nicht zu viele Fragen :)
Liebe Grüße
Molle

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Word-VBA
16.10.2018 12:10:30
Fennek
Hallo,
lade zwei kleine, aber aussagekräftige Dateien (1x xlsx, 1x docx) hoch bei
https://www.ms-office-forum.net/forum/forumdisplay.php?f=30
mfg
Warum soll er es denn dort...
16.10.2018 13:22:30
Case
Hallo, :-)
... hochladen - geht doch auch hier. ;-)
Servus
Case

AW: Hier mal ein Beispiel,...
16.10.2018 16:03:16
Molle
Moin Case,
das mit dem Bild klappt auch wunderbar, nur wenn ich dies in meinem Code mit einbaue funktioniert dies nicht mehr.
Kann ich dies überhaupt in mein Code mit einbauen oder gilt das dann als eigenständiges Modul?
Ebenso verhält sich dies mit dem Code den du verlinkt hast, einzeln klappt alles wunderbar. Nur wenn ich dies in meinem Code versuche mit einzubauen, geht mir das ganze ding hoch.
Nachfolgend ein Auszug meines Codes, er ist nicht schön aber funktioniert ;)
Option Explicit
Sub Word()
On Error Resume Next
MsgBox "Report wird geöffnet"
MsgBox "Report ist auf Laufwerk _ K: \*****\_El Salvador\******\7.  _
Report_Master_Stage_II_El_Salvador hinterlegt"
MsgBox ""
Dim WordApp As New Word.Application
With WordApp
.Visible = True: .WindowState = xlMaximized
.Documents.Open Filename:="K:\International\_El Salvador\01. Market screening\7.  _
Report_Master_Stage_II_El_Salvador\20181001_Report ****.docx"
'Fügt Daten aus Excel an Textmarke im aktiven Worddokument ein
Dim doc As Word.Document, TextMarke As Word.Bookmark, TabZeile As Word.Row
Dim strWordDatei As String
Dim wks As Worksheet, Zelle As Range, I As Long, Zeile As Long, strText As String
Dim Bereich As Range, Zaehler As Long
Dim objWordRange As Object
Dim objDocument As Object
Dim objDialog As Object
Dim strDoc As String
Dim WordObj As Object
Dim objWord As Object
Dim objDoc As Object
Dim myWord As Object
'0.1 Country overview
.ActiveDocument.Bookmarks("Überschrift").Range.Text = Sheets("Country overview").Range("C1") _
'1 .General Framework
.ActiveDocument.Bookmarks("GFÜ").Range.Text = Sheets("1. General Framework").Range("B2") _
.ActiveDocument.Bookmarks("GFÜS").Range.Text = Sheets("1. General Framework"). _
Range("B3")
'1 .General Framework Tabelle Share of RE 2017
'1.1 General Framework Tabelle Share of RE 2017 in MW
.ActiveDocument.Bookmarks("GFShare").Range.Text = Sheets("1. General  _
Framework").Range("B98")
.ActiveDocument.Bookmarks("D1").Range.Text = Sheets("1. General  _
Framework").Range("D100")
.ActiveDocument.Bookmarks("D2").Range.Text = Sheets("1. General  _
Framework").Range("E100")
.ActiveDocument.Bookmarks("Oil").Range.Text = Sheets("1.  _
General Framework").Range("D102")
.ActiveDocument.Bookmarks("Hy").Range.Text = Sheets("1.  _
General Framework").Range("D103")
.ActiveDocument.Bookmarks("Bio").Range.Text = Sheets(" _
1. General Framework").Range("D104")
.ActiveDocument.Bookmarks("Geo").Range.Text =  _
Sheets("1. General Framework").Range("D105")
.ActiveDocument.Bookmarks("Wind").Range.Text =  _
Sheets("1. General Framework").Range("D106")
.ActiveDocument.Bookmarks("Solar").Range. _
Text = Sheets("1. General Framework").Range("D107")
.ActiveDocument.Bookmarks("Gas").Range. _
Text = Sheets("1. General Framework").Range("D108")
.ActiveDocument.Bookmarks("Nuc"). _
Range.Text = Sheets("1. General Framework").Range("D109")
.ActiveDocument.Bookmarks(" _
Coal").Range.Text = Sheets("1. General Framework").Range("D110")
.ActiveDocument. _
Bookmarks("Net").Range.Text = Sheets("1. General Framework").Range("D111")
.ActiveDocument. _
Bookmarks("Thermal").Range.Text = Sheets("1. General Framework").Range("D112")
. _
ActiveDocument.Bookmarks("Total").Range.Text = Sheets("1. General Framework").Range("D113")
'1.1 General Framework Tabelle Share of RE 2017 in %
.ActiveDocument.Bookmarks("OilP").Range.Text = Sheets("1. General Framework" _
).Range("E102")
.ActiveDocument.Bookmarks("HyP").Range.Text = Sheets("1. General  _
Framework").Range("E103")
.ActiveDocument.Bookmarks("BioP").Range.Text = Sheets("1. General  _
Framework").Range("E104")
.ActiveDocument.Bookmarks("GeoP").Range.Text = Sheets("1.  _
General Framework").Range("E105")
.ActiveDocument.Bookmarks("WindP").Range.Text = Sheets("1.  _
General Framework").Range("E106")
.ActiveDocument.Bookmarks("SolarP").Range.Text =  _
Sheets("1. General Framework").Range("E107")
.ActiveDocument.Bookmarks("GasP").Range.Text =  _
Sheets("1. General Framework").Range("E108")
.ActiveDocument.Bookmarks("NucP").Range.Text =  _
Sheets("1. General Framework").Range("E109")
.ActiveDocument.Bookmarks("CoalP").Range. _
Text = Sheets("1. General Framework").Range("E110")
.ActiveDocument.Bookmarks("NetP"). _
Range.Text = Sheets("1. General Framework").Range("E111")
.ActiveDocument.Bookmarks(" _
ThermalP").Range.Text = Sheets("1. General Framework").Range("E112")
.ActiveDocument.Bookmarks(" _
TotalP").Range.Text = Sheets("1. General Framework").Range("E113")
'2. Tax Framework
.ActiveDocument.Bookmarks("TaxÜ").Range.Text = Sheets("2. Tax Framework").Range("A2")
.ActiveDocument.Bookmarks("TaxS").Range.Text = Sheets("2. Tax Framework").Range(" _
A3")
'Tax Incentives
'1. Law
.ActiveDocument.Bookmarks("TaxIn").Range.Text = Sheets("2. Tax Framework").Range(" _
A67")
.ActiveDocument.Bookmarks("Tax1B").Range.Text = Sheets("2. Tax Framework"). _
Range("A68")
.ActiveDocument.Bookmarks("Tax1L").Range.Text = Sheets("2. Tax Framework"). _
Range("B68")
.ActiveDocument.Bookmarks("Tax1D").Range.Text = Sheets("2. Tax  _
Framework").Range("C68")
.ActiveDocument.Bookmarks("Tax1S").Range.Text = Sheets("2. Tax  _
Framework").Range("D68")
'2. Law
.ActiveDocument.Bookmarks("Tax2B").Range.Text = Sheets("2. Tax Framework").Range(" _
A69")
.ActiveDocument.Bookmarks("Tax2L").Range.Text = Sheets("2. Tax Framework"). _
Range("B69")
.ActiveDocument.Bookmarks("Tax2D").Range.Text = Sheets("2. Tax Framework"). _
Range("C69")
.ActiveDocument.Bookmarks("Tax2S").Range.Text = Sheets("2. Tax  _
Framework").Range("D69")
'3. Law
.ActiveDocument.Bookmarks("Tax3B").Range.Text = Sheets("2. Tax Framework").Range(" _
A70")
.ActiveDocument.Bookmarks("Tax3L").Range.Text = Sheets("2. Tax Framework"). _
Range("B70")
.ActiveDocument.Bookmarks("Tax3D").Range.Text = Sheets("2. Tax Framework"). _
Range("C70")
.ActiveDocument.Bookmarks("Tax3S").Range.Text = Sheets("2. Tax  _
Framework").Range("D70")
'4. Law
.ActiveDocument.Bookmarks("Tax4B").Range.Text = Sheets("2. Tax Framework").Range(" _
A71")
.ActiveDocument.Bookmarks("Tax4L").Range.Text = Sheets("2. Tax Framework"). _
Range("B71")
.ActiveDocument.Bookmarks("Tax4D").Range.Text = Sheets("2. Tax Framework"). _
Range("C71")
.ActiveDocument.Bookmarks("Tax4S").Range.Text = Sheets("2. Tax  _
Framework").Range("D71")
End With
Set objWordRange = Nothing
Set objDoc = Nothing
End Sub

Ich hoffe man kann dies in meinem Code mit einbauen
Viele Grüße und danke für eure Hilfe :)
Molle
Anzeige
AW: VBA Bookmarker
17.10.2018 08:46:02
Molle
Moin:)
hier ist mein angepasster Code, welcher nicht funktioniert. Das Beispiel welches du aufführst kann ich gar nicht einbauen :( Es will einfach nicht so wie ich will.
LG
Molle
Option Explicit
Sub Word()
On Error Resume Next
MsgBox "Report wird geöffnet"
MsgBox "Report ist auf Laufwerk _ K: \*****\_El Salvador\******\7. Report_Master_Stage_II_El_Salvador hinterlegt"
MsgBox
Dim WordApp As New Word.Application
With WordApp
.Visible = True: .WindowState = xlMaximized
.Documents.Open Filename:="K:\International\_El Salvador\01. Market screening\7. Report_Master_Stage_II_El_Salvador\20181001_Report ****.docx"
'Fügt Daten aus Excel an Textmarke im aktiven Worddokument ein
Dim doc As Word.Document, TextMarke As Word.Bookmark, TabZeile As Word.Row
Dim strWordDatei As String
Dim wks As Worksheet, Zelle As Range, I As Long, Zeile As Long, strText As String
Dim Bereich As Range, Zaehler As Long
Dim objWordRange As Object
Dim objDocument As Object
Dim objDialog As Object
Dim strDoc As String
Dim WordObj As Object
Dim objWord As Object
Dim objDoc As Object
Dim myWord As Object
'Bild einfügen
ThisWorkbook.Worksheets("0. Map").Shapes("Grafik 3").Copy
Set objWordRange = objDoc.Bookmarks("Grafik 3").Range
objWordRange.Paste
'0.1 Country overview
.ActiveDocument.Bookmarks("Überschrift").Range.Text = Sheets("Country overview").Range("C1")
'1 .General Framework
.ActiveDocument.Bookmarks("GFÜ").Range.Text = Sheets("1. General Framework").Range("B2")
.ActiveDocument.Bookmarks("GFÜS").Range.Text = Sheets("1. General Framework").Range("B3")
'1 .General Framework Tabelle Share of RE 2017
'1.1 General Framework Tabelle Share of RE 2017 in MW
.ActiveDocument.Bookmarks("GFShare").Range.Text = Sheets("1. General Framework").Range("B98")
.ActiveDocument.Bookmarks("D1").Range.Text = Sheets("1. General Framework").Range("D100")
.ActiveDocument.Bookmarks("D2").Range.Text = Sheets("1. General Framework").Range("E100")
.ActiveDocument.Bookmarks("Oil").Range.Text = Sheets("1. General Framework").Range("D102")
.ActiveDocument.Bookmarks("Hy").Range.Text = Sheets("1. General Framework").Range("D103")
.ActiveDocument.Bookmarks("Bio").Range.Text = Sheets("1. General Framework").Range("D104")
.ActiveDocument.Bookmarks("Geo").Range.Text = Sheets("1. General Framework").Range("D105")
.ActiveDocument.Bookmarks("Wind").Range.Text = Sheets("1. General Framework").Range("D106")
.ActiveDocument.Bookmarks("Solar").Range.Text = Sheets("1. General Framework").Range("D107")
.ActiveDocument.Bookmarks("Gas").Range.Text = Sheets("1. General Framework").Range("D108")
.ActiveDocument.Bookmarks("Nuc").Range.Text = Sheets("1. General Framework").Range("D109")
.ActiveDocument.Bookmarks("Coal").Range.Text = Sheets("1. General Framework").Range("D110")
.ActiveDocument.Bookmarks("Net").Range.Text = Sheets("1. General Framework").Range("D111")
.ActiveDocument.Bookmarks("Thermal").Range.Text = Sheets("1. General Framework").Range("D112")
.ActiveDocument.Bookmarks("Total").Range.Text = Sheets("1. General Framework").Range("D113")
'1.1 General Framework Tabelle Share of RE 2017 in %
.ActiveDocument.Bookmarks("OilP").Range.Text = Sheets("1. General Framework").Range("E102")
.ActiveDocument.Bookmarks("HyP").Range.Text = Sheets("1. General Framework").Range("E103")
.ActiveDocument.Bookmarks("BioP").Range.Text = Sheets("1. General Framework").Range("E104")
.ActiveDocument.Bookmarks("GeoP").Range.Text = Sheets("1. General Framework").Range("E105")
.ActiveDocument.Bookmarks("WindP").Range.Text = Sheets("1. General Framework").Range("E106")
.ActiveDocument.Bookmarks("SolarP").Range.Text = Sheets("1. General Framework").Range("E107")
.ActiveDocument.Bookmarks("GasP").Range.Text = Sheets("1. General Framework").Range("E108")
.ActiveDocument.Bookmarks("NucP").Range.Text = Sheets("1. General Framework").Range("E109")
.ActiveDocument.Bookmarks("CoalP").Range.Text = Sheets("1. General Framework").Range("E110")
.ActiveDocument.Bookmarks("NetP").Range.Text = Sheets("1. General Framework").Range("E111")
.ActiveDocument.Bookmarks("ThermalP").Range.Text = Sheets("1. General Framework").Range("E112")
.ActiveDocument.Bookmarks("TotalP").Range.Text = Sheets("1. General Framework").Range("E113")
'2. Tax Framework
.ActiveDocument.Bookmarks("TaxÜ").Range.Text = Sheets("2. Tax Framework").Range("A2")
.ActiveDocument.Bookmarks("TaxS").Range.Text = Sheets("2. Tax Framework").Range("A3")
'Tax Incentives
'1. Law
.ActiveDocument.Bookmarks("TaxIn").Range.Text = Sheets("2. Tax Framework").Range("A67")
.ActiveDocument.Bookmarks("Tax1B").Range.Text = Sheets("2. Tax Framework").Range("A68")
.ActiveDocument.Bookmarks("Tax1L").Range.Text = Sheets("2. Tax Framework").Range("B68")
.ActiveDocument.Bookmarks("Tax1D").Range.Text = Sheets("2. Tax Framework").Range("C68")
.ActiveDocument.Bookmarks("Tax1S").Range.Text = Sheets("2. Tax Framework").Range("D68")
'2. Law
.ActiveDocument.Bookmarks("Tax2B").Range.Text = Sheets("2. Tax Framework").Range("A69")
.ActiveDocument.Bookmarks("Tax2L").Range.Text = Sheets("2. Tax Framework").Range("B69")
.ActiveDocument.Bookmarks("Tax2D").Range.Text = Sheets("2. Tax Framework").Range("C69")
.ActiveDocument.Bookmarks("Tax2S").Range.Text = Sheets("2. Tax Framework").Range("D69")
'3. Law
.ActiveDocument.Bookmarks("Tax3B").Range.Text = Sheets("2. Tax Framework").Range("A70")
.ActiveDocument.Bookmarks("Tax3L").Range.Text = Sheets("2. Tax Framework").Range("B70")
.ActiveDocument.Bookmarks("Tax3D").Range.Text = Sheets("2. Tax Framework").Range("C70")
.ActiveDocument.Bookmarks("Tax3S").Range.Text = Sheets("2. Tax Framework").Range("D70")
'4. Law
.ActiveDocument.Bookmarks("Tax4B").Range.Text = Sheets("2. Tax Framework").Range("A71")
.ActiveDocument.Bookmarks("Tax4L").Range.Text = Sheets("2. Tax Framework").Range("B71")
.ActiveDocument.Bookmarks("Tax4D").Range.Text = Sheets("2. Tax Framework").Range("C71")
.ActiveDocument.Bookmarks("Tax4S").Range.Text = Sheets("2. Tax Framework").Range("D71")
'3. Financing
.ActiveDocument.Bookmarks("FinÜ").Range.Text = Sheets("3. Financing").Range("A2")
.ActiveDocument.Bookmarks("FinS").Range.Text = Sheets("3. Financing").Range("A3")
'4. Competitors
.ActiveDocument.Bookmarks("ComÜ").Range.Text = Sheets("4. Competitors").Range("A2")
.ActiveDocument.Bookmarks("ComS").Range.Text = Sheets("4. Competitors").Range("A3")
'5. Country economics
.ActiveDocument.Bookmarks("CEÜ").Range.Text = Sheets("5.Country economics").Range("A2")
.ActiveDocument.Bookmarks("CES").Range.Text = Sheets("5.Country economics").Range("A3")
'6. Energy prices
.ActiveDocument.Bookmarks("EPÜ").Range.Text = Sheets("6. Energy prices").Range("A2")
.ActiveDocument.Bookmarks("EPS").Range.Text = Sheets("6. Energy prices").Range("A3")
'Distribution Company (Tabelle)
Anzeige
Du wirst da wohl...
17.10.2018 09:46:39
Case
Hallo, :-)
... niemanden finden, der das nachbaut. ;-)
Lade die Excel- und die Worddatei (gezippt und die Daten ggf. anonymisiert) hier hoch, dann sehen wir weiter. ;-)
Servus
Case

Hilfe
19.10.2018 13:57:03
Molle
Kann mir keiner dabei helfen? :(
Liebe Grüße

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige