Consulting

Results 1 to 11 of 11

Thread: Solved: replace save with save as

  1. #1
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location

    Solved: replace save with save as

    Previous post seened to fail, probably my title

    Can anyone help with this, im trying to use excel to open a word document add a line of text and then save it with a different name

    Im most of the way there but cant figure out the save as bit

    any help greatfully appreciated

    Gibbo

    [VBA] Option Explicit
    Sub openWordPlease()
    ' dont forget to set a ref to word object library
    Dim wordApp As Object
    Dim wordFile As Object
    Dim myFile As String
    Dim Rename As String
    myFile = ThisWorkbook.Path & "\Test.doc"
    Rename = "Test" & Now() & ". Doc"
    Set wordApp = CreateObject("Word.Application")
    wordApp.Visible = True
    Set wordFile = wordApp.Documents.Open(myFile)

    On Error GoTo 0
    If wordFile Is Nothing Then
    MsgBox "File is not found!", vbInformation, "ERROR"
    GoTo quickEnd
    End If
    With wordFile
    .Range.Text = Range("A1").Value
    .SaveAs ThisWorkbook.Path & Rename <<< My Error
    End With
    'wordApp.Quit
    quickEnd:
    Set wordFile = Nothing
    Set wordApp = Nothing
    End Sub
    [/VBA]

  2. #2
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location
    ok figured it out as follows

    [VBA] Option Explicit
    Sub openWordPlease()
    ' dont forget to set a ref to word object library

    Dim wordApp As Object
    Dim wordFile As Object
    Dim myFile As String
    Dim Rename As String


    myFile = ThisWorkbook.Path & "\Test.doc"
    Rename = ThisWorkbook.Path & "\Test3.doc"
    Set wordApp = CreateObject("Word.Application")
    wordApp.Visible = True
    Set wordFile = wordApp.Documents.Open(myFile)

    On Error GoTo 0
    If wordFile Is Nothing Then
    MsgBox "File is not found!", vbInformation, "ERROR"
    GoTo quickEnd
    End If

    With wordFile
    .Range.Text = Range("A1").Value
    .SaveAs Rename
    End With

    'wordApp.Quit
    quickEnd:
    Set wordFile = Nothing
    Set wordApp = Nothing
    End Sub[/VBA]

    Thanks for looking

  3. #3
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    Now() uses / to seperate the day/month/year. Unfortunately / are also used as path seperators for folders.

    So when you say

    [VBA]SaveAs ThisWorkbook.Path & Rename[/VBA]

    You are really saying

    [VBA]SaveAs ThisWorkbook.Path & m/d/y...[/VBA]

    And of course those folders do not exist. What you need to do is specify the format and do not use /.

    [VBA]Format(Now(), "dd-mm-yyyy h:mm:ss")[/VBA]

  4. #4
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location
    Thanks Jake, i was also wondering if using this method i can add several cell contents into the document seperated as paragraphs?

  5. #5
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    Make sure you set a reference to Microsoft Word Object Library.
    [vba]
    Option Explicit

    Sub openWordPlease()

    Dim wordApp As New Word.Application
    Dim wordFile As Word.Document
    Dim myFile As String
    Dim Rename As String
    Dim i As Long

    wordApp.Visible = True
    Set wordFile = wordApp.Documents.Add

    For i = 1 To 3
    wordApp.Selection.TypeText Text:=Range("A" & i).Value
    wordApp.Selection.TypeParagraph
    Next i

    End Sub
    [/vba]

  6. #6
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location
    Thanks Jake that works great

    Im still having problems saving with a date format though, i ve tried various ways of getting your piece of code to fit in with mine but cant seem to figure it out, can you show me how i would add it to my original code I posted

    Many thanks

  7. #7
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    [VBA]Rename = "Test" & Format(Now(), "dd-mm-yyyy h:mm:ss") & ". Doc" [/VBA]
    Change the format as needed. For example, use "mmm dd" to add the short month and day only to the file name (i.e., Feb 20).

  8. #8
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location
    Thanks Jake I d already tried that and got the error this is not a valid file path

    [VBA]
    Option Explicit
    Sub openWordPlease()
    ' dont forget to set a ref to word object library
    Dim wordApp As Object
    Dim wordFile As Object
    Dim myFile As String
    Dim Rename As String

    myFile = ThisWorkbook.Path & "\Test.doc"
    'save file as
    Rename = "Test" & Format(Now(), "dd-mm-yyyy h:mm:ss") & ". Doc"
    Set wordApp = CreateObject("Word.Application")
    wordApp.Visible = True
    Set wordFile = wordApp.Documents.Open(myFile)

    On Error GoTo 0
    If wordFile Is Nothing Then
    MsgBox "File is not found!", vbInformation, "ERROR"
    GoTo quickEnd
    End If

    With wordFile
    .Range.Text = Range("A1").Value
    .Range.Text = Range("A2").Value
    .SaveAs Rename <<<<<<<<<<<<<<<<<<Error Here
    End With

    'wordApp.Quit
    quickEnd:
    Set wordFile = Nothing
    Set wordApp = Nothing
    End Sub
    [/VBA]

  9. #9
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    You are not able to use : either, sorry. Try something like this.

    [VBA]
    Rename = "Test" & Format(Now(), "dd-mm-yyyy h.mm.ss") & ". Doc"
    [/VBA]

  10. #10
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location
    Thanks Again Jake, workes great

    I ve posted the finished code below for anyone else with a similar problem

    [VBA] Option Explicit
    Sub openWordPlease()
    ' dont forget to set a ref to word object library
    Dim wordApp As Object
    Dim wordFile As Object
    Dim myFile As String
    Dim Rename As String

    myFile = ThisWorkbook.Path & "\Test.doc"
    'save file as
    Rename = ThisWorkbook.Path & "\" & "Test" & _
    Format(Now(), "dd-mm-yyyy h.mm.ss") & ". Doc"
    Set wordApp = CreateObject("Word.Application")
    wordApp.Visible = True
    Set wordFile = wordApp.Documents.Open(myFile)

    On Error GoTo 0
    If wordFile Is Nothing Then
    MsgBox "File is not found!", vbInformation, "ERROR"
    GoTo quickEnd
    End If

    With wordFile
    .Range.Text = Range("A1").Value
    .Range.Text = Range("A2").Value
    .SaveAs Rename
    End With

    'wordApp.Quit
    quickEnd:
    Set wordFile = Nothing
    Set wordApp = Nothing
    End Sub [/VBA]

  11. #11
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    You're Welcome

    Take Care

Posting Permissions

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