Code to refresh macro once monthly

  • Thread starter Thread starter Rcar
  • Start date Start date
R

Rcar

I have a macro with several Web Queries that I use to
update data in a spreadsheet. I want the macro to only
refresh the web queries once a month(end of month) so it
can then be captured for data analysis. If someone opens
the spreadsheet I don't want the figures to change. What
code do I use for that?
 
Rcar,

Private Sub Update()
'Is this the last day of the month (actually looks for the 0th day of the
next month)?
If Date = DateSerial(Year(Date),Month(Date)+1,0) then

'Update your data.
'If you don't want the users to access the new data, SaveAs temporary
filename and
' close workbook.
End If
'Rest of your stuff.

End Sub
If someone opens the spreadsheet I don't want the figures to change.
Click to expand...
Before, after, during the update???
 
I just need to make sure that for example if someone opens the file
after it has been updated that the data doesn't change. This is the
macro I have been using:

So, are you saying to place your routine around mine with the if?
Sorry for the questions, I haven't worked with this before and I
really want to get this resolved. If you could show me I would be
grateful.

Thanks

Rhonda


Sub UpdateAllMeters()
'
' UpdateAllMeters Macro
' Macro recorded 11/09/2003 by rhocarit
'
' Keyboard Shortcut: Ctrl+x
'
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 = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
Range("D1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\Happy_Valley_12944.iqy" _
, Destination:=Range("D1"))
.Name = "Happy_Valley_12944"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
Range("G1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\Holyrood_13048.iqy", _
Destination:=Range("G1"))
.Name = "Holyrood_13048"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
Range("J1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\Port_Saunders_12247.iqy" _
, Destination:=Range("J1"))
.Name = "Port_Saunders_12247"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll ToRight:=4
Range("M1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft Office\Office\Queries\St.
Anthony_12946.iqy" _
, Destination:=Range("M1"))
.Name = "St. Anthony_12946"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
Range("P1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft Office\Office\Queries\St.
Johns_11770.iqy", _
Destination:=Range("P1"))
.Name = "St. Johns_11770"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll ToRight:=8
Range("S1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft Office\Office\Queries\St.
Johns_11772.iqy", _
Destination:=Range("S1"))
.Name = "St. Johns_11772"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
Range("V1").Select
ActiveWindow.SmallScroll ToRight:=3
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft Office\Office\Queries\St.
Johns_12308.iqy", _
Destination:=Range("V1"))
.Name = "St. Johns_12308"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
Range("Y1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft Office\Office\Queries\St.
Johns_12379.iqy", _
Destination:=Range("Y1"))
.Name = "St. Johns_12379"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll ToRight:=5
Range("AB1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft Office\Office\Queries\St.
Johns_12380.iqy", _
Destination:=Range("AB1"))
.Name = "St. Johns_12380"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
Range("AE1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft Office\Office\Queries\St.
Johns_12733.iqy", _
Destination:=Range("AE1"))
.Name = "St. Johns_12733"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll ToRight:=7
Range("AH1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft Office\Office\Queries\St.
Johns_12734.iqy", _
Destination:=Range("AH1"))
.Name = "St. Johns_12734"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
Range("AK1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft Office\Office\Queries\St.
Johns_12735.iqy", _
Destination:=Range("AK1"))
.Name = "St. Johns_12735"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll ToRight:=5
Range("AN1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\Whitbourne.iqy", _
Destination:=Range("AN1"))
.Name = "Whitbourne"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
End Sub
 
could do with a bit of editing couldn't it - or do all the arguments
have to be left in?

J
 
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 said:
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!!!
Click to expand...
[Very big SNIP]


.
Click to expand...
 
Rhonda,

Glad that it now works for you.
Sorry, I've no idea what msft stocks are and what the table should look like
so, I can't help you there.
I assume that they're some kind of US stock market listing which we don't
get here in the UK.

ATB
Henry


Rhonda said:
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 said:
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!!!
Click to expand...
[Very big SNIP]


.
Click to expand...
Click to expand...
 
Henry, I hate to bother you again but I tried applying
the If block to my macro here at work and it ignores the
if altogether. I have the code below. I tried just
creating web queries and putting the If block around that
and that works but at the end of month instead of just
updating the figures, it inserts a new table next to the
other one, and another if I run it again. My old macro
just simply updates the numbers which is why I have been
beating my brains out trying to get that one to work. Any
ideas:

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."


' UpdateAllMeters Macro
' Macro recorded 11/09/2003 by rhocarit
'
' Keyboard Shortcut: Ctrl+x
'
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 = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
Range("D1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\Happy_Valley_12944.iqy" _
, Destination:=Range("D1"))
.Name = "Happy_Valley_12944"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
Range("G1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\Holyrood_13048.iqy", _
Destination:=Range("G1"))
.Name = "Holyrood_13048"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
Range("J1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\Port_Saunders_12247.iqy" _
, Destination:=Range("J1"))
.Name = "Port_Saunders_12247"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll ToRight:=4
Range("M1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\St. Anthony_12946.iqy" _
, Destination:=Range("M1"))
.Name = "St. Anthony_12946"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
Range("P1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\St. Johns_11770.iqy", _
Destination:=Range("P1"))
.Name = "St. Johns_11770"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll ToRight:=8
Range("S1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\St. Johns_11772.iqy", _
Destination:=Range("S1"))
.Name = "St. Johns_11772"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
Range("V1").Select
ActiveWindow.SmallScroll ToRight:=3
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\St. Johns_12308.iqy", _
Destination:=Range("V1"))
.Name = "St. Johns_12308"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
Range("Y1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\St. Johns_12379.iqy", _
Destination:=Range("Y1"))
.Name = "St. Johns_12379"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll ToRight:=5
Range("AB1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\St. Johns_12380.iqy", _
Destination:=Range("AB1"))
.Name = "St. Johns_12380"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
Range("AE1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\St. Johns_12733.iqy", _
Destination:=Range("AE1"))
.Name = "St. Johns_12733"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll ToRight:=7
Range("AH1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\St. Johns_12734.iqy", _
Destination:=Range("AH1"))
.Name = "St. Johns_12734"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
Range("AK1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\St. Johns_12735.iqy", _
Destination:=Range("AK1"))
.Name = "St. Johns_12735"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll ToRight:=5
Range("AN1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\Whitbourne.iqy", _
Destination:=Range("AN1"))
.Name = "Whitbourne"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

Else
MsgBox "This is NOT the last day of the Month. Your
data has NOT been updated."

End If

End Sub













-----Original Message-----
Rhonda,

Glad that it now works for you.
Sorry, I've no idea what msft stocks are and what the table should look like
so, I can't help you there.
I assume that they're some kind of US stock market listing which we don't
get here in the UK.

ATB
Henry


Rhonda said:
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
Click to expand...
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
Click to expand...
data
will be updated."
With ActiveSheet.QueryTables.Add(Connection:= _
etc.

End With
Else
MsgBox "This is NOT the last day of the Month.
Click to expand...
Your
data has NOT been
updated."
End If
End Sub

The keyboard shortcut you've assigned this to (Ctrl +
Click to expand...
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


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) +
Click to expand...
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]


.
Click to expand...
Click to expand...


.
Click to expand...
 
Rhonda,
Works for me (WinXP, XL XP)

When you say it ' ignores the If statement' does it get as far as putting up
the message saying it's NOT the last day of the month?

If so, try changing the Windows date to 30th September.
Double click the time in the taskbar, change the date to the 30th and apply.
Close the Date/time window and try running your macro again.

Still not working?
Try stepping through the macro (F8 for each step) in the VBA editor window
Once the yellow highlight has got to the If line, hover the mouse over the
word Date.
A little box will appear showing you what date XL thinks it is.
This should be 30/9/03 or 9/30/03 depending on your date settings.

A thought.......
Are you on a network at work?
Does your network allow you to change the system date, or (more likely) is
this reserved for the network administrator?
If you can't change it, you'll have to wait till the last day of the month
to see it working!!!!!!


HTH
Henry




Rhonda said:
Henry, I hate to bother you again but I tried applying
the If block to my macro here at work and it ignores the
if altogether. I have the code below. I tried just
creating web queries and putting the If block around that
and that works but at the end of month instead of just
updating the figures, it inserts a new table next to the
other one, and another if I run it again. My old macro
just simply updates the numbers which is why I have been
beating my brains out trying to get that one to work. Any
ideas:

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."


' UpdateAllMeters Macro
' Macro recorded 11/09/2003 by rhocarit
'
' Keyboard Shortcut: Ctrl+x
'
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 = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
Range("D1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\Happy_Valley_12944.iqy" _
, Destination:=Range("D1"))
.Name = "Happy_Valley_12944"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
Range("G1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\Holyrood_13048.iqy", _
Destination:=Range("G1"))
.Name = "Holyrood_13048"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
Range("J1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\Port_Saunders_12247.iqy" _
, Destination:=Range("J1"))
.Name = "Port_Saunders_12247"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll ToRight:=4
Range("M1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\St. Anthony_12946.iqy" _
, Destination:=Range("M1"))
.Name = "St. Anthony_12946"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
Range("P1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\St. Johns_11770.iqy", _
Destination:=Range("P1"))
.Name = "St. Johns_11770"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll ToRight:=8
Range("S1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\St. Johns_11772.iqy", _
Destination:=Range("S1"))
.Name = "St. Johns_11772"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
Range("V1").Select
ActiveWindow.SmallScroll ToRight:=3
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\St. Johns_12308.iqy", _
Destination:=Range("V1"))
.Name = "St. Johns_12308"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
Range("Y1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\St. Johns_12379.iqy", _
Destination:=Range("Y1"))
.Name = "St. Johns_12379"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll ToRight:=5
Range("AB1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\St. Johns_12380.iqy", _
Destination:=Range("AB1"))
.Name = "St. Johns_12380"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
Range("AE1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\St. Johns_12733.iqy", _
Destination:=Range("AE1"))
.Name = "St. Johns_12733"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll ToRight:=7
Range("AH1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\St. Johns_12734.iqy", _
Destination:=Range("AH1"))
.Name = "St. Johns_12734"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
Range("AK1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\St. Johns_12735.iqy", _
Destination:=Range("AK1"))
.Name = "St. Johns_12735"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll ToRight:=5
Range("AN1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft
Office\Office\Queries\Whitbourne.iqy", _
Destination:=Range("AN1"))
.Name = "Whitbourne"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

Else
MsgBox "This is NOT the last day of the Month. Your
data has NOT been updated."

End If

End Sub













-----Original Message-----
Rhonda,

Glad that it now works for you.
Sorry, I've no idea what msft stocks are and what the table should look like
so, I can't help you there.
I assume that they're some kind of US stock market listing which we don't
get here in the UK.

ATB
Henry


Rhonda said:
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


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]


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top