Cell Formatting with a Web Query

Hi,

I have a vba web query which grabs some data formated on the web page as 01:03. Excel unhelpfully converts this to a time, eg 01:03:00, when I want to keep it in the original format.

This is my web query -

With ActiveSheet.QueryTables.Add(Connection:= "URL;" & URLVal1, Destination:=Range"$A$1"))
.Name = "TEMP"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebTables = "4"
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = True
.WebDisableRedirections = True
.Refresh BackgroundQuery:=False

End With

I've tried preformating the cells where the import occurs with a text format, but it doesn't fix it - I would have thought that the WebDisableDateRecognition property might have worked, but it doesn't.

Any idea how I can set a particular column to preserve the original text formating?

Thanks,
David

After importing, loop through each cell to add an apostrophe which prevents Excel's automatic formatting:

Please Login or Register  to view this content.

Change rows and column as required.

Hi,

Thanks for your reply - almost fixed it.

What I didn't say in my original request was that some of the numbers can be bigger than 24 - eg 32:17. This causes the conversion using the HH:MM mask to return an incorrect value. 32:17 becomes 08:17 - I'm guessing because this is a day and 8 hours

Any ideas on how I might solve this?

Thanks
David

Use the cell's .Text property instead of .Value and separate the parts with Mid or Split:

Please Login or Register  to view this content.

That's great - thanks for all your help! Problem fixed!