update depending on date

R

Rcar

I have a macro that I only want to update according to
the date but after some testing , as soon as I open the
spreadsheet it updates. Here is my macro, any advice?

Private Sub Update()


If Date = DateSerial(Year(Date), Month(Date) + 1, 0)
Then


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
ActiveWorkbook.Close
End If
'Rest of your stuff.

End Sub

'If you don't want the users to access the new data,
SaveAs temporary


' close workbook and lets the user decide if he wants
to save the data or not.

Sub SaveWorkbookBackup()
Dim awb As Workbook, BackupFileName As String, i As
Integer, OK As Boolean
If TypeName(ActiveWorkbook) = "Nothing" Then Exit Sub
Set awb = ActiveWorkbook
If awb.Path = "" Then
Application.Dialogs(xlDialogSaveAs).Show
Else
BackupFileName = awb.FullName
i = 0
While InStr(i + 1, BackupFileName, ".") > 0
i = InStr(i + 1, BackupFileName, ".")
Wend
If i > 0 Then BackupFileName = Left
(BackupFileName, i - 1)
BackupFileName = BackupFileName & ".bak"
OK = False
On Error GoTo NotAbleToSave
With awb
Application.StatusBar = "Saving this
workbook..."
.Save
Application.StatusBar = "Saving this workbook
backup..."
.SaveCopyAs BackupFileName
OK = True
End With
End If
NotAbleToSave:
Set awb = Nothing
Application.StatusBar = False
If Not OK Then
MsgBox "Backup Copy Not Saved!", vbExclamation,
ThisWorkbook.Name
End If
End Sub
 
T

Tom Ogilvy

I suspect your querytables already exist and they are just getting
refreshed.

Private Sub Update()
If Date = DateSerial(Year(Date), Month(Date) + 1, 0) Then
msgbox "Running code against your wishes"

.. . .
End if
End Sub

will show whether the code is actually being executed or not.
 
R

Rhonda

I didn't get that to work, maybe because it's not in the
right place.

I tried this but failed as well:
What do I put the if around so it will work. Everytime I
tried opening the spreadsheet it continues to refresh so
somethings not right.



Private Sub UpdateAllMeters()


Public Function NthDayOfMonth( _
intMonth As Integer, _
intYear As Integer, _
intOccurrence As Integer, _
intDay As Integer) _
As VariantIf

If intDay = 30 Then

'If Date = DateSerial(Year(Date), Month(Date) + 1, 0)
Then
'MsgBox "Running code against your wishes"


' 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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How record each refresh? 0
Web query - unable to extract data 3
Retrieving consecutive pages in web query What's wrong here? 1
How not to display any pop up message for macro? 4
How do you importing unicode from web 2
help with prompt to pull wed address 1
some exotic error 5
Web Query Results to an Array 0

Top