I must embed many ServiceNow Change Requests (CR) as PDF documents in an Excel spreadsheet.
I can do this manually:
1 - export each CR to PDF (Landscape)
2 - embed all exported PDF files in the Excel spreadsheet
I need to know how I can automate this process, via Excel VBA.
I do know already 95% of all required VBA actions.
Very high level:
1 - execute for each CR an hyperlink: https://ing.service-now.com/change_request.do?sys_id=xxxxxxxx&PDF
note: I do now for each CR its sys_id, in above example shown as xxxxxxxxx (I retrieve/determine these sys_ids via REST APIs)
This step creates for each CR a change_request.pdf
2 - close all Edge windows in which the PDFs were created (1 window per CR)
3 - embed the created PDFs in an Excel sheet
What I'm looking for is an improved logic for steps 1 and 2.
Ideally a REST API would exist, which returns for each CR the PDF data (I can then save that data as .pdf file).
That way I do not require hyperlinks, close windows, etc.
Another solution could be using CreateObject("ADODB.Stream") logic.
However, that returns for each hyperlink HTML data, iso the final PDF data 😞
