external data works in 2003 not in 2000
 

I have this code, works great in 2003, I open the file in 2000 and the macro
does not access txt file on website, it goes right to the on error message.
If I manually refresh data Data | Refresh Data it will perform the update.
Why does it work ok in 2003 and not 2000?
'
Sub CheckUpdate_Macro()
Dim strCompleteURL As String

strCompleteURL = "URL;http://www.mywebsite.com/update.txt" 'I had a 3 part
url, I thought may have been problem so I combined
Sheets("UPDATE").Select
Sheets("UPDATE").Unprotect "1234"
On Error GoTo ErrorMessage
With ActiveSheet.QueryTables.Add(Connection:= _
strCompleteURL, Destination:=Range("A1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlPasteDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With


Exit Sub
ErrorMessage:
MsgBox "The update failed, make sure you are " & _
"connected to the internet."

End Sub
'
Thanks!
Mike

external data works in 2003 not in 2000
 

Mike,
That value of .RefreshStyle = xlPasteDeleteCells is not valid in version
early than XL2003:

<From 2002 Help
XlCellInsertionMode can be one of these XlCellInsertionMode constants.
xlInsertDeleteCells. Partial rows are inserted or deleted to match the exact
number of rows required for the new recordset.
xlOverwriteCells. No new cells or rows are added to the worksheet. Data in
surrounding cells is overwritten to accommodate any overflow.
xlInsertEntireRows. Entire rows are inserted, if necessary, to accommodate
any overflow. No cells or rows are deleted from the worksheet.
<From Help

Also, there is no .WebDisableRedirections property to a QueryTable in
XL2000.

Unless you really need these extra setting, leave them out of the code, so
earlier versions function and XL2003 will use the default values.

NickHK

"mikeolson" wrote in message
...
I have this code, works great in 2003, I open the file in 2000 and the
macro
does not access txt file on website, it goes right to the on error
message.
If I manually refresh data Data | Refresh Data it will perform the update.
Why does it work ok in 2003 and not 2000?
'
Sub CheckUpdate_Macro()
Dim strCompleteURL As String

strCompleteURL = "URL;http://www.mywebsite.com/update.txt" 'I had a 3 part
url, I thought may have been problem so I combined
Sheets("UPDATE").Select
Sheets("UPDATE").Unprotect "1234"
On Error GoTo ErrorMessage
With ActiveSheet.QueryTables.Add(Connection:= _
strCompleteURL, Destination:=Range("A1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlPasteDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With


Exit Sub
ErrorMessage:
MsgBox "The update failed, make sure you are " & _
"connected to the internet."

End Sub
'
Thanks!
Mike

external data works in 2003 not in 2000
 

Why are you recreating the querytable. If it refreshes manually, then that
indicates it already exists. Just


Sheets("UPDATE").Select
Sheets("UPDATE").Unprotect "1234"
Activesheet.Querytables(1).Refresh BackgroundQuery:=False

should be all you need.

--
Regards,
Tom Ogilvy


"mikeolson" wrote:

I have this code, works great in 2003, I open the file in 2000 and the macro
does not access txt file on website, it goes right to the on error message.
If I manually refresh data Data | Refresh Data it will perform the update.
Why does it work ok in 2003 and not 2000?
'
Sub CheckUpdate_Macro()
Dim strCompleteURL As String

strCompleteURL = "URL;http://www.mywebsite.com/update.txt" 'I had a 3 part
url, I thought may have been problem so I combined
Sheets("UPDATE").Select
Sheets("UPDATE").Unprotect "1234"
On Error GoTo ErrorMessage
With ActiveSheet.QueryTables.Add(Connection:= _
strCompleteURL, Destination:=Range("A1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlPasteDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With


Exit Sub
ErrorMessage:
MsgBox "The update failed, make sure you are " & _
"connected to the internet."

End Sub
'
Thanks!
Mike