Consulting

Results 1 to 11 of 11

Thread: Replace save with save as

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

    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

    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

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

    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
    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.

    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

  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
    Rename = "Test" & Format(Now(), "dd-mm-yyyy h:mm:ss") & ". Doc"
    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


    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 
        End With
    'wordApp.Quit
    quickEnd:
        Set wordFile = Nothing
        Set wordApp = Nothing
    End Sub

  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.


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

  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

    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

  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
  •