Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query Refresh - Turning it off completely
Hello All,
I have recently had to try and fix a spreadsheet made by a ex- coworker. The issue I am having is that he created links to external data that I cannot find. Every time I open the workbook is asks me if I want to disable or enable the automatic refresh. Is there a way that I can stop the box from coming up (removing links?) and turning off the automatic refresh? Basically once the data is imported into excel I do NOT want it to refresh if the external files are updated. Here is the import code: 'import .tab file SourceFile = "TEXT;" & LoadsResultsPath & ResultsRootName & ".tab" Dest = 1 + 14 * counter Sheets(TabInputPage).Select With ActiveSheet.QueryTables.Add(Connection:=SourceFile , Destination:=Cells(2, Dest)) .Name = ".tab" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = True .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = False .AdjustColumnWidth = False .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = xlWindows .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = True .TextFileTabDelimiter = False .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = True .TextFileColumnDataTypes = Array(1, 1, 1, 1) .Refresh BackgroundQuery:=False End With Is there anything there? I've also tried different settings in the option menu, with no luck. Any help would be greatly appreciated. Jack |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query Refresh - Turning it off completely
On 8 Mar 2007 12:41:08 -0800, "JohnJack"
wrote: Hello All, I have recently had to try and fix a spreadsheet made by a ex- coworker. The issue I am having is that he created links to external data that I cannot find. Every time I open the workbook is asks me if I want to disable or enable the automatic refresh. Is there a way that I can stop the box from coming up (removing links?) and turning off the automatic refresh? Basically once the data is imported into excel I do NOT want it to refresh if the external files are updated. <snip Is there anything there? I've also tried different settings in the option menu, with no luck. Jack: On the line right before End With, but this line ..Delete Make sure you keep the BackgroundQuery argument as False. Once the data is imported, this line will delete the QueryTable object. The data will still be in the worksheet, just the link between it and the external data will be severed. -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query Refresh - Turning it off completely
Try this:
Click in a field that has the data from the query. Go to the Data Menu, then to Import External Data, then select Date Range Properties Uncheck which options you want to disable under Refresh Controls. HTH "JohnJack" wrote: Hello All, I have recently had to try and fix a spreadsheet made by a ex- coworker. The issue I am having is that he created links to external data that I cannot find. Every time I open the workbook is asks me if I want to disable or enable the automatic refresh. Is there a way that I can stop the box from coming up (removing links?) and turning off the automatic refresh? Basically once the data is imported into excel I do NOT want it to refresh if the external files are updated. Here is the import code: 'import .tab file SourceFile = "TEXT;" & LoadsResultsPath & ResultsRootName & ".tab" Dest = 1 + 14 * counter Sheets(TabInputPage).Select With ActiveSheet.QueryTables.Add(Connection:=SourceFile , Destination:=Cells(2, Dest)) .Name = ".tab" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = True .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = False .AdjustColumnWidth = False .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = xlWindows .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = True .TextFileTabDelimiter = False .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = True .TextFileColumnDataTypes = Array(1, 1, 1, 1) .Refresh BackgroundQuery:=False End With Is there anything there? I've also tried different settings in the option menu, with no luck. Any help would be greatly appreciated. Jack |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query Refresh-Enable Automatic Refresh Dialogue Box | Excel Discussion (Misc queries) | |||
Timing of automatic query refresh and macro pivot table refresh | Excel Programming | |||
Turning Calcuation Off Completely | Excel Programming | |||
Turning off Screen Refresh | Excel Programming | |||
Excel does not close from VB!! (when i refresh Refresh query with BackgroundQuery:=False) | Excel Programming |