How to save a worksheet as pdf into a SharePoint list and include the metadata at the same time

Thank you for reaching out and sharing the details of your scenario. I understand that you are currently exporting an excel worksheet as a PDF to a SharePoint document library using VBA, and you would also like to automatically save metadata to the corresponding SharePoint list item at the same time.

While excel’s ExportAsFixedFormat method supports exporting the file to SharePoint paths, it does not automatically attach or sync matter data to the documents associated SharePoint columns. To achieve this you will need to combine 2 operations:

1.      save the worksheet as a PDF file to SharePoint.

2.      Update the metadata fields for that file using SharePoint APIs(via Microsoft graph, rest API, auto power automate)

Option 1: VBA solution using SharePoint rest API

you can extend your VBA code to make a rest API call to SharePoint after saving the file.

Option 2: use power automate for metadata update(recommended)

I asked him blood and more reliable method is to use power automate(flow):

1.      continue using your VBM macro to export and save the PDF to your SharePoint document library

2.      create a power automate flow that triggers “when a file is created or modified in a document library”

3.       In the flow, use “get file metadata” and " Update the file properties" actions to:

·         Read metadata values(you can have VBA write them into a hidden excel file, JSON file or naming convention).

·         Update the SharePoint file’s metadata columns accordingly.

This approach avoids rest authentication issues and uses Microsoft supported no code automation tools.

Option 3: using Microsoft graph API(advanced)

If you have registered an Azure AD app, you can programmatically,

·         Upload the pdf using Graph Files API (/sites/{siteid}/drive/items/{itemID}/content)

·         Upload the associated ListItem fields using /sites/{siteId}/lists/{listId}/Items/{ItemsId}/fields.

References:

Working with files in Microsoft Graph - Microsoft Graph v1.0 | Microsoft Learn

Update listItem - Microsoft Graph v1.0 | Microsoft Learn

Please let us know if you require any further assistance we’re happy to help. If you found this information useful, kindly mark this as "Accept Answer".