Hyperlink in vba geht nur hart kodiert
05.03.2024 10:51:22
floket2
Übernehme ich die Adresse der Webseite hart kodiert klappt es prima.
ActiveWorkbook.Queries.Add Name:="Table 0", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Web.Page(Web.Contents(""http://internsite.com:20088/@@@/PROD/ALL%20OTHER%20REGION/Dollar%20Application%20Motor/Backlog%20Status.htm""))," _
& Chr(13) & "" & Chr(10) & " Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Data0,{{""PERSON_NAME"", type text}, {""HOLIDAYS"", Int64.Type}, {""COMPLETED_TILL"", type date}," & _
" {""COMPLETED_ON"", type datetime}, {""COMMENT"", type text}})," & Chr(13) & "" & Chr(10) & " #""Extracted Date"" = Table.TransformColumns(#""Changed Type"",{{""COMPLETED_ON"", DateTime.Date, type date}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Extracted Date"""
Probiere ich es aber mit einer String Variable
WebsiteAddress = Sheets("Dashboard").Range("AI25").Value
in der meine Webadresse drinsteht und rufe
ActiveWorkbook.Queries.Add Name:="Table 0", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Web.Page(Web.Contents(WebsiteAddress))," _
& Chr(13) & "" & Chr(10) & " Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Data0,{{""PERSON_NAME"", type text}, {""HOLIDAYS"", Int64.Type}, {""COMPLETED_TILL"", type date}," & _
" {""COMPLETED_ON"", type datetime}, {""COMMENT"", type text}})," & Chr(13) & "" & Chr(10) & " #""Extracted Date"" = Table.TransformColumns(#""Changed Type"",{{""COMPLETED_ON"", DateTime.Date, type date}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Extracted Date"""
funktioniert es nicht.
Ich würde das gerne mit Zuweisung programmieren. Woran könnte liegen, dass es nicht klappt?
Ich habe auch schon probiert, vorne und hinten Anführungszeichen chr(34) hinzuzufügen- ohne Erfolg.