Formatting of Excel Data copied to Word bookmarks

[GMK]

Hello,

I have an Excel sheet containing all data in a table, some of data needs to be picked and transferred to a Word Template. This is done via VBS in Excel and bookmarks in Word. Currently I am struggling to display the right format for the time values. The strings get display correctly but the date gets imported as "TRUE" instead of "hh:mm". Would appreciate if somebody could look into my code and give me a hint how to solve this.

My code:


Dim lngZeile As Long
Dim strTeam As String
Dim strZeit As Date
Dim strOrt As String
Dim Paragraphe As Object, WordApp As Object, WordDoc As Object




Sub SpielbestaetigungTest()


File = "C:\...........\_Admin\IMPORT TEST VBA.dotx"
Set WordApp = CreateObject("Word.Application")
WordApp.Visible = True
Set WordDoc = WordApp.Documents.Add(Template:=File)




Sheets("Hauptblatt").Activate
lngZeile = ActiveCell.Row
strTeam = Cells(lngZeile, 5)
WordApp.ActiveDocument.Bookmarks("Team").Range.Text = strTeam


strZeit = Cells(lngZeile, 6)
WordApp.ActiveDocument.Bookmarks("Spielzeit").Range.Text = strZeit = Format(Cells(lngZeile, 6), "hh:mm")


strOrt = Cells(lngZeile, 7)
WordApp.ActiveDocument.Bookmarks("Ort").Range.Text = strOrt






End Sub

Thanks a lot,
GMK

[macropod]

You probably need no more than:

Sub SpielbestaetigungTest()
Dim lngZeile As Long
Dim WordApp As Object, WordDoc As Object


Sheets("Hauptblatt").Activate
lngZeile = ActiveCell.Row


File = "C:\...........\_Admin\IMPORT TEST VBA.dotx"
Set WordApp = CreateObject("Word.Application")
WordApp.Visible = True
Set WordDoc = WordApp.Documents.Add(Template:=File)


WordDoc.Bookmarks("Team").Range.Text = Cells(lngZeile, 5).Text
WordDoc.Bookmarks("Spielzeit").Range.Text = Cells(lngZeile, 6).Text
WordDoc.Bookmarks("Ort").Range.Text = Cells(lngZeile, 7).Text
End Sub

[GMK]

THANKS A LOT! It worked perfectly.

Looking at your code I probably overcomplicated things... :bug:

Cheers,
GMK