Generate PDF from Word documents stored in SharePoint
Looking for some advice/guidance on where to begin...
A workbook I came across has some functionality I believe will be immensely useful for QA within a program of work I am involved in. Among other things, the workbook allows a user to select Word documents from multiple SharePoint repositories and stitches them together in a PDF. I've seen it in action on another users machine, but I do not have access to some of the directories being pointed to so the document will not generate for me. Regardless, I believe I have a few key points pinned down I "need" to focus on in order to get a minimum viable product up and running. Changing some variable names and omitting information in order to generify.
​
1. There is a "Forms" form that allows the user to filter on numerous SharePoint lists where they can add or remove forms to the list that would ultimately be included within the PDF generated.
2. There are multiple hidden tabs with extracts from SharePoint document repositories. Each Range points to A2 within the corresponding range. This appears to be called prior to displaying a listing of forms for user to select to ensure inventory is current at all times as the list in SharePoint is quite dynamic.
Sub RefreshForms()
Range("FormsinSharePoint").ListObject.QueryTable.Refresh BackgroundQuery:=False
...
End Sub
​
3. While I would be lying if I said I understand what is going on here, it appears this is where the "magic" happens. The most perplexing thing is I would imagine the list within FormsPrintList gets updated based on the above selections...this does not happen. Maybe because I do not have the appropriate access?
Sub MakePDF()
​
Dim wdDoc As Word.Document
Dim wdApp As Word.Application
Dim bNewApp As Boolean
Dim sh As Worksheet
Dim r As Integer
Dim x As Integer
Dim BeginRow As Integer
Dim EndRow As Integer
Dim ChkCol As Integer
Dim RowCnt As Integer
Sheets("FormsTemplate").Select
... 'I intentionally left out a few If statements that attach forms based on user selections surrounding other questions answered within the workbook.
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
On Error GoTo 0
If wdApp Is Nothing Then
Set wdApp = CreateObject("Word.Application")
bNewApp = True
End If
Set wdDoc = wdApp.Documents.Add
wdApp.Visible = True
Set sh = ActiveWorkbook.Worksheets("FormsPrintList")
r = 1
Do Until sh.Cells(r, 1).Value = ""
If Len(wdDoc.Range) > 0 Then
wdDoc.Bookmarks("\\EndOfDoc").Range.InsertBreak wdPageBreak
End If
' wdDoc.Bookmarks("\\EndOfDoc").Range.InsertFile filename:=sh.Cells(r, 2).Value
r = r + 1
Loop
Set wdApp = Nothing
Set sh = Nothing
​
End Sub
​
I am looking to recreate this on a smaller scale to start, adding in additional functionality when I get some additional understanding. Having a workbook pull from 1 SharePoint document repo to start allowing a user to select any number of forms from that repo and produce a PDF straight from the workbook.