View Full Version : [SOLVED:] Replace save with save as
gibbo1715
02-20-2005, 04:16 AM
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
gibbo1715
02-20-2005, 04:25 AM
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
Jacob Hilderbrand
02-20-2005, 04:30 AM
Now() uses / to seperate the day/month/year. Unfortunately / are also used as path seperators for folders.
So when you say
SaveAs ThisWorkbook.Path & Rename
You are really saying
SaveAs ThisWorkbook.Path & m/d/y...
And of course those folders do not exist. What you need to do is specify the format and do not use /.
Format(Now(), "dd-mm-yyyy h:mm:ss")
gibbo1715
02-20-2005, 04:33 AM
Thanks Jake, i was also wondering if using this method i can add several cell contents into the document seperated as paragraphs?
Jacob Hilderbrand
02-20-2005, 04:49 AM
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
gibbo1715
02-20-2005, 04:52 AM
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
Jacob Hilderbrand
02-20-2005, 04:57 AM
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).
gibbo1715
02-20-2005, 05:03 AM
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
Jacob Hilderbrand
02-20-2005, 05:12 AM
You are not able to use : either, sorry. Try something like this.
Rename = "Test" & Format(Now(), "dd-mm-yyyy h.mm.ss") & ". Doc"
gibbo1715
02-20-2005, 05:21 AM
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
Jacob Hilderbrand
02-20-2005, 05:35 AM
You're Welcome :beerchug:
Take Care
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.