1

Good morning,

I would like to convert the PDF file to Word one (from PDF to DOCX) using Excel macro.

So far I learned the process from this video:

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

and this link:

https://www.pk-anexcelexpert.com/pdf-to-word-converter-macro-in-excel-vba/

but the problem is, that the example is based on the certain cells including the fixed file directory:

  pdf_path = sh.Range("E4").Value
  word_path = sh.Range("E5").Value

I would like to have always directory the same as my active workbook, which I am working on.

In this event I tried the following code:

    Sub Wort_To_PDF()

       Application.ScreenUpdating = False
       Application.DisplayAlerts = False
       Application.DisplayStatusBar = True


       Dim pdf_path As String
       Dim word_path As String

       pdf_path = ThisWorkbook.Path & "\"
       word_path = ThisWorkbook.Path & "\"

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

       Set fo = fso.GetFolder(pdf_path)

       Dim wa As Object
       Dim doc As Object

       Set wa = CreateObject("word.application")
       wa.Visible = True

       Dim file_Count As Integer

       For Each f In fo.Files

       Application.StatusBar = "Converting - " & file_Count + 1 & "/" & fo.Files.Count
       Set doc = wa.Documents.Open(f.Path)
       doc.SaveAs2 (word_path & "\" & Replace(f.Name, ".pdf", ".docx"))
       doc.Close False
       file_Count = file_Count + 1
       Next

       wa.Quit

       MsgBox "All PDF files have been converted in to word", vbInformation
       Application.StatusBar = ""

   End Sub

I am getting an error "Type mismatch" pointing the following line:

   Set fo = fso.GetFolder(pdf_path)

I found some hints regarding the usage of active workbook directory in VBA

How to get the path of current worksheet in VBA?

and tried to put it into my code:

   pdf_path = Application.ActiveWorkbook.Path
   word_path = Application.ActiveWorkbook.FullName

but the error is exactly the same.

Can anyone help me? I would like to convert the PDF file to docx in the same directory, where my active workbook is stored.

UPDATE:

When I change Dim fo as Folder to Dim fo As Object or Dim fo as Scripting.Folder I am getting another error, informing me, that file is corrupted. Debugger shows the following line:

   Set doc = wa.Documents.Open(f.Path)

enter image description here

I think, that problem might be somewhere with my excel document, which is already opened and used. In general, the code executes the first sheet only instead of all of them.

enter image description here

braX
  • 11,506
  • 5
  • 20
  • 33
Geographos
  • 827
  • 2
  • 23
  • 57
  • Please try `pdf_path = ThisWorkbook.Path` instead of `pdf_path = ThisWorkbook.Path & "\"` – FaneDuru Aug 19 '20 at 09:29
  • It's still the same mate. The same error at the same place :( – Geographos Aug 19 '20 at 09:32
  • Try declaring `Dim fo As Object`, please. – FaneDuru Aug 19 '20 at 09:48
  • It's a hint. I changed it and now I am getting another error: Set doc = wa.Documents.Open(f.Path) - the file appears to be corrupted – Geographos Aug 19 '20 at 09:51
  • Word cannot simple open a pdf file. It has to convert it. Usually it is able to do that, sometimes with big differences between the pdf and the converted docx one, but it will do it. How, exactly, the error is shown? Then, try manually opening it. Are you able to do that? – FaneDuru Aug 19 '20 at 10:01
  • I've uploaded my query. It looks like I am close to the solution, as my doc file is generated but contains the one page only. Is that something, which I should do with my excel workbook, which I am working on? – Geographos Aug 19 '20 at 10:15
  • There is not any connection between excel open workbook and the document you have just open in Word. Did you try **manually opening the pdf file, in discussion**, as I suggested above? If yes, how it behaves? If not, I wish you a good day! – FaneDuru Aug 19 '20 at 10:21
  • OK, I opened PDF file manually, and it looks like I have got only one page there only – Geographos Aug 19 '20 at 10:33
  • So, like I supposed, this is what Word can. Excel, instead, is not able to open at all such files. When I try something similar, I have Acrobat DC installed and I use a reference to it... – FaneDuru Aug 19 '20 at 10:35
  • But yesterday I tested the code from tutorial YT and it was working correctly, saving all .docx documents properly. I just wanted to modify this code superseding the fixed folder path with my active workbook directory. – Geographos Aug 19 '20 at 10:37
  • Do you mean, you used **the same files to be converted** and try telling us that only the folder path has changed the way Word converts them? If yes, I have some doubts that it would be true, but not needing conversions in this way, I do not know what Word is able to do. It maybe properly convert the editable pdf files, being exported from some applications and it has problems with the scanned one. This is only a supposition. I never tried this method... – FaneDuru Aug 19 '20 at 10:40
  • If you take a look on youtube link, which I attached here and next my folder directory in the image, then you can compare the situation. I tried exactly the same PDF file with these 2 methods. The YT one is working, but I don't want it with fixed directory. This is the problem. I believe, that not only you haven't tried this method. In general was hard to find something similar in Google. Most of stuff applies on the other hand really - changing word to PDF. – Geographos Aug 19 '20 at 10:44
  • Sorry, I cannot believe that story... Why don't you try, please, writing in Excel sheet the path in discussion (only for testing reason) and give it a shut in such a situation. With **the same file, of course**. I can swear it will behave exactly the same, but who knows? Maybe my logic needs to be refreshed... :) – FaneDuru Aug 19 '20 at 10:51
  • Right. I will probably back to YT example, and as you said - write the path to my workbook manually. However, will do it via Excel formula which will bring the path automatically. – Geographos Aug 19 '20 at 10:55
  • You are loosing time using a formula. Test it manually writing and you will see (probably) that anything will be changed... But **use the same file**. – FaneDuru Aug 19 '20 at 10:57

1 Answers1

2

The code could fail because ActiveWorkbook.Path contains something invalid:

If the ActiveWorkbook is a new workbook that was not saved yet, Path is empty - you will get Runtime error 5 (Invalid Argument)

If ActiveWorkbook is on a Sharepoint site or something like that, Path might be an URL - you will get Runtime error 76 (Path not found)

However in your case, it seems that the returned object of the GetFolder-method returns something that is not expected by the VBA runtime. It might be case that you have a type definition Folder somewhere that hides the Folder-type of the Scripting Library. Declare your variable qualified:

Dim fo as Scripting.Folder

Do so for all the other scripting object (f for example)

If you add a reference to the Microsoft Word 16 Object Library, you can also declare the word objects with the correct type, eg

Dim wa as Word.Application
Dim doc as Word.Document

Update: If you loop over all files of the folder, make sure you open only Word files with the Word.Application. Opening some other kind of files will throw errors like the one you see (might be corrupt)

Add a check for the filetype before you open it - you want to convert only word files:

For Each f In fo.Files
    if fso.GetExtensionName(f.Name) like "doc*" Then
  
       Set doc = wa.Documents.Open(f.Path)
       doc.SaveAs2 (word_path & "\" & Replace(f.Name, ".pdf", ".docx"))
       doc.Close False
       file_Count = file_Count + 1
    End If
Next f
FunThomas
  • 23,043
  • 3
  • 18
  • 34