Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Mar 2003
    Location
    Marlow, Buckinghamshire, England
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel & Web Query & Worksheet Formatting (VBA / Excel 2000 & 2002)

    All affected Worksheet cells are globally formatted to "General" when a Web Query completes - irrespective of the formatting of the cells prior to the query, and irrespective of how any QueryTable property is set. Does anyone know how can I preserve cell formatting such that it doesn't get changed by the query? (PreserveFormatting doesn't seem to provide the answer.)

    Thank you,

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel & Web Query & Worksheet Formatting (VBA / Excel 2000 & 2002)

    Strange indeed.

    I have an XL2000 worksheet with a webquery. If I click "Data Range Properties" and check "Preserve Cell formatting", all formatting is kept as advertised.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    New Lounger
    Join Date
    Mar 2003
    Location
    Marlow, Buckinghamshire, England
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel & Web Query & Worksheet Formatting (VBA / Excel 2000 & 2002)

    Many thanks for your prompt response. You are, indeed, correct and using the method you note, I can achieve what I require for second and subsequent Query retrievals, but not for the first retrieval. Following your procedure if I programatically execute the Query, wait for it to finish, then manually reset Cell formatting to "Text" (reverting from "General" as appears forced by the Query), then go "Data Range Properties" and check "Preserve cell formatting", then manually "Refresh Data", all is well and Cell formatting is preserved. However, what I am attempting to achieve is to preserve Cell formatting on the first execution of the Query. I create and run the Query as follows:

    Set qtWebQuery = ActiveSheet.QueryTables.Add(Connection:="URL;" & strURI, _
    Destination:=Range("A1"))

    With qtWebQuery
    .AdjustColumnWidth = False
    .PostText = Application.WorksheetFunction.Substitute(strQueryD ata, " ", " ")
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshPeriod = 0
    .RefreshStyle = xlOverwriteCells
    .SaveData = False
    .TablesOnlyFromHTML = False
    .WebFormatting = xlWebFormattingAll
    .WebDisableDateRecognition = True
    .Refresh BackgroundQuery:=False
    End With

    Any further thoughts you may have on the subject will be appreciated.

    Thank you,

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel & Web Query & Worksheet Formatting (VBA / Excel 2000 & 2002)

    What you could do is use this kludge:

    - insert a new sheet
    - copy the sheet's formatting to that sheet
    - create the query
    - refresh it
    - copy, paste-special the formatting from the new sheet to the query sheet.
    - remove the new sheet.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    New Lounger
    Join Date
    Mar 2003
    Location
    Marlow, Buckinghamshire, England
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel & Web Query & Worksheet Formatting (VBA / Excel 2000 & 2002)

    Once again, many thanks for your prompt response.

    Your 'kludge' is very sensible but unfortunately in my case it won't work. What I'm basically attempting to do is import part numbers (purely integers BUT with a variable number of leading zeroes) where the format of the part number (including the leading zeroes) must be retained. As you can no doubt see, when the Query completes, the leading zeroes are lost (because the Query forces "General" formatting), therefore no amount of 'kludging' with formatting after the event will recover those leading zeroes.

    As an aside, my 'kludge' (since I have control of the server script that responds to the Query) is on the server, and as the report is formed, to replace all zero digits with a non-numerical character that I know is not used elsewhere in the report. Then on the client side when the Query completes, to reset Cell formatting to "Text" and to replace all instances of the non-numerical character with a 0 digit. Inelegant and it causes me grief to have to do it, but it overcomes the problem.

    Again, thank you for all your help.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel & Web Query & Worksheet Formatting (VBA / Excel 2000 & 2002)

    Is there no way to specify the format of the partnumber field ( in the query or -better- in the table of the database) as text then?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    New Lounger
    Join Date
    Mar 2003
    Location
    Marlow, Buckinghamshire, England
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel & Web Query & Worksheet Formatting (VBA / Excel 2000 & 2002)

    Indeed this is the case. An SQL database does hold all Part Numbers (with their leading zeroes) as varChar, i.e., "Text". The server script that responds to the Query places the results of interrogating the database (the Part Numbers) in an HTML table which when displayed, by for example IE, shows the leading zeroes retained.

    To demonstrate this problem simply (without coding any VBA - and assuming you have the time and inclination) I have attached a sample HTML file (zeroes.txt - you will need to change the file extension to htm or html) the contents of which are:

    <html>
    <body>
    <table>
    <tr><td>000001</td></tr>
    <tr><td>000002</td></tr>
    </table>
    </body>
    </html>

    Again, if you are interested, carry out the following to demonstrate the problem:

    1. Save the file 'zeroes.htm' (or create a new one having the contents above) to some known location on your hard disk.
    2 . Create a new blank Workbook.
    3. Format all Cells to Text.
    4. Create a new Web Query.
    5. Set the Address in the Web Query to point to the HTML file you have saved on the hard disk, say, c:zeroes.htm.
    6. Select the table displayed (there should only be one) in the New Web Query dialogue box.
    7. Click Import.
    8. At your choice leave the Worksheet address in which to put data as the default.
    9. Click Properties.
    10. Check Preserve cell formatting.
    11. Click OK.

    You will note that leading zeroes are not present but Cell formatting is retained ("Text").

    If we can get this to work (i.e., retain the leading zeroes) then we may solve my problem.

    Thank you,

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel & Web Query & Worksheet Formatting (VBA / Excel 2000 & 2002)

    I replicate your problem, I'm sorry.

    If these zeroes are there just for display, why not use a custom number format of "0000000000000" (the amount of zeroes equals the number of digits in the credit card numbers). After the import set the custom format for the entire column(s) with those numbers.

    Of course if the database would output the credit card numbers with spaces:

    1234 1234 1234 1234

    All would be well...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    New Lounger
    Join Date
    Mar 2003
    Location
    Marlow, Buckinghamshire, England
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel & Web Query & Worksheet Formatting (VBA / Excel 2000 & 2002)

    Good suggestion, but unfortunately in our case not appropriate.

    The Part Numbers (not Credit Card Numbers) have a length ranging from four to twelve digits with a variable number of leading zeroes. For example, Part Numbers 1234, 01234, 001234, etc., are all valid and each indicates a different unique part (please don't ask why this numbering system, it's "historical"). Therefore formatting Cells to "Custom" with, say, "000000000000" after the Query completes would produce invalid results.

    Once again, many thanks,

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel & Web Query & Worksheet Formatting (VBA / Excel 2000 & 2002)

    Pity...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    New Lounger
    Join Date
    Mar 2003
    Location
    Marlow, Buckinghamshire, England
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel & Web Query & Worksheet Formatting (VBA / Excel 2000 & 2002)

    Jan,

    Thank you for all your help. Is there any merit in reporting this to Microsoft as a bug? If so, how does one go about reporting a bug?

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Excel & Web Query & Worksheet Formatting (VBA / Excel 2000 & 2002)

    I wouldn't hold much hope of getting any sympathy from Microsoft - they will tell you the result is by design. As I recall, if you put a single quote in front of a numeric string, it will actually preserve the number as a text number with leading zeros. I would suggest you look at changing your SQL script to add the character to each number as you create the XLM data.
    Wendell

  13. #13
    New Lounger
    Join Date
    Mar 2003
    Location
    Marlow, Buckinghamshire, England
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel & Web Query & Worksheet Formatting (VBA / Excel 2000 & 2002)

    Thank you for the advice and the suggestion. Prepending a number that has leading zeroes with an apostrophe will, as you say, allow leading zeroes to be retained in a "General" formatted Cell - except, unfortunately, in the case of a Web Query. All that happens with a Web Query, is that the results of the Query include the apostrophe (along with the leading zeroes and the number) in the Cell. But perhaps doing this is a better work-around than my solution (replacing all zeroes with 'X' on the server then replacing all 'X's with zero on the client).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •