Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Feb 2014
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts

    From Excel open Word template then save using filepath name

    I am very much a newbie with vba and stuck on what must be the most basic if things.

    From Excel I am opening a Word.dotx file, and then copying a range from Excel to a bookmark location in the Word document.
    Then I simply want to save the newly created Word document with exactly the same filepath name and location as the template is in but
    with .docx file extension and a date stamp of “mmmm” (those will of course be the other way around)

    I have read masses of post, but unfortunately all that has done is confuse me even more as to how I proceed, can anybody help me?

    Code so far:

    Code:
    Sub CommandButton1_Click()
    
    Dim wd As Word.Application
    Dim wdDoc As Word.Document
    Dim WdRange As Word.Range
    
    ThisWorkbook.ActiveSheet.Range("B10:E40").Copy         ‘Range on Excel sheet of condensed Timesheets    
    Set wd = New Word.Application 
    Set wdDoc = wd.Documents.Open("C:\Users\Me\Documents\MyDocs\ServiceTimes\2014\Timesheets.dotx") 
    wd.Visible = True 
    Set WdRange = wdDoc.Bookmarks("Point_1").Range 
    wdDoc.Bookmarks.Add "Point_1", WdRange 
    WdRange.PasteAndFormat (wdPasteDefault) 
    ‘Now need to save the new document so Filepath name will read
    C:\Users\Me\Documents\MyDocs\ServiceTimes\2014\Timesheets(Month whatever).docx
      Exit Sub
    End Sub

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,862
    Thanks
    0
    Thanked 179 Times in 165 Posts
    Try something based on:
    Code:
    Sub CommandButton1_Click()
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
    Dim wdRng As Word.Range
    Dim wdPath As String
    wdPath = "C:\Users\Me\Documents\MyDocs\ServiceTimes\" & Format(Now, "YYYY") & "\"
    ThisWorkbook.ActiveSheet.Range("B10:E40").Copy 'Range on Excel sheet of condensed Timesheets
    Set wdApp = New Word.Application
    Set wdDoc = wdApp.Documents.Open(WdPath & "Timesheets.dotx") ' Create a new document
    With wdDoc
      'Populate the document
      Set wdRng = .Bookmarks("Point_1").Range
      wdRng.PasteAndFormat (wdPasteDefault)
      .Bookmarks.Add "Point_1", wdRng
      'Save the document
      .SaveAs2 Filename:=WdPath & "Timesheets_" & Format(Now, "YYYYMMDD") & ".docx", _
        FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
    End With
    wdApp.Visible = True
    Set wdDoc = Nothing: Set wdApp = Nothing
    End Sub
    Last edited by macropod; 2014-02-23 at 20:45.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #3
    New Lounger
    Join Date
    Feb 2014
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you macropod, your help is very much appreciated.

    As yet not had chance to fit what you gave me in with what I am working on. (slow process due to my inexperience)

    A cheeky after question if I may.
    I see you have used wdApp As Word.Application and wdRng As Word.Range instead of what I had used. In layman’s terms how do the terms differ and under what situations would you use one as opposed to the other?

    Always wanting to learn more.

    Many thanks.

  5. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,862
    Thanks
    0
    Thanked 179 Times in 165 Posts
    Quote Originally Posted by jjhs View Post
    I see you have used wdApp As Word.Application and wdRng As Word.Range instead of what I had used. In layman’s terms how do the terms differ and under what situations would you use one as opposed to the other?
    Largely a matter of preference; to be clear as to what aspect of the called application the variable relates, whilst at the same time avoiding anything that might be confused with a reserved term.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #5
    New Lounger
    Join Date
    Feb 2014
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    With a correction to my typo error in the File Path Name and bit of trial and error I have managed to get the template “Timesheets.dotx” open and saved with the new file name.
    However I have quit clearly not implemented what you gave me correctly as the newly created “Timesheets (February).docx” file is not appearing

    at this location;

    Code:
    C:\Users\Me\Documents\MyDocs\ServiceTimes\2014 Timesheets\   ”the new Timesheets (February) 2014.docx  file should show here”
    But instead it is here;

    Code:
    C:\Users\Me\AppData\Roaming\Microsoft\Templates\Timesheets (February) 2014.docx
    This is the code I have put in;


    Code:
    Sub Test_Macropod()
    
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
    Dim wdRng As Word.Range
    Dim wdPath As String
    
        wdPath = "C:\Users\Me \Documents\MyDocs\ServiceTimes\2014 Timesheets\” 
        ThisWorkbook.ActiveSheet.Range("B10:E40").Copy 'Range on Excel sheet of condensed Timesheets
        Set wdApp = New Word.Application
        Set wdDoc = wdApp.Documents.Open("C:\Users\Me\Documents\MyDocs\SerciceTimes\2014 Timesheets\Timesheets.dotx")
        With wdDoc
         	'Populate the document
         Set wdRng = .Bookmarks("Point_1").Range
         wdRng.PasteAndFormat (wdPasteDefault)
         .Bookmarks.Add "Point_1", wdRng
           'Save the document
          .SaveAs2 Filename:="Timesheets " & Format(Now, "(MMMM) YYYY") & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
        End With
        wdApp.Visible = True
        Set wdDoc = Nothing: Set wdApp = Nothing
        End Sub
    Have you any suggestions as to where I am going wrong?

  7. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,862
    Thanks
    0
    Thanked 179 Times in 165 Posts
    Having set wdPath (via wdPath = "C:\Users\Me\Documents\MyDocs\ServiceTimes\" & Format(Now, "YYYY") & "\"), you should then be using:
    Set wdDoc = wdApp.Documents.Open(WdPath & "Timesheets.dotx")
    when creating the new document and:
    .SaveAs2 Filename:=wdPath & "Timesheets_" & Format(Now, "(MMM) YYYY") & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
    when saving it. You'll note that I inserted 'Format(Now, "YYYY") & "\")' into the WdPath definition. That's so the code will auto-update the path each year. You can replace that with your hard-coded number if you prefer.

    By omitting the wdPath reference in your SaveAs2 line, Word apparently defaults to saving the file your templates folder.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #7
    New Lounger
    Join Date
    Feb 2014
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I would hate to let on just how long this has taken to get running; basic typing errors, template.dotx file kept being corrupted, lack of vba knowledge, but it was the removal of ;

    Code:
     “& Format(Now, "YYYY") & "\"
    from the end of the wdPath name that cracked it,
    so it now reads;

    Code:
    wdPath = "C:\Users\Me \Documents\MyDocs\ServiceTimes\2014 Timesheets\”
    Various other measures still need to add to code, ie to check if file open, if already exists prevent being over written and making the new document the active window but otherwise that’s it.

    Thank you again for all your help and advice

  9. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,862
    Thanks
    0
    Thanked 179 Times in 165 Posts
    I'm glad you've got it sorted.

    FWIW, The only reason I added ' “& Format(Now, "YYYY") & "\"' is that you had:
    Set wdDoc = wd.Documents.Open("C:\Users\Me\Documents\MyDocs\Se rviceTimes\2014\Timesheets.dotx")
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  10. The Following User Says Thank You to macropod For This Useful Post:

    jjhs (2014-02-25)

Posting Permissions

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