Jul 27 2021 09:48 AM
Hi I'm looking a way to connect INSEE data in Excel using Power Query but I don't understand how to connect Power Query
If I use VB code I correctly get data in imported in an excel sheet but I would prefer to get a connected source : any idea ?
VB code :
Sub Series()
Dim IdBank As Variant, i As Byte
IdBank = Array("010534284")
For i = 0 To UBound(IdBank)
Import IdBank(i)
Next i
End Sub
Sub Import(Idbk As Variant)
Dim Site As String, Sht As Worksheet
Site = "https://bdm.insee.fr/series/sdmx/data/SERIES_BDM/" & Idbk
Application.DisplayAlerts = False
Set Sht = Sheets.Add(, ActiveSheet)
Sht.Name = Idbk
With Sheets(Idbk)
ThisWorkbook.XmlImport Url:=Site, ImportMap:=Nothing, Overwrite:=True, _
Destination:=.Range("A3")
.Range("A1").Value = "Mise à jour du :"
.Range("A1").HorizontalAlignment = xlRight
.Range("B1").Value = Format(Now(), "dd/mm/yyyy")
.Range("C1").Value = Format(Now(), "hh:nn")
End With
Application.DisplayAlerts = True
Set Sht = Nothing
End Sub
Jul 31 2021 11:37 PM
Solution@Cyssou Perhaps the attached file helps you to get started with PowerQuery.
Connecting to the site with the specified Idbank-code produces a list of indices for the past 17.5 years, but perhaps I didn't find all the data "hidden" in the XML source. Enter an Idbank-code on the first sheet. On the data ribbon, press Refresh All and the data will be updated in the table on the Data sheet.
Aug 02 2021 12:25 AM
@Riny_van_Eekelen thanks this is really helpfull !!
Jul 31 2021 11:37 PM
Solution@Cyssou Perhaps the attached file helps you to get started with PowerQuery.
Connecting to the site with the specified Idbank-code produces a list of indices for the past 17.5 years, but perhaps I didn't find all the data "hidden" in the XML source. Enter an Idbank-code on the first sheet. On the data ribbon, press Refresh All and the data will be updated in the table on the Data sheet.