The code below works, but not like I would like. I need to find a way to assign the query to a listobject. If you run the code you will see the connection only is made [under Data=> queries and conections] and the results of the query appear in the Sunrise tab. I have tried
$qt = $c.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcExternal,$cSource,$null,$c.Range(“$A$1”)).QueryTable
#$qt.CommandText = $SQL
and other combinations, but just returns errors. This can be done in VBA.
$mcode = @’
let
Source = Web.Page(Web.Contents(“https://sunrise-sunset.org/calendar?location=Raleigh&month=|MTH|&year=2021”)),
Data0 = Source{0}[Data],
#”Changed Type” = Table.TransformColumnTypes(Data0,{{“SUN”, type text}, {“MON”, type text}, {“TUE”, type text}, {“WED”, type text}, {“THU”, type text}, {“FRI”, type text}, {“SAT”, type text}})
in
#”Changed Type”
‘@
$mon = “April”
$qry = “Sunrise”
$cn = “Query – “+$qry
$mcode = $mcode.Replace(“|MTH|”,$mon)
$a = New-Object -ComObject Excel.Application
$a.Visible = $true
$a.ScreenUpdating = $true
$a.UserControl = $true
$a.DisplayAlerts = $false
$b = $a.workbooks.add()
$c = $b.Worksheets.Item(1)