Code to refresh macro once monthly
Thanks Henry for the info, it works! I have one last
thing to ask:
I am at home right now so I just created a query on a
table from msft stocks. Then In the ThisWorkbook module
for the Open procedure I put the code you mentioned. The
only problem I have, which isn't much, is that when it
performs the update it copies the table into the next 2
columns instead of just refreshing the data in the table
already present how can it modified to maintain the same
range?
Thanks,
Rhonda
-----Original Message-----
Rhonda,
Runs OK for me (WinXP, XL XP).
The Then MUST be on the same line as the If statement.
Don't forget the End If statement at the end, before the
End Sub statement.
Private Sub UpdateAllMeters()
If Date = DateSerial(Year(Date), Month(Date) + 1, 0)
Then
MsgBox "This is the last day of the Month. Your data
will be updated."
With ActiveSheet.QueryTables.Add(Connection:= _
etc.
End With
Else
MsgBox "This is NOT the last day of the Month. Your
data has NOT been
updated."
End If
End Sub
The keyboard shortcut you've assigned this to (Ctrl + X)
is also the windows
keyboard shortcut for CUT.
I find this a very useful shortcut, so I wouldn't want
to override it (just
my preference).
I would use Ctrl + 1 or Ctrl + 2 instead.
This won't affect your macro, but your users may
complain if they use Ctrl
+X for cut.
HTH
Henry
"Rhonda" wrote in message
...
Yeah, it might. I'm fairly new to this so any
suggestions
or changes are welcomed by anyone. I tried using the
recommended If statement in this post but when I run
the
macro it just ignores it and updates the figures
anyway.
Maybe I have it in the wrong place:
Private Sub UpdateAllMeters()
If Date = DateSerial(Year(Date), Month(Date) + 1,
0)
Then
MsgBox "Running code against your wishes"
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\Bishops_Falls_12089.iqy" _
, Destination:=Range("A1"))
.Name = "Bishops_Falls_12089"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCel
Help!!!
[Very big SNIP]
.
|