Excel VBA web query problem since new cable connection (text/numbers format problem)
 

I recently moved house from an adsl only area to a cabled area. I took the cable. Everything is running good... except..

Ever since the house move there has been a constant problem with running some VBA code in Excel that has been running OK for years in adsl...

..downloading stock market data every 15 minutes from Yahoo via code that runs itself from 8am to 9pm without any user interaction..

.OnTime TimeValue("09:00:00"), "RunMacro"

etc, etc.

You can see from the .png that sometimes the data is formatted OK as the required 'number' but sometimes is randomly formatting itself as 'text'. The 'text', of course, is messing up the charts that result from the data.

Not to worry, I found the perfect piece of code to instantly convert all highlighted 'text' numbers to 'numbers', but it's really bugging me.

Any ideas as to what is causing it? tia.

PS: If I go to the actual sheet that has the Query and refresh the query when it is in 'text' mode it usually reverts right back to 'numbers' immediately.

Re: Excel VBA web query problem since new cable connection (text/numbers format probl
 

This is the code I have for this, it is not mine but may help you.

Sub GetData()

Dim QuerySheet As Worksheet
Dim DataSheet As Worksheet
Dim qurl As String
Dim i As Integer, iMax As Integer

Clear

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

Set DataSheet = ActiveSheet

For iMax = 0 To 1000 Step 200

i = 7 + iMax
If Cells(i, 1) = "" Then
GoTo stopHere
End If

qurl = "http://download.finance.yahoo.com/d/quotes.csv?s=" + Cells(i, 1)
i = i + 1
While Cells(i, 1) <> "" And i < iMax + 207
qurl = qurl + "+" + Cells(i, 1)
i = i + 1
Wend
qurl = qurl + "&f=" + Range("C2")
Range("c1") = qurl

QueryQuote:
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("N7"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With

Range("N7:N207").Select
Selection.TextToColumns Destination:=Range("N7"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1))

Range("N7:W207").Select
Selection.Copy
Cells(7 + iMax, 3).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

' Range("N7:W207").Select
' Selection.ClearContents
Next iMax

ClearNames

'turn calculation back on
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
' Range("C7:H2000").Select
' Selection.Sort Key1:=Range("C8"), Order1:=xlAscending, Header:=xlGuess, _
' OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
' Columns("C:C").ColumnWidth = 25.43
' Range("h2").Select



stopHere:
ClearNames
Clear2

End Sub
Sub Clear()

Range("C7:L1200").Select
Selection.ClearContents


End Sub
Sub Clear2()

Columns("N:AA").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1").Select

End Sub


Sub doALL()

Sheets("Yahoo1").Select
GetData

Sheets("Yahoo2").Select
GetData

Sheets("Yahoo3").Select
GetData

End Sub
Sub ClearNames()

With ThisWorkbook
For Each nQuery In Names
If IsNumeric(Right(nQuery.Name, 1)) Then
nQuery.Delete
End If
Next nQuery
End With

End Sub

Re: Excel VBA web query problem since new cable connection (text/numbers format probl
 

Thanks for the reply RD, but I'm really not thinking it's a code problem. I've had the code running since early nineteen ninety something with ntl cable, virgin adsl, plusnet adsl..

I'm thinking it's a cable setup thing somewhere. I uninstalled the latest Microsoft Excel update yesterday - made no difference at all.

I'm waiting to close down for the day tonite so I can delete the disconnected ppp adapter from the last adsl connection - I just noticed it.. maybe that's it.

i'll take a peek at your code over some cocoa later lol