Problems with data format? My code goes wrong?

[terrryc2115]

I am a new beginnner to excel VBA. I hope to automate the task of importing data from web into excel. However, when I run the following code, the result is far from what it should be :

Sub data()

' data Macro

Dim ticker As Variant
ticker = Sheets("input").Range("A1").Value

Sheets("ps").Select
Cells.Clear

    ActiveWorkbook.Queries.Add _
    Name:="Table_" & ticker & "", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""https://www.etnet.com.hk/www/eng/stocks/realtime/quote_ci_pl.php?code=" & ticker & """))," & Chr(13) & "" & Chr(10) & "    Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Data0,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}, {""Column6"", type text}, {" & _
        """Column7"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    
    With Sheets("ps").ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table_" & ticker & """;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 0]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Query_" & ticker & ""
'        .Refresh BackgroundQuery:=False
    End With

End Sub

The result of the above code is shown below and is wrong :

wrong.png

Obviously, there is no formating? I have to load the data into the sheet mannually and the expected result should be :


right.png


How can I automate both task (import data and load it into the sheet) with VBA? Which part of the code goes wrong? Please give me a hand!

[Winon]

Hello terrryc2115,

Thank you for you quick response in correcting the matter

[terrryc2115]

Sorry for not knowing the rule... Thanks for your kind reminder!