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

Code:

---

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

---

ok figured it out as follows

Code:

---

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

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]

Thanks Jake, i was also wondering if using this method i can add several cell contents into the document seperated as paragraphs?

Make sure you set a reference to Microsoft Word Object Library.

Code:

---

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

---

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

Code:

---

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

Thanks Jake I d already tried that and got the error this is not a valid file path

Code:

---

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

---

You are not able to use : either, sorry. Try something like this.

Code:

---

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

---

Thanks Again Jake, workes great

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

Code:

---

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

---

You're Welcome :beerchug:

Take Care