Dynamic Refreshing of Data connections in Excel using VBA

Question

I am trying to construct a piece of VBA, that will execute automatically everytime a workbook is opened. This piece of code should firstly check whether this workbook has been opened before on that specific day and secondly, if it has not been opened, it should refresh the data connections, close/save the workbook after.

I have developed the following code which I believe should work (It doesn't).

Private Sub Workbook_Open()
  Dim wsSheet As Worksheet
  On Error Resume Next
  Set wsSheet = Sheets("book_helper")
  On Error GoTo 0

  If wsSheet Is Nothing Then
    Sheets.Add.Name = "book_helper"
    ActiveWorkbook.RefreshAll
    Sheets("book_helper").Range("A1").Value = Date
    ActiveWorkbook.Close savechanges:=False
  Else
    If Sheets("book_helper").Range("A1").Value < Date Then
        ActiveWorkbook.RefreshAll
        Sheets("book_helper").Range("A1").Value = Date
        ActiveWorkbook.Close savechanges:=False
    End If
  End If

End Sub

When I open this workbook it simply does not do anything (Doesn't open and the date is not saved). Is there any better way of doing this.

Trum · Accepted Answer · 2015-06-29 08:22:29Z

1

The reason it's not working is because you've got the option savechanges set to false, meaning that you're stuck in a loop where the condition will never be met.

If you change this to true. The workbook will save the first time before closing and will thus open again subsequently without action, because the condition is true.

answered Jun 29, 2015 at 8:22

Trum

6303 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamic Refreshing of Data connections in Excel using VBA

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related