AutoSave an excel file on open

[Felix18807]

I generate routine excel reports (in access) for a user and then email him the result. The problem he is encountering is that he frequently launches the file directly from outlook and forgets to save it locally. Any work he does on the file is subsequently lost.

To help with this issue I can think of a few solutions but I think only two seem really feasable.

1) Save the file in a shared drive and post him the link instead of the file.
2) Insert a macro into the created excel file that saves the file to his personal file when he launches it.

Solution 1 is straight forward and achievable but I would not no how to approach 2 as it would need the ability to insert code into an excel file via access.

My question is how hard is it going to be to acheive solution 2?
I made this bit of code but I've no idea how to insert it into the excel file automatically.

Function FileOrDirExists(PathName As String) As Boolean 
On error resume next
    Dim iTemp As Integer 

    iTemp = GetAttr(PathName) 

    Select Case Err.Number 
    Case Is = 0 
        FileOrDirExists = True 
    Case Else 
        FileOrDirExists = False 
    End Select 
     
End Function

Private Sub Workbook_Open()

Dim myDir as string
myDir= "U:\" & thisworkbook.name

if FileOrDirExists(mydir) then
Msgbox "This file exists. Please save as a different mane"
else
ActiveWorkbook.SaveAs Filename:=mydir , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

End Sub

 

[PHV]

Why not simply send a template (.xlt) ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Felix18807]

I'm having some issues recreating the issue so I don't know if an xlt would remedy the problem. When I launch any excel file from outlook and attempt to save it it states that the file is read only and forces me to choose a save location (Office 2010). I'm going to assume the default behavior is different for earlier versions.

So I'm not sure - would launching the template file from Outlook 2007 then subsequently clicking save force the user to save the file elsewhere? I will try this and see if it fixes it for him

The original request:

"Around 10 times now I have opened up spreadsheets that you have sent back matched and I have spent nearly a day’s work doing the manually checks and getting them ready to import then forgotten that every time I am clicking save it doesn’t save it to my U drive it is saving it to the Outlook temporary folder which then disappears the next day and I have to start from scratch again.

So is there any way you can auto insert a macro into the spreadsheet so that when I open it, it prompts me to save it straight away?"

 

[oharab]

So that's 10 days work lost! You'd've thought after doing it twice, three times max they'd remember what to do!

Rather than automatically saving the work for them, you could put some code in the Workbook_BeforeSave event that checks to see where this file is being saved & if it's in the temp folder then cancel the save and display a rather large msgbox informing the user of their error.

The biggest problem with any VBA based approach is the VBA needs to be run. If the user turns off macros or it's a 2007 document in a non-trusted area, even the fanciest code won't be run. User training is key.

B.

----------------------------------------------
Ben O'Hara

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.