PDA

View Full Version : Excel link in webpage works, but saves in temp file instead of original location



rnpjen
11-23-2007, 08:18 PM
I am truly a beginner here, so I hope someone can help me.

I have created an internal website for my company that includes links to all of our quality documents (combination of Word, Excel & Acrobat files). Several of the Excel documents are active logs that all employees enter information into.

When a user clicks on a link, the document opens in Excel, data is entered, then the user attempts to close the file. The user is prompted to save the file (they have been instructed to always choose yes unless they don't want to save their changes).

The problem is when the file is opened from the website, it opens as a temp file so when the file is saved, it saves in a temp folder instead of the original location. My users are beginners and have lots of trouble having to navigate to save files, so having them save before they close would be difficult.

From what I have read here on this site, it seems that I can have a script run before closing that will save the file in a specified location and as a specified name.

I have tried using some of the code that has been posted in other threads here but can't seem to get the correct result, probably because I don't know what I'm doing.

I hope someone can help and that I have given enough information.

Thanks.

tpoynton
11-23-2007, 08:32 PM
Welcome! I mostly lurk here, and learn from answers to posts like yours. I dont have a specific answer, but you might be able to get a good start on it by using the macro recorder. You'll then likely put the code you need into a 'before close' event in ThisWorkbook.

I wonder if you will have problems if two people open the file?

Anyway, try the recorder and post questions here. I'm sure you'll get good advice.

rnpjen
11-23-2007, 09:26 PM
Thank you for your suggestion. I recorded a macro. Here is the code:

Sub SaveAs()
'
' SaveAs Macro
' Macro recorded 11/23/2007 by Jennifer Aston
'

'
Application.CommandBars("Visual Basic").Visible = True
ChDir "R:\Sites\RNP-INTRANET\library\Active Files\QF-22"
ActiveWorkbook.SaveAs Filename:= _
"R:\Sites\RNP-INTRANET\library\Active Files\QF-22\QF-22 Customer Furnished Document-Parts Log - 2007.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

I don't know where I would put this in "You'll then likely put the code you need into a 'before close' event in ThisWorkbook".

Could you help me with that?

Thanks.

tpoynton
11-24-2007, 08:13 AM
I'm hoping someone more knowledgeable than I chimes in on this about the issue of more than one person opening the file at a time...

to put into ThisWorkbook, open the visual basic editor and expand the items under your file's name. Then double-click on ThisWorkbook to open a blank place for your code. Then click on the (General) dropdown and select Workbook, and then click on the other dropdown and select BeforeClose. Paste your recorded code into there, save, open via a link and see what happens.

lucas
11-24-2007, 09:57 AM
Where is the thisworkbook module? Click here (http://slucas.virtualave.net/Wink/ThisWorkbookModule.htm)

lucas
11-24-2007, 10:01 AM
Link to Jake's function to check to see if a workbook is open kb entry:
http://vbaexpress.com/kb/getarticle.php?kb_id=186

rnpjen
12-02-2007, 07:52 PM
Thank you all for your help with this. I was able to make the code work and it did just what I needed it to do. Just one more question . . . When the user opens the file, the visual basic toolbar shows up. After you close the toolbar and try to close the file, it comes again. Like I said before, my users are beginners and don't want them to mess with anything.Is there a way to set it so that it doesn't come up?