Automating Refreshing of Password Protected Excel 2010 Worksheets

[jwagner]

Windows XP
Excel 2010


I have eight password protected, multi-tabbed, Excel 2010 worksheets which I would like to automatically refresh each morning before I come into work. How can I go about completing this? I am a beginner with VBA.


Thanks in advance for the help!

 

[Smitty]

Welcome to the Board!

What do you need to refresh?

You can use Windows Scheduler to open the workbook each day, and then use the Open even to trigger the refresh action.

You can get around the password part like this:

Sub Foo()
  Dim ws as Worksheet
    For each ws in ActiveWorkbook.Worksheets
      ws.Unprotect "Password here"
        '  Refresh
      ws.Protect "Password here"
    Next ws
End Sub

HTH,

 

[jwagner]

I'm refreshing revenue reports that are used by others in the company. They're saved on a share drive that I have access to. The files are quite large, they take roughly half an hour to refresh each, which is why I wanted them to open and refresh early in the morning before anyone started using them. Can I put in a code to have them refresh once but not everytime someone opens the file?

Also, where should I go to learn more about VBA and Excel in general? I'm really interested in learning more.

Thanks!

 

[Smitty]

You can put a counter in an unused cell to act as a test. Here's an example:

        '   Check if the WebData routine has already been run once today
            If ws.Range("WebDate") <= Date And ws.Range("WebClicks") = 0 Then
            '   Set the WebClicks counter so the procedure can't be run again
                ws.Range("WebClicks") = 1
                '  Refresh code here

                Else
                    MsgBox "WebData has already been updated today", vbInformation + vbOKCancel, "No Update Necessary"
                    Exit Sub
                End If

And then you test/reset it in the Workbook_Open event:

Private Sub Workbook_Open()
'   Reset the WebData click counter
'   The WebData clicks routine is run in mod_WebData
    With wsWebData
        If .Range("WebDate") < Date Then
            .Range("WebDate").Value = Date
            .Range("WebClicks").Value = 0
        End If
    End With
    
End Sub

As for learning VBA, there are plenty of free tutorials you'll find on the internet, and there are lots of good books. John Walkenbach is my favorite, but Bill Jelen (aka Mr. Excel) has plenty of great books that you can get through the site.

The best way to learn VBA though is by and far using the macro recorder. Just record processes with which you're intimately familiar, then read the code. Most of it should be easily translatable from what you did to how the code captures it. Note that the recorder throws in a lot of unnecessary stuff, but you learn to eliminate that pretty quickly.