Prevent user from accidentally running a macro more than once

[Jon Melone]

Hi,
I have workbook ("download.xls") that is over-written every Monday morning by an export from a database. In a second workbook ("report.xls"), I have a macro that copies the new data from the download workbook and appends it to existing data in the report workbook.

Once the new data is added, a pivot table automatically updates along with other formulas in other tabs.

However,
everytime the macro runs, the same data is appended over and over again. How do I evaluate that the new data for the current week has already been added and the macro should be disabled until the next week?

Also,
it is not uncommon that the dowload from the database is incomplete, so after running the macro, a day or two later, I need to delete the data that's been added and re-run the macro against a fresh download. Just need to make sure the macro can be reenabled for this instance.

The only idea I have so far is to use messages to warn the user to double-check the dates before running the macro (compare sysdate to most recent date in the download).

Any thoughts on a more robust approach?

Jon

 

[doubledaffy]

Could you just wipe the data every time and put the newest data in, or do you need to keep some of it?

 

[mikerickson]

This might work.
Create a customDocumentProperty called "LastUpdated"

At the beginning of your routine put code like this

LUpdate = ThisWorkbook.CustomDocumentProperties("LastUpdated")
If MsgBox("Last Update " & LUpdate & vbCr & "Update again?", vbYesNo) = vbNo Then Exit Sub

Rem code to set the value of LastUpdated to Now

Rem updating code

 

[Jon Melone]

Downloading a full new file is a possibility, but it's more of a processing strain to do so. I prefer to keep adding a new week to the report file.

I'll try the customDocumentProperty idea next.

Thanks!
Jon

 

[doubledaffy]

If your data has timestamps, you might try comparing those to only append "new" data.

 

[Jon Melone]

I don't have a datestamp in the data in its present form, but it would be a rather easy add.

I'll see how I like the messaging approach and if I feel brave, work on your suggestion.

Thanks!
Jon

 

[Jon Melone]

I have not used ThisWorkbook.CustomDocumentProperties before. My brief research online hasn't helped my understanding. Can you help explain further? Is "LastUpdated" an existing property? How is it modified?
Thanks!
Jon

 

[mikerickson]

You could use something like this to alert the user how recently the file has been updated.
Note that after 6 days of no update, the default button changes from NO-dont update to Yes-do update.

Sub test()
    Dim lastUpdated As Date
    Dim strPrompt As String, lngButton As Long
    Dim propertyName As String, myProperty As DocumentProperty
    propertyName = "LastUpdated"
    
    On Error GoTo MakeProperty
    Set myProperty = ThisWorkbook.CustomDocumentProperties(propertyName)
    On Error GoTo 0
    
    If IsDate(myProperty.Value) Then
        lastUpdated = CDate(myProperty.Value)
    End If
    
    If lastUpdated = 0 Then
        strPrompt = "never"
    Else
        If Int(lastUpdated) = Int(Now) Then
            strPrompt = "at " & CStr(CDate(lastUpdated - Int(lastUpdated)))
        Else
            strPrompt = "on " & CStr(lastUpdated)
        End If
    End If
    strPrompt = "File updated last updated " & strPrompt & "." & vbCr & "Update now?"
    lngButton = IIf((Now - lastUpdated) < 6, vbDefaultButton2, vbDefaultButton1)

    If MsgBox(strPrompt, vbYesNo + lngButton) = vbNo Then Exit Sub
    
    myProperty.Value = CStr(Now)
   
    Call Updating_Routine
   
Exit Sub

MakeProperty:
    Rem error handling to create custom document property if it doesn't exist
    ThisWorkbook.CustomDocumentProperties.Add Name:=propertyName, _
        LinkToContent:=False, Type:=msoPropertyTypeString, Value:=vbNullString
    Err.Clear
    Resume
End Sub

 

[Jon Melone]

Sorry for my late reply.

Thanks for providing a couple of solutions. Looks like I have it working now.

Jon