Convert Excel Document (selected sheets) to PDF & then that PDF to Word

[dixiecricket]

I have an excel worksheet where, depending on cell values, specific sheets are selected and need to be printed to an editable text document. Via VBA, I am able to convert everything to PDF and then to DOCX; but in the DOCX each row of text is returned as a table row so the result doesn't allow for the ability to freely edit text. When I open the PDF and manually export the PDF to a word document, the result IS a fully editable text document.

Is there a way, in the original VBA, to export the created PDF to word?
Thank you!

 

[Dan_W]

Im not sure that I follow, my apologies, but is there some reason you're not just transferring the data directly to Word from Excel? it seems like you're taking the long and unreliable way around by converting it to a PDF first, and then extracting data from that.

 

[dixiecricket]

I was unable to bring selected worksheets, with header images and text formatting as written in Excel, into Word without error. If you can provide a way to do that, I will go that route.

 

[Dan_W]

Well, what was the error? Transferring data from Excel to Word is a very, very common task, and so I expect that these errors can be addressed.

 

[dixiecricket]

Converting from Excel to Word, everything appeared to work seamlessly. But when I would open the word document, I would receive an error "Word found unreadable content error" and would be prompted to repair the file. But the file was always blank regardless of what option I chose. Google research showed that this error could results from several issues, malware, error during conversion, etc.; everythning was ruled out as a cause except for the image in the header. Removing the image allowed to document to process.....but I have to have the image.

My next route was to try the PDF option so that it would lock all images and text formatting in place; and I can bring that into Word without problem; but each row of text is brought in as an individual table row so text cannot be freely edited.

If you know of a way to bring the selected worksheets and images in to Word - I would be grateful for the assistance.

 

[Dan_W]

There are 1000 different ways of doing it. The first step is to see what you tried first, and work out what went wrong. The "malware and error during conversion" causes don't sound particularly compelling,, and the image issue sounds very strange, but whatever the reason, is entirely fixable.

So - how did you transfer the data in the first place?

 

[dixiecricket]

Code is below - it provides a blank Word document. No error, nothing:

Sub printCoverLetter()

Dim wb As Workbook
Dim filePath As String
Dim sheetsToPrintArray()
Dim sheetCount As Integer
Dim objWord As Object
Dim objDoc As Object
Dim objTitle As String
Dim FileCopyPath As String

Set wb = ThisWorkbook

'change this title to something more relative if needed
objTitle = "Configuration Guide"
filePath = wb.Path
FileCopyPath = filePath & "\" & objTitle & ".docx"

'always adjust cover letter contents first, just to make sure
'this is being called everytime the sheet is activated within the sheet code, but just as a fail safe
Sheet2.Range("$K$53:$K$63").AutoFilter Field:=1, Criteria1:="1"

'always print
sheetCount = 1
ReDim Preserve sheetsToPrintArray(sheetCount)
sheetsToPrintArray(0) = Sheet2.Index
sheetsToPrintArray(1) = Sheet4.Index

'if medicaid
If Sheet1.Cells(15, "D") = "YES" Then
sheetCount = sheetCount + 1
ReDim Preserve sheetsToPrintArray(sheetCount)
sheetsToPrintArray(sheetCount) = Sheet6.Index
End If

'if medicare
If Sheet1.Cells(16, "D") = "YES" Then
sheetCount = sheetCount + 1
ReDim Preserve sheetsToPrintArray(sheetCount)
sheetsToPrintArray(sheetCount) = Sheet7.Index
End If

'if ACPS
If Sheet1.Cells(17, "D") = "YES" Then
sheetCount = sheetCount + 1
ReDim Preserve sheetsToPrintArray(sheetCount)
sheetsToPrintArray(sheetCount) = Sheet8.Index
End If

hideShow True, sheetsToPrintArray
Sheets(sheetsToPrintArray).Select

Set objWord = CreateObject("word.application")
objWord.Visible = True
Set objDoc = objWord.Documents.Add
objDoc.SaveAs2 Filename:=FileCopyPath

hideShow False, sheetsToPrintArray

'to remove the multi sheet selection
Sheet1.Select

End Sub



Function hideShow(isVisible As Boolean, sheetsToPrintArray())
For i = 0 To UBound(sheetsToPrintArray)
Sheets(sheetsToPrintArray(i)).Visible = isVisible
Next i
End Function

 

[Dan_W]

Ok. I don't have my computer here to check anything, but that all seems fine. But there's nothing in there that copies that data across, though. In your code, you get as far as deciding which sheets to select, you select them, hide the rest, create an instanc3 of word, add a new doc, save that document, unhide the sheets, and that's it...

 

[dixiecricket]

I have been unable to figure out how to copy text/headers and footers from each selected page and paste, in order, in Word.