Saving the Workbook as a temporary file that I can later recall

[Excel Jr]

Hello,

I've become quite frustrated. I've created a very complex dynamic spreadsheet/form (not a vba form) with circular references, data validation/drop downs, conditional formatting based on the dependent circular references, etc. I've been trying to make a clear/reset button for a month now...

I can't use any of the Clear methods as they all mess up at least one thing on my sheet. Everything is extremely dependent as it's meant for non-excel users. I've spent a month building this, I know there are better ways of making forms, but this is what my boss requested and I can't change the format (it's got to appear very simple, "idiot proof" was the term used

I found that by changing the file access with the following code does just the trick!

Sub Reopen()
    ThisWorkbook.Saved = True
    ThisWorkbook.ChangeFileAccess xlReadOnly, , False
    Application.Wait Now + TimeValue("00:00:01")
    ThisWorkbook.ChangeFileAccess xlReadWrite, , True
End Sub

However, this form will commonly be emailed to users and opened from their mailbox and this code requires that the file be saved on the user's computer for it to work. I could ask that the users place it on their desktop 1st, but my goal is to make it as easy as possible. All of my other macros work fine from the mailbox, but this one is giving me a headache.

I've previously used code to save a workbook temporarily and then send it via Outlook with the following code:

Sub SaveSendDelete_CurrentWorkBook()


    Dim OlApp As Object
    Dim NewMail As Object
    Dim TempFilePath As String
    Dim FileExt As String
    Dim TempFileName As String
    Dim FileFullPath As String
    Dim MyWb As Workbook


    Set MyWb = ThisWorkbook


    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With


        TempFilePath = Environ$("temp") & "\"
        FileExt = ".xlsm"
        TempFileName = ThisWorkbook.Sheets("PAF").Range("B10").Value & " PAF " & Format(Now, "mm-dd-yyyy")
        FileFullPath = TempFilePath & TempFileName & FileExt
        MyWb.SaveCopyAs FileFullPath




    Set OlApp = CreateObject("Outlook.Application")
    Set NewMail = OlApp.CreateItem(0)


        On Error Resume Next
            With NewMail
                .To = "MichaelJackson@Creeping.com"
                .CC = "JohnSnow@TheWall.com"
                .Subject = "New Form Has Arrived"
                .Body = "Attached is a form."
                .Attachments.Add FileFullPath
                .Display
            End With
        On Error GoTo 0


    Kill FileFullPath
    Set NewMail = Nothing
    Set OlApp = Nothing


  
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With


End Sub

I'm trying to figure how to alter the code to save the file in the temp folder when the workbook is opened. So that a refresh/reload button will either:
1. Change the file access from read to read/write (automatically reverting the file back to it's last save)
2. Or preferably calling back the temp file (with delay) and closing the current workbook without saving

Private Sub Workbook_SaveTempOnOpen() 
    Dim TempFilePath As String
    Dim FileExt As String
    Dim TempFileName As String
    Dim FileFullPath As String

    If Dir(Environ("temp") & "\[COLOR=#ff0000]activework?[/COLOR]") <> "" Then Kill Environ("temp") & "\[COLOR=#ff0000]activeworkbook?[/COLOR]"


        TempFilePath = Environ$("temp") & "\"
        FileExt = ".xlsm"
        TempFileName = [COLOR=#ff0000]"ActiveWorkbook?"[/COLOR]
        FileFullPath = TempFilePath & TempFileName & FileExt
        ThisWorkbook.SaveAs FileFullPath
End Sub

I'm not sure how to reference the active workbook here so that it will work no matter what the file is named and I'm honestly not even sure if that's the real problem. I've just started delving into VBA and would really appreciate any advice.

Thanks!

 

[spiralrain]

try using:

ActiveWorkbook.Name

If that doesn't work, try:
Dim ThisBook as string
ThisBook = Activeworkbook.name

Then using ThisBook in each of those spots.

 

[Excel Jr]

This Works! Thanks you!

 

[Excel Jr]

Now my only issue is that if the file is already in the temp folder when opening, then it asks if i want to replace it. Can this be solved by turning the event handler off or do I need an if statement that checks to see if it already exists and then kill it if true?

Private Sub Workbook_Open()


    Dim TempFilePath As String
    Dim FileExt As String
    Dim TempFileName As String
    Dim FileFullPath As String
    Dim ThisBook As String
    
    ThisBook = ActiveWorkbook.Name


        TempFilePath = Environ$("temp") & "\"
        TempFileName = ThisBook
        FileFullPath = TempFilePath & TempFileName & FileExt
        ThisWorkbook.SaveAs FileFullPath


End Sub

Not sure how to code it. Thanks for all the help!

 

[spiralrain]

I use:

Application.DisplayAlerts = False

Try putting it in between these two lines in your code:

FileFullPath = TempFilePath & TempFileName & FileExt
ThisWorkbook.SaveAs FileFullPath