unique error 400 only if internet not accessible

[Guest]

I get error code 400 only if the internet is not accessible, otherwise this
code works fine. I have searched through and no other solutions offered seem
to address my issue so I am posting it here. If I repeat the macro 3 or 4
times, I get another message stating that autorecover has aborted and I can
no longer save my workbook. Here's my code:

'Sub CheckUpdate_Macro()
Dim strCompleteURL As String
Const strDefaultURL As String = "URL;http://"

cboPartTwo = Sheets("UPDATE").Range("L1")
cboPartThree = "update.txt"
strCompleteURL = strDefaultURL & cboPartTwo & cboPartThree 'gives
URL;http://www.mywebsite.com/update.txt
Sheets("UPDATE").Select
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
'

Thank you in advance for your help!

Mike

 

[NickHK]

Mike,
I get a general error 1004 with my net connection unplugged and cannot
reproduce your autorecover problem.
Something else happening also ?

NickHK

 

[Guest]

I left out this code because I didn't think it would matter, but now that I
think about it, I think this is causing the auto recover error, here's the
entire code, sorry I left it out before, I thought it would help.
'
Sub CheckUpdate_Macro()
Dim strCompleteURL As String
Const strDefaultURL As String = "URL;http://"

cboPartTwo = Sheets("UPDATE").Range("L1")
cboPartThree = "update.txt"
strCompleteURL = strDefaultURL & cboPartTwo & cboPartThree
Sheets("UPDATE").Select
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


'Assign today's date to sDate
Dim sDate As Date 'system date
' Dim xDate As Date 'expiration date

sDate = Date
Set yDate = Sheets("MAIN").Range("A2")
Set xDate = Sheets("MAIN").Range("B2")
If (sDate > xDate) Or (sDate < yDate) Then
Sheets("MAIN").Range("Z2") = xDate
Sheets("MAIN").Range("Z5") = "LOCK"
Sheets("Error").Visible = True
Sheets("Error").Select
ThisWorkbook.Save
Else
Sheets("MAIN").Range("Z5") = "UNLOCK"
Sheets("SETUP").Visible = True
Sheets("SETUP").Select
ThisWorkbook.Save
End If

Exit Sub
ErrorMessage:
MsgBox "The update failed, make sure you are " & _
"connected to the internet."
If Sheets("MAIN").Range("Z5") = "LOCK" Then
Sheets("Error").Visible = True
Sheets("Error").Select
ThisWorkbook.Save
Else
Sheets("MAIN").Range("Z5") = "UNLOCK"
Sheets("SETUP").Visible = True
Sheets("SETUP").Select
ThisWorkbook.Save
End If
End Sub
'
Thanks for looking at this

 

[NickHK]

One problem I can see is with your "dates". Appears that you are not using
"Option Explicit".

'OK this is really a date
Dim sDate As Date 'system date
sDate = Date

'yDate & xDate are not declared and hence Variants
Set yDate = Sheets(1).Range("A2")
Set xDate = Sheets(1).Range("B2")
'These variable reference the Range objects, because you use Set, not the
Range object's defualt property .Value

'Whilst this will work, it is somewhat ambiguous.
If (sDate > xDate) Or (sDate < yDate) Then

'Declares them as dates and drop the Set. I would advise against using
default properties and be explicit.
dim xDate as date
xDate = Sheets(1).Range("A2").value

Apart from that I cannot reproduce your Auto recover problems. Any code in
the _Beforesave or _calculate events ?

tried with XL 2002 & XL 2K

NickHK

 

[Guest]

I made the changes as you suggested. I disabled my internet and I got the
400 error still. I haven't been able to duplicate the auto-recover error
since. Any ideas on the 400 error when the internet is disabled?

 

[Guest]

Got it...for now. The error 400 was caused by a locked cell (Z5) in my code.
I unlocked the code, disabled internet, code works great now. I'm going to
try to duplicate the auto-recover error, if I can't, I think I'm set.

Thanks!
Mike

 

[NickHK]

Mike,
I would expect an error if an inet connection is not available. You're
trapping the error, so in that way you code works fine.
don't know why you get #400 and I get #1004, but the both general
application errors.

Not sure the your problem is now.

NickHK

I made the changes as you suggested. I disabled my internet and I got the
400 error still. I haven't been able to duplicate the auto-recover error
since. Any ideas on the 400 error when the internet is disabled?

-------------------------- CUT ----------------