Web query messing up existing formatting despite being told not to...

[bluto32]

I am writing a macro to fetch some "frequency & symbol rates" data from a website.

The data typically looks like this: 22000 5/6

When the query is performed, although the data looks fine on the screen, it has been converted into a fraction. Clicking on the cell reveals it's true nature: 22000.833333333333...

This is a problem, as I would like to read off the last 3 characters as a string "5/6" and use them somewhere else.

My code is below, and was generated with the macro recorder. I have tried formatting the entire worksheet as "Text" before running the code, but this doesn't help. Clicking cell I36 shows it has been changed from "Text" format to "Fraction" format. Oddly enough, the whole numbers in other cells are still formatted as text! Is this a bug?

Sub Webpage()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://en.kingofsat.net/pos-28.2E.php", Destination:=Range("A1"))
.Name = "Web query"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
[COLOR=seagreen].PreserveFormatting = True               [/COLOR][COLOR=magenta]'Oh no it isn't...[/COLOR]
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = True
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

Bluto

 

[shg]

I don't do web queries, but just ran your code on a virgin worksheet, and cell I36 contains 2200 5/6 as text.

Re-ran the code, same thing.

 

[bluto32]

Thanks for this.

I've just done the same here, also on a brand new workbook, but still get it formatted as a fraction.

I'm using Excel 2010 on Windows 7 Ultimate, fully up to date (as far as I know - my machine gets important/critical updates for Windows and Office automatically).

Bluto

 

[shg]

I used 2003/2007 on XP.

Assuming your cells are formatted as General when you started (as mine were), I don't have another suggestion, sorry.

 

[gregtx81]

Don't know how to address the underlying issue, but you could throw this in at the end of your procedure...

Range("I36").Value = "'" & Range("I36").Text

 

[bluto32]

shg - Thanks for getting back to me about your versions of Excel; I guess this is a bug in Excel 2010.

Gregtx - Thank you for your suggestion!!!

I'm relatively new to VBA and haven't used ".Text" before; it makes the underlying issue straightforward to deal with. I have now captured those last three characters ("5/6") with:

Dim mystring As String
mystring = Right(Range("I36").Text, 3)

mystring now contains "5/6" rather than the "333" from before.

Many thanks indeed - project up and running again

Bluto