PDA

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