String + WebQuery Problem

[economistleo]

Hi,

I'm using Msqury to get external data from a website. One of the imported fields is an id string. the problem is that all of the characters in this string are numerical, and ms excel evaluates the id as such. Some of these id are larger than 17 digit, and so I lose some of my last digits. My question is, how can I setup the Wquery to import everything as string(text)? Is there a way to do so? Im using it in vba code: '

With ActiveSheet.QueryTables.Add(Connection:= _
urt, _
Destination:=Cells(1, 1))

.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2"
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

I've tried all kinds of combinations within these options, and had no success. Any thoughts or ideas?

Thanks,

Leo

 

[SkipVought]

Hi,

WebPreFormattedTextToColumns Property
See AlsoApplies ToExampleSpecificsReturns or sets whether data contained within HTML <PRE> tags in the Web page is parsed into columns when you import the page into a query table. The default is True. Read/write Boolean.

What happend if you change this property?

Try different WebFormatting properties.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[economistleo]

No deal, Skip.

Do you know how does it work (Ms query)? I mean, you think it evaluates the data before putting into the sheets? Maybe I could try to send it to an array before, but it seems it only accepts range values...any idea?

(Thanks for the answer, btw. I see you are a very active member)

 

[SkipVought]

I hardly ever use the WEB query.

I know how to handle this sort of issue, if a DB query or a file import, but not WEB.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}