Consulting

Results 1 to 3 of 3

Thread: Formatting of Excel Data copied to Word bookmarks

  1. #1
    VBAX Newbie
    Joined
    Jun 2021
    Posts
    2
    Location

    Formatting of Excel Data copied to Word bookmarks

    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

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    VBAX Newbie
    Joined
    Jun 2021
    Posts
    2
    Location

    Thumbs up

    THANKS A LOT! It worked perfectly.

    Looking at your code I probably overcomplicated things...

    Cheers,
    GMK

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •