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

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

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