sync with a SharePoint list to xl

get_query_data.py

# %%
import xlwings as xw

# Define the path to your .iqy file
iqy_file_path = 'query.iqy'

# Initialize variables to hold the data you're interested in
sharepoint_application_url = ''
list_view_id = ''
list_name_id = ''

# Open and read the .iqy file
with open(iqy_file_path, 'r') as file:
    for line in file:
        line = line.strip()  # Remove leading/trailing whitespace

        # Check if line contains specific data and extract it
        if line.startswith('SharePointApplication='):
            sharepoint_application_url = line.split('=')[1]
        elif line.startswith('SharePointListView='):
            list_view_id = line.split('=')[1]
        elif line.startswith('SharePointListName='):
            list_name_id = line.split('=')[1]

# Use xlwings to open Excel and write the data to the "config" sheet
app = xw.App(visible=True)  # Set visible=False if you don't want Excel to open visibly
wb = app.books.add()  # Add a new workbook, or use app.books.open('path/to/workbook.xlsx') to open an existing one

# Ensure there is a sheet named "config", add one if not
if "config" not in [s.name for s in wb.sheets]:
    wb.sheets.add("config")

sheet = wb.sheets["config"]  # Reference the "config" sheet

# Write the data into column B, starting from row 1
sheet.range('B1').value = sharepoint_application_url
sheet.range('B2').value = list_view_id
sheet.range('B3').value = list_name_id

# Optionally, save the workbook
# wb.save('path/to/save/your/workbook.xlsx')

# If you don't want to leave Excel open, uncomment the next line
# app.quit()


# %%

sync_data_sp.bas

Sub link_edit_mode_sp()
'this will download a list from SP to XL in a worksheet named "data_sp" range A1
'start in the list and export as excel then use connection properties to get guids
Dim ws_worksheet As Worksheet
Dim list_obj As ListObject

Const SERVER As String = "[link to main site]/_vti_bin"
Const LISTNAME As String = "{[guid for list]}"
Const VIEWNAME As String = "{[guid for view]}"

Set ws_worksheet = Sheets("data_sp")

Set list_obj = ws_worksheet.ListObjects.Add( _
    SourceType:=xlSrcExternal _
    , Source:=Array(SERVER, LISTNAME, VIEWNAME) _
    , LinkSource:=True _
    , XlListObjectHasHeaders:=xlYes _
    , Destination:=ws_worksheet.Range("A1") _
    )

Set ws_worksheet = Nothing
Set list_obj = Nothing

End Sub