Retrieveing INSEE data in Excel via Power query

New Contributor

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 = "" & Idbk

Application.DisplayAlerts = False
Set Sht = Sheets.Add(, ActiveSheet)
Sht.Name = Idbk
With Sheets(Idbk)
ThisWorkbook.XmlImport Url:=Site, ImportMap:=Nothing, Overwrite:=True, _

.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



2 Replies
best response confirmed by Cyssou (New Contributor)

@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.

Screenshot 2021-08-01 at 08.31.06.png

@Riny_van_Eekelen thanks this is really helpfull !!