SaveAs File Path

Armster

Board Regular
Joined
Jul 26, 2007
Messages
63
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 :devilish:
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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:
Code:
Dim FName As String
FName = Application.GetSaveAsFilename(Filefilter:="Microsoft Office Excel Workbook (*.xls), *.xls")
ThisWorkbook.SaveAs (FName)
 

Armster

Board Regular
Joined
Jul 26, 2007
Messages
63
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

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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

Board Regular
Joined
Jul 26, 2007
Messages
63
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

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
I would start with something like:
Code:
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.
 

Forum statistics

Threads
1,181,657
Messages
5,931,261
Members
436,785
Latest member
KingGideon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top