VBA Excel .xlsx (.xlsm) to .docx conversion

Question

Good afternoon,

I would like to convert the .xslx file into word. Because I need to get everything accurately (I have 27 sheets with labels, tabs, images, etc) I decided to save it first as the PDF document, which works fine. Afterwards I would like to have it converted into .docx file, because the PDF to DOCX conversion also retains all these features.

Because PDF treats every worksheet as the single page, I would like to have it exactly the same in Word with everything kept intact from original excel document.

I found the nice PDF to DOCX conversion guide here

https://www.youtube.com/watch?v=Op25fUfvIl0

and following all these steps I've made this code working. However next, when I attended to combine this code with the saving XLSX as PDF macro I am having an error: Type mismatch indicating the following line:

  Set fo = fso.GetFolder(pdf_path)

Whereas my full code looks as follows:

  Sub xlstodoc()

 'SAVING XLSM FILE AS PDF

ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    ThisWorkbook.Path & "\" & ThisWorkbook.Name, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True
    
    
    
'CONVERTING PDF FILE INTO DOCX
    
    
  Dim fso As New FileSystemObject
  Dim fo As Folder
  Dim f As File

  Dim wa As New Word.Application
  Dim doc As Word.Document

  wa.Visible = True

  Dim pdf_path As String
  Dim word_path As String

  pdf_path = ThisWorkbook.Path & "\" '& ThisWorkbook.Name & ".pdf"
  word_path = ThisWorkbook.Path & "\"

  Set fo = fso.GetFolder(pdf_path)

  For Each f In fo.Files

    Set doc = wa.Documents.Open(f.Path)
    doc.SaveAs2 (word_path & "\" & Replace(f.Name, ".pdf", ".docx"))
    doc.Close False
    
  Next

  wa.Quit

  Application.StatusBar = ""

  MsgBox "All done"


  Sheets("Frontsheet").Select

  End Sub

The Youtube example applies to fixed folders. In my case I need the target directory the same as my active worksheet, which I am working on. How can I make this code successful? I think, that I am quite close to the solution.

TinMan TinMan · Accepted Answer · 2020-08-18T16:18:35

I'm guessing that you have another library referenced that has a folder object. Fully qualifying your types should solve the issue.

  Dim fso As New Scripting.FileSystemObject
  Dim fo As Scripting.Folder
  Dim f As Scripting.File

You can eliminate the fo object altogether:

  For Each f In fso.GetFolder(pdf_path).Files
    If fso.GetExtensionName(f) = "pdf" Then
        Set doc = wa.Documents.Open(f.Path)
        doc.SaveAs2 (word_path & "\" & Replace(f.Name, ".pdf", ".docx"))
        doc.Close False
    End If
  Next

VBA Excel .xlsx (.xlsm) to .docx conversion

1 Answers