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
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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] . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to refresh macro once monthly
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" wrote in message ... 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] . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to refresh macro once monthly
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" wrote in message ... 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] . . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to refresh macro once monthly
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" wrote in message ... 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" wrote in message ... 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] . . |
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) |