View original (French)

VBA Excel: Web Page and QueryTable

Solved
mikel831 Posted messages 237 Registration date   Status Member Last intervention   -  
yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   -

Hello,
The following piece of code
        ' Copy the web page into "Sheet2"
        Sheets("Sheet2").Select
        Cells.Clear
        ' add a QueryTable
        Set QT = ActiveSheet.QueryTables.Add(Connection:="URL;" & URL, 
       Destination:=Range("A1"))
        ' configure the QueryTable...
        With QT
        .WebSelectionType = xlEntirePage '... to select the entire page
        .Refresh BackgroundQuery:=False  '... to refresh the data and paste it into the sheet
        End With
        ' release the QueryTable object
        Set QT = Nothing
......
copies an entire web page into Sheet2 starting from A1 and it works satisfactorily for my needs. However, some areas of the web page contain indications like 1-14 (for example) and are imported into the Excel cells as a date format like Jan-14 ... Is it possible to avoid this issue and get back 1-14?
 


Windows / Chrome 124.0.0.0


1 answer

  1.
    yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   Ambassadeur 1 588
     

    Hello,

    You can see that you have the same behavior if you manually enter "1-14" into a cell.

    To prevent Excel from interpreting the data, you must first set the cells to "Text" format.

     Cells.Clear Cells.NumberFormat = "@"
    1
    1. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588
       

      You could also have tested it simply by assigning a value like this:

       Cells.Clear [a5] = "1-14" Cells.NumberFormat = "@" [a6] = "1-14"

      You would have noticed that it had nothing to do with the fact that the source was a web page.

      0
    2. mikel831 Posted messages 237 Registration date   Status Member Last intervention   19
       

      Thank you for your response!
      1) If I set the cells to text format before the Qt set, it doesn't change anything.
      2) If I do it afterwards, the janv-14 obviously transforms into 41640...
      Is there no configuration parameter for QT?

      0
    3. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588 > mikel831 Posted messages 237 Registration date   Status Member Last intervention  
       

      I think there is a setting that allows you to preserve the format of the cells.

      It would be more of a setting accessible through the destination, rather than through the connection.

      I suggest you test manually to find this setting, then automate if necessary (the VBA could simply use an existing parameter).

      https://stackoverflow.com/questions/56678825/formating-change-in-excel-when-refresh-data-source-connectred-with-sql-server

      0
    4. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588 > yg_be Posted messages 23437 Registration date   Status Contributor Last intervention  
       

      and so?

      .WebDisableDateRecognition = True
      1
    5. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588 > mikel831 Posted messages 237 Registration date   Status Member Last intervention  
       

      With my test HTML file, it works by simply setting it to text format.

       Cells.Clear Cells.NumberFormat = "@" Set QT = ActiveSheet.QueryTables.Add(Connection:="URL;" & Url, _ Destination:=Range("A1")) ' configure the QueryTable... With QT .WebSelectionType = xlEntirePage '... to select the entire page '.PreserveFormatting = False '.WebDisableDateRecognition = False .Refresh BackgroundQuery:=False '... to refresh the data and paste it into the sheet 
      one <br> 1-14 <br> three
      1