Interested to become VBA Pro? Here is 100% free guided e-course with Real-life projects. Sign-up to participate, get assignments and a certificate:- bit.ly/3eX8tzl
By the way, an easier way to get a filepath than copying the file name and adding it to the folder in Windows Explorer, you can use Shift+Left Click on the file you want then choose "Copy As Path".
This was super helpful. There are up to 4 tables in a file I was going through and it was cumbersome using refresh all and getting errors because only 1 table is valid and I could not find a way to stop the error message (none of the VBA commands for error handling etc.. stopped the error message from appearing). Now with some modification I can find the right table and import the values.
Awesome video and outstanding tutorial. I am running into a problem though. When using the doc.table method (tRow and tCount) ..... it works, but it stops counting at the first row (and after the 4th column). When I Debug.Print tRow & tCount it returns 2 and 5 respectively - That count is correct. However, when I "send" it to the Worksheet, I only get 1Row and 4 Columns. Please note, the first column is actually blank or "empty". I thought that might have something to do with it. - Now, when I use the Paragraph method and Debug.Print wLine ...... ALL of the information, text, etc.. comes back to the Immediate Window PERFECTLY (minus Trim and Clean). The problem with this is, ...... I cannot get it to "paste" that data to the Spreadsheet. Cell "A1" is continually blank. I am stuck. - Any suggestions?
When I run this code the line "Set wDoc = wApp.Documents.Open(filename, False)" fails and Excel prompts an error saying that the Open method couldn't work. What can this be? (I already have the MS objects libraries checked in Tools > References).
Many thanks to make this video....Could you please make a video of advance array, VBA dictionary and collection so we can develop our advance knowledge in VBA
Please check this video. I have used practical example with simple example to use array and dictionary. btw - array and dictionary are very simple to use in VBA. th-cam.com/video/GVykRlxfzPI/w-d-xo.html
@@VBAA2Z thank you so much......you have made a video to comparing sheet could you please elaborate that because I am new in vba array dictionary and collection
It just doesn't work for me. Not sure why. The code run hangs for infinite time at this last line Set wDoc = wApp.Documents.Open(FolderPath & pdffile, False) FolderPath = Application.ThisWorkbook.Path & "\" FileExtn = "*.pdf*" pdffile = Dir(FolderPath & "\" & FileExtn) ExcelFile = Dir(FolderPath & "\" & "*.xls*") wApp.Visible = False '''Loop through each pdf file in folder Do While pdffile ""
On Error Resume Next Set wDoc = wApp.Documents.Open(FolderPath & pdffile, False)
Hello, LP! The application works occasionally but with a lot of pdfs I'm getting this notification: "Microsoft Excel is waiting for another application to complete an OLE action" How do I overcome this? Does it have something to do with the fact that PDFXchange editor is my default pdf.
Hi Tobie, this could be due to multiple scenarios like memory not cleared properly, the library locked by other app. Try below settings and see if it helps. techcommunity.microsoft.com/t5/excel/no-links-vba-addons-but-microsoft-excel-waiting-for-another/m-p/331063 While the error message is always the same, there are actually a couple of pretty common scenarios that trigger this particular error in Microsoft Excel: DDE protocol is deactivated in Excel - This problem can occur because the DDE protocol (Dynamic Data Exchange) is deactivated in the Excel settings. Corrupt Office Installation - Some users have reported that the problem was resolved after reinstalling or repairing the entire Office installation. Adobe Acrobat PDFMaker add-in conflicts with Excel - There have been some reports from users who were able to fix the problem by disabling or uninstalling the PDFMaker plug-in. IE (Internet Explorer) process is interfering with the DDE - this usually occurs when the user tries to save a file in Microsoft Excel. In this case, the solution is to manually end the process. Examble for DDE protocol, a quick guide. Open Microsoft Excel and click on File. It doesn't matter whether you're opening a new workbook or a new document. Open Microsoft Excel and go to File On the File menu in the left pane, click Options. Go to File> Options From the Excel Options menu in the left menu, click the Advanced tab. Then move to the right pane and scroll down until you get to the General section. Make sure that the Ignore other applications using Dynamic Data Exchange (DDE) check box is cleared. Examble for Adobe Acrobat : In the COM Add-Ins box, either clear the check box for the Acrobat PDFMaker Office COM Add-in, or select it and click Remove to delete it completely. Examble for IE explorer: Press Ctrl + Shift + Esc to open Task Manager. Switch to the Processes tab in Task Manager and check whether an Internet Explorer process is currently active. If one is open, just right click on it and choose End Task to close it. End the Internet Explorer (IE) process Return to Excel and see if the "Microsoft is waiting for another application to complete an OLE action" error still occurs when you try to save the file. Wenn das alles nicht geholfen hat , dann hilft nur (maybe) die Unterdrückung von Excel Application Messaging mit VBA or turn off compatibility mode .
@@VBAA2Z I will explore more as some did not work but it may be that the pdf is in some sort of Acrobat Format or to big. I would also like to know how to change the code to get the heading or description just above a table if that is possible.
Excel file has many sheets with different data structures, I need to output a PDF file containing the data of all the sheets so that when printing, choose 2-sided printing mode. Can VBA do this?
Hi Sir, I have same kind of requirement but I need to copy entire data from pdf to excel and I don't have any other applications except ms office. While using thus code I am getting notification as "Microsoft excel is waiting for another application to complete an OLE acttion" Don't know what to do.
Hi Avinash - please check out this thread techcommunity.microsoft.com/t5/excel/microsoft-excel-is-waiting-for-another-application-to-complete/m-p/1388914
Great tutorial @VBA A2Z Does this only work on some type of PDF's? Because I am getting only abracadabra back when I try to extract data. Also I cannot find any usable field when I look inside PDF's with notepad etc I can read inside the PDF files => "PDF 1.4" Any suggestions?
Correct, it does not work for all types of PDF. Some pdf created using Acrobat are not readable using Word App lib leaving us with Acrobat API which is cost involved but most reliable.
Hi Lung, One question! This is clearly means, Word Library has the facility to convert pdf files. We can open pdf files in work and then we can extract a piece of information. But could you guide us, is this facility also works with Office 2003, 2007, 2010, 2013, 2016, 365 or word 2003, 2007, 2010, 2013, 2016, 365 as well?
@@giancajaracadillo Por favor revise este enlace. Tenga en cuenta que algunos controles de formulario, como ListView, no funcionan para Excel de 64 bits. vbaa2z.blogspot.com/2020/04/compile-error-code-in-this-project-must.html
try below and also check this video: th-cam.com/video/uccdOWubLpg/w-d-xo.html Set obj = ActiveDocument.InlineShapes(1) obj.OLEFormat.Activate obj.OLEFormat.Object.SaveAs "filename"
Hi my vba code not identifying some tables...because it is mergerd in 2 or 3 pages and its only givin the table data from last page only not all data. plz help
In that case I guess you can extract all data to excel first and organize your table back. All the best! 3. Read all PDF Content using VBA th-cam.com/video/uc6palG76Y8/w-d-xo.html 4. Extract table from PDF using VBA th-cam.com/video/uc6palG76Y8/w-d-xo.html
@@VBAA2Z Thank you.. but i want to do the same without using Acrobat , am asking about making any changes in this code (code in this video) will help me????
@@vg6119 yes, from any office application like excel, word etc. you'll need PDF Acrobat for that but if this is the pdf you're creating you can easily convert excel / word doc to pdf check this video. th-cam.com/video/v-uxpc86wrw/w-d-xo.html
I am trying to convert the pdf to word doc but the format does not remain same and some text goes to some other page of the word doc. Can you please help advise ?
You may have already figured it out. This happened to me when I typed wline.range.text in the for each loop Instead of wline = pg.range.text By mistake
Very useful and informative. However, I believe your video would be much better if you would write the code in advance so that you could show and explain the code. Watching you type, correct typos, handle errors, etc. is very boring and makes the video excessively long.
Didn't know Word app could deal with pdf tables! Awesome tip, thanks!
You're welcome.
Interested to become VBA Pro? Here is 100% free guided e-course with Real-life projects. Sign-up to participate, get assignments and a certificate:- bit.ly/3eX8tzl
By the way, an easier way to get a filepath than copying the file name and adding it to the folder in Windows Explorer, you can use Shift+Left Click on the file you want then choose "Copy As Path".
thanks for the useful tip Kyle.
You’re the best! Thanks for another informative and useful video.
You are so welcome!
This was super helpful. There are up to 4 tables in a file I was going through and it was cumbersome using refresh all and getting errors because only 1 table is valid and I could not find a way to stop the error message (none of the VBA commands for error handling etc.. stopped the error message from appearing). Now with some modification I can find the right table and import the values.
Glad it helped!
Thank you for sharing this powerful tip
You are so welcome
Another's excellent one. Thanks.
Thanks for listening
Another excellent tutorial thank you!
Glad you liked it!
thank you so much for this helpful tutorial
You're very welcome!
love your tuts sir. keep it up. many thanks.
Glad you like them!
Thanks as always. Keep up the good work
Thanks, will do!
You’re a miracle worker!!! Thanks brother
Glad to help
thanks for useful video.
You are most welcome
When i run the code at 6:07, it keeps running but doesn't display anything to the Immediate window. Why?
this is brilliant. thank you.
You're very welcome!
Thanks a lot man
U are a god
Glad to hear it help but I am not :)
Another useful tut. thanks!
Glad it was helpful!
Thanks. Very informative
You're welcome
Thanks sir for sharing your knowledge. Keep it up
It's my pleasure
million thanks to you Sir
you're most welcome!
Thanks bro.... I really didn't know about this before 👍🏽
Most welcome. Thanks for watching
@@VBAA2Z Bro can you please tell,
How can I print an Excel file using vba & selecting no. of copies using vba only ?
try something like this.
Sheet1.PrintOut Copies:=10
Awesome video and outstanding tutorial. I am running into a problem though. When using the doc.table method (tRow and tCount) ..... it works, but it stops counting at the first row (and after the 4th column). When I Debug.Print tRow & tCount it returns 2 and 5 respectively - That count is correct. However, when I "send" it to the Worksheet, I only get 1Row and 4 Columns. Please note, the first column is actually blank or "empty". I thought that might have something to do with it. - Now, when I use the Paragraph method and Debug.Print wLine ...... ALL of the information, text, etc.. comes back to the Immediate Window PERFECTLY (minus Trim and Clean). The problem with this is, ...... I cannot get it to "paste" that data to the Spreadsheet. Cell "A1" is continually blank. I am stuck. - Any suggestions?
another great video thanks so much!
Glad you enjoyed it!
Amazing Sir
thanks
Thanks a lot for the awesome video. It seems you forgot the link in the description :)
Sure will upload it to blog soon.
Please check vbaa2z.blogspot.com/
When I run this code the line "Set wDoc = wApp.Documents.Open(filename, False)" fails and Excel prompts an error saying that the Open method couldn't work. What can this be? (I already have the MS objects libraries checked in Tools > References).
Hi Antonio, what is the exact error? it is possible your file path is not correct.
Many thanks to make this video. Please make the video on msi installer for VSTO deployment project. I am waiting for next video of your VSTO Series.
Sure, we've many topics to be covered in Office for .NET :)
Supperb
Thank you! Cheers!
Many thanks to make this video....Could you please make a video of advance array, VBA dictionary and collection so we can develop our advance knowledge in VBA
Please check this video. I have used practical example with simple example to use array and dictionary. btw - array and dictionary are very simple to use in VBA.
th-cam.com/video/GVykRlxfzPI/w-d-xo.html
@@VBAA2Z thank you so much......you have made a video to comparing sheet could you please elaborate that because I am new in vba array dictionary and collection
It just doesn't work for me. Not sure why. The code run hangs for infinite time at this last line
Set wDoc = wApp.Documents.Open(FolderPath & pdffile, False)
FolderPath = Application.ThisWorkbook.Path & "\"
FileExtn = "*.pdf*"
pdffile = Dir(FolderPath & "\" & FileExtn)
ExcelFile = Dir(FolderPath & "\" & "*.xls*")
wApp.Visible = False
'''Loop through each pdf file in folder
Do While pdffile ""
On Error Resume Next
Set wDoc = wApp.Documents.Open(FolderPath & pdffile, False)
é isso que estava procurando, seria bom em Português
Obrigada. Vou tentar atualizar as legendas
I use excel 2007 at work. Do you think this could work?
Yes, it should.
Hello, LP! The application works occasionally but with a lot of pdfs I'm getting this notification: "Microsoft Excel is waiting for another application to complete an OLE action" How do I overcome this? Does it have something to do with the fact that PDFXchange editor is my default pdf.
Hi Tobie, this could be due to multiple scenarios like memory not cleared properly, the library locked by other app.
Try below settings and see if it helps. techcommunity.microsoft.com/t5/excel/no-links-vba-addons-but-microsoft-excel-waiting-for-another/m-p/331063
While the error message is always the same, there are actually a couple of pretty common scenarios that trigger this particular error in Microsoft Excel:
DDE protocol is deactivated in Excel - This problem can occur because the DDE protocol (Dynamic Data Exchange) is deactivated in the Excel settings.
Corrupt Office Installation - Some users have reported that the problem was resolved after reinstalling or repairing the entire Office installation.
Adobe Acrobat PDFMaker add-in conflicts with Excel - There have been some reports from users who were able to fix the problem by disabling or uninstalling the PDFMaker plug-in.
IE (Internet Explorer) process is interfering with the DDE - this usually occurs when the user tries to save a file in Microsoft Excel. In this case, the solution is to manually end the process.
Examble for DDE protocol, a quick guide.
Open Microsoft Excel and click on File.
It doesn't matter whether you're opening a new workbook or a new document.
Open Microsoft Excel and go to File
On the File menu in the left pane, click Options.
Go to File> Options
From the Excel Options menu in the left menu, click the Advanced tab.
Then move to the right pane and scroll down until you get to the General section.
Make sure that the Ignore other applications using Dynamic Data Exchange (DDE) check box is cleared.
Examble for Adobe Acrobat : In the COM Add-Ins box, either clear the check box for the Acrobat PDFMaker Office COM Add-in, or select it and click Remove to delete it completely.
Examble for IE explorer: Press Ctrl + Shift + Esc to open Task Manager.
Switch to the Processes tab in Task Manager and check whether an Internet Explorer process is currently active.
If one is open, just right click on it and choose End Task to close it.
End the Internet Explorer (IE) process
Return to Excel and see if the "Microsoft is waiting for another application to complete an OLE action" error still occurs when you try to save the file.
Wenn das alles nicht geholfen hat , dann hilft nur (maybe) die Unterdrückung von Excel Application Messaging mit VBA or turn off compatibility mode .
@@VBAA2Z I will explore more as some did not work but it may be that the pdf is in some sort of Acrobat Format or to big. I would also like to know how to change the code to get the heading or description just above a table if that is possible.
sure, happy to assist. feel free to send me your code to the email in video description.
Thank you for all your usefull videos.
Please i need help to download multiple files form SharePoint subfolders that could be exist early or not.
here is example of how to upload. to download you just need to reverse the source and destination path.
th-cam.com/video/LLxM7cekToU/w-d-xo.html
@@VBAA2Z That right. Thank you. I am going to try it and after i will come back to you.
Excel file has many sheets with different data structures, I need to output a PDF file containing the data of all the sheets so that when printing, choose 2-sided printing mode. Can VBA do this?
Sure it can it done. I'll try and cover this topic in future videos
is it possible to attached signature to pdf file with api / any other method using VBA?
I'll try and demo in coming videos for now here is API ref.
www.adobe.com/content/dam/acom/en/devnet/acrobat/pdfs/access_v9.pdf
Hi Sir, I have same kind of requirement but I need to copy entire data from pdf to excel and I don't have any other applications except ms office.
While using thus code I am getting notification as "Microsoft excel is waiting for another application to complete an OLE acttion"
Don't know what to do.
Hi Avinash - please check out this thread techcommunity.microsoft.com/t5/excel/microsoft-excel-is-waiting-for-another-application-to-complete/m-p/1388914
Great tutorial @VBA A2Z
Does this only work on some type of PDF's?
Because I am getting only abracadabra back when I try to extract data. Also I cannot find any usable field when I look inside PDF's with notepad etc
I can read inside the PDF files => "PDF 1.4"
Any suggestions?
Correct, it does not work for all types of PDF.
Some pdf created using Acrobat are not readable using Word App lib leaving us with Acrobat API which is cost involved but most reliable.
Hi Lung, One question! This is clearly means, Word Library has the facility to convert pdf files. We can open pdf files in work and then we can extract a piece of information. But could you guide us, is this facility also works with Office 2003, 2007, 2010, 2013, 2016, 365 or word 2003, 2007, 2010, 2013, 2016, 365 as well?
Yes it should. Though I have not tried in all the versions.
@@VBAA2Z I did some research. OCR is available from version 2013. Thanks a lot Lung.
@@kamalam29 great, thanks Kamal
Los códigos solo son compatibles con Office 365? O que?
También debería funcionar en versiones anteriores de Office.
@@VBAA2Z la vez pasada estaba haciendo el logo en un video tuyo, pegué el código y no funcionó...
sale como que: el codigo debe actualizarse para usarse en sistemas de 64 bits
@@giancajaracadillo Por favor revise este enlace. Tenga en cuenta que algunos controles de formulario, como ListView, no funcionan para Excel de 64 bits.
vbaa2z.blogspot.com/2020/04/compile-error-code-in-this-project-must.html
This was a grt tip thank you so much for this, is it possible for you to provide the code ?
Sure will upload it to blog soon.
Please check vbaa2z.blogspot.com/
@@VBAA2Z Thank you soo much..!! You are awesome !!
Where do you get the dark theme for the VBA code editor? Thanks
Nevermind, found your other video where you explain it.
ok great! hope it helps
Hi. Thank you for the wonderful code. But why I am getting an error with Disable Macro in Word
Happy to help.
please check support.microsoft.com/en-us/office/enable-or-disable-macros-in-microsoft-365-files-12b036fd-d140-4e74-b45e-16fed1a7e5c6
Can u help me?
How to extract some image from word document to folder using excel vba?
try below and also check this video: th-cam.com/video/uccdOWubLpg/w-d-xo.html
Set obj = ActiveDocument.InlineShapes(1)
obj.OLEFormat.Activate
obj.OLEFormat.Object.SaveAs "filename"
hi friend, your video helped me a lot, but I have one little problem, I can't copy the header of the page, can you help me?
It is possible the header is snapshot and not text
@@VBAA2Z can you show me please how to write the code?
if snapshot we can't read data
Hi my vba code not identifying some tables...because it is mergerd in 2 or 3 pages and its only givin the table data from last page only not all data. plz help
In that case I guess you can extract all data to excel first and organize your table back. All the best!
3. Read all PDF Content using VBA
th-cam.com/video/uc6palG76Y8/w-d-xo.html
4. Extract table from PDF using VBA
th-cam.com/video/uc6palG76Y8/w-d-xo.html
@@VBAA2Z Thank you.. but i want to do the same without using Acrobat , am asking about making any changes in this code (code in this video) will help me????
Hi can you share the vba program and I have pdf exchange will it work on it?
This program can work without PDF Reader / Acrobat.
Thank you, do you have a copy of the program so that I can try it out
Also is there a way we can run a vba in excel and have a text or sentence inserted in pdf?
@@vg6119 yes, from any office application like excel, word etc. you'll need PDF Acrobat for that but if this is the pdf you're creating you can easily convert excel / word doc to pdf
check this video.
th-cam.com/video/v-uxpc86wrw/w-d-xo.html
Can you please send file link?
vbaa2z.blogspot.com/
Still waiting for the link my friend.
vbaa2z.blogspot.com/
I am trying to convert the pdf to word doc but the format does not remain same and some text goes to some other page of the word doc. Can you please help advise ?
Have you tried this? th-cam.com/video/uc6palG76Y8/w-d-xo.html
@@VBAA2Z I cannot see the reference used in video at my office laptop Excel.
This version uses Acrobat API, I'd recommend checking it from beginning.
@@VBAA2Z you are saying in the below video Acrobat API is used th-cam.com/video/uc6palG76Y8/w-d-xo.html
Yes, mostly.
Compile error user defined type not defined
May I know which line is causing this error?
You may have already figured it out.
This happened to me when I typed
wline.range.text in the for each loop
Instead of wline = pg.range.text
By mistake
Very useful and informative. However, I believe your video would be much better if you would write the code in advance so that you could show and explain the code. Watching you type, correct typos, handle errors, etc. is very boring and makes the video excessively long.
thanks Jim! much appreciated for feedback
word is converting everything in my pdf into an image, hel please
snapshot doc cannot be read