Script to SaveAs?

[ks100]

How would I go about saving a workbook as to a new location?

The code would be immedietly following the file being saved over at its current location.

Basically what is already scripted is:

1. File is opened.
2. Data is pasted from another spreadsheet.
3. File is saved.
4. File is closed.

I need to add a step between 3 and 4 that would:

Save file as (same file name) in R:\logs

So in other words, it the final script needs to open a file, modify it, save over itself, then save a duplicate to a new location (also overwriting a file).

[JieJenn]

Regard step 3 and 4, maybe

Please Login or Register  to view this content.

[ks100]

Regard step 3 and 4, maybe

Please Login or Register  to view this content.

That ends up saving a workbook to R:\logs that is empty.

[JieJenn]

My code is only addressing step 3 and step 4.

[JOHN H. DAVIS]

Can you provide a copy of the code you are using?

[JP2511]

Hi I have an issue with saving the word document with the value of a Cell, also I want to make it a loop, meaning it will create e.g. 60 letters with different info, and to save it with a cell value which is for every save different, please can someone help me? See code:

Sub CertGenerator()

On Error GoTo errorHandler

Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim mywdRange As Word.Range
Dim Customer As Excel.Range
Dim Deliverynr As Excel.Range
Dim POnumber As Excel.Range

Set wdApp = New Word.Application
With wdApp
.Visible = True
.WindowState = wdWindowStateMaximize
End With
'Set the cell you want to copy'
Set myDoc = wdApp.Documents.Add(Template:="C:\Test\Authorisationform.doc")
Set Customer = Sheets("Sheet1").Range("A2")
Set Deliverynr = Sheets("Sheet1").Range("D2")
Set POnumber = Sheets("Sheet1").Range("E2")
Set Colnr = Sheets("Sheet1").Range("D2")
'Set the bookmark names'
With myDoc.Bookmarks
.Item("Customer").Range.InsertAfter Customer
.Item("Deliverynr").Range.InsertAfter Deliverynr
.Item("POnumber").Range.InsertAfter POnumber
.Item("Colnr").Range.InsertAfter Colnr
End With
'How to save word document'
With wdApp.ActiveDocument
wdDoc.SaveAs ("C:\Test\"".doc")
.Close

End With

errorHandler:

Set wdApp = Nothing
Set myDoc = Nothing
Set mywdRange = Nothing

End Sub

[stefankemp]

Regard step 3 and 4, maybe

Please Login or Register  to view this content.

If the workbook has already been saved once, then ThisWorkbook.Name will include the extension (for example .xlsm), so you shouldn't add the extension in your filename string.

[JP2511]

Hi Stefan,

Thank you for you reply, although i am trying to save it to a .doc document and to take the name from Cell D1 till D65. You have an idea? SO the result will be that I have 65 documents saved with the value of cell D1 till D65, hope this helps