SaveAs File Path

[Armster]

I've been asked to find out how to do the following from one of my directors who plays alot with excel

Basically he has an excel spreadsheet with links to external MS Word Template documents. What he wants is the users to click the link, edit the template and then "save as" it into the relevant job folder (which could be anywhere on the network) He wants the filepath of this new location to be added to the adjacent cell within the orginial excel spreadsheet.

Is this at all possible? I'm not really sure where to start on this, would i require some script within the word template/document that picks up a save as routine and sends it out ?

help or pointers or any ideas would be most welcome

Thanks
Dave

 

[Lewiy]

You can use GetSaveAsFileName to select a location for a save file and store this path as a string. Using SaveAs the string name will then save the document. You then will also have the file path stored as a string variable so you can then put it in which ever cell you like:

Dim FName As String
FName = Application.GetSaveAsFilename(Filefilter:="Microsoft Office Excel Workbook (*.xls), *.xls")
ThisWorkbook.SaveAs (FName)

 

[Armster]

how would this work though?

Would I have to create a macro button within the word document that says "save this file" or something and attach this macro to that? or is there a way to do this behind the scenes sort of speak. or can all this be done within excel itself ?

 

[Lewiy]

Ah, re-reading your original post now it makes a little more sense. I'm not too familiar with the linking between Excel & Word through VBA but I imagine that you could control the whole process through Excel by assigning the template document to a variable within the code and manipulating it like that.

 

[Armster]

not sure that would work as the saveas would be controlled in another program and would not update the variable ? not sure, could do with mroe help

 

[Lewiy]

I would start with something like:

Dim appWD As Word.Application
Set appWD = CreateObject("Word.Application")
With appWD
    .Visible = True
    .Documents.Open ("C:/Doc2.doc")
End With

You will need to make sure you have a reference set to the Microsoft Word Object Library (Tools>References)

I believe you should then be able to control anything in the Word document, just as if you were programming in Word VBA, just prefix it all with “appWD” or keep it within a “With” block.

I’m not too familiar with the methods of Word VBA so that’s as far as I could manage to go with it, but I presume there will be a similar SaveAsFileName method in Word as there is in Excel.