| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to refresh macro once monthly
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code to refresh macro once monthly
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. Before, after, during the update??? "Rcar" wrote in message ... 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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code to refresh macro once monthly
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 "Henry" wrote in message ... 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. Before, after, during the update??? "Rcar" wrote in message ... 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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code to refresh macro once monthly
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VBA code to refresh Child Pivot Table | Excel Discussion (Misc queries) | |||
| Code To Refresh Query Table | Excel Discussion (Misc queries) | |||
| How do I refresh a macro? | Excel Discussion (Misc queries) | |||
| refresh macro | Excel Worksheet Functions | |||
| Sheet protection code conflicts with Pivot Table "auto refresh" | Excel Discussion (Misc queries) | |||
Hybrid Mode
