Sub MatMas()
Dim strKey As String, strDate As String
Dim lngI As Long
On Error GoTo NewDate
strKey = "43885E5D60BE9CDDE053190A3F0AEXXX"
With ThisWorkbook
NewDate:
strDate = InputBox("Datum eingeben (Format = JJJJMMTT)", "Abfrage", Format(Date - 30, "yyyymmdd"))
If Len(strDate) Then
If .Queries.Count > 0 Then
For lngI = 1 To .Queries.Count
If .Queries(lngI).Name Like "*MatMas*" Then .Queries(lngI).Delete
Next
For lngI = 1 To .Sheets("Tabelle1").ListObjects.Count
If .Sheets("Tabelle1").ListObjects(lngI).DisplayName Like "*MatMas*" Then .Sheets("Tabelle1").ListObjects(lngI).Delete
Next
End If
.Queries.Add Name:="MatMas", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Quelle = Xml.Tables(Web.Contents(""http://api1.boydakexport.com/b2bservice/GetMaterialSAP/" & strKey & "&lud=" & strDate & """))," & Chr(13) & "" & Chr(10) & " Table0 = Quelle{0}[Table]," & Chr(13) & "" & Chr(10) & " #""Geänderter Typ"" = Table.TransformColumnTypes(Table0,{{""Attribute:ProductCode"", type text}, {""Attribute:ModelName"", type text}, {""Attribute:ProductName_TR"", type text}, {""" & _
"Attribute:ProductName_EN"", type text}, {""Attribute:NetWeight"", type number}, {""Attribute:GrossWeight"", type number}, {""Attribute:packet_count"", Int64.Type}, {""Attribute:Volume"", type number}, {""Attribute:Status_Date"", type date}, {""Attribute:Status"", type text}, {""Attribute:CartelaCode"", type text}, {""Attribute:Manufacture"", type number}, {""Attribu" & _
"te:Brand"", Int64.Type}, {""Attribute:Unit"", type text}, {""Attribute:LastUpdateDate"", type date}, {""Attribute:UrunSinifKodu"", type text}, {""Attribute:KalemTipi"", type text}, {""Attribute:UrunHiyerarsisi"", type text}, {""Attribute:UrunHiyerarsisiTanimi"", type text}, {""Attribute:MalzemeSerisi"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Geänderter Typ"""
With .Sheets("Tabelle1").ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""MatMas""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [MatMas]")
.ListObject.DisplayName = "MatMas"
End With
.Sheets("Tabelle1").Rows(1).Replace "Attribute:", ""
End If
End With
End Sub
