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

dixiecricket

New Member
Joined
Jun 15, 2015
Messages
9
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!
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
I have been unable to figure out how to copy text/headers and footers from each selected page and paste, in order, in Word.
 
Upvote 0

Forum statistics

Threads
1,224,550
Messages
6,179,462
Members
452,915
Latest member
hannnahheileen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top