Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MSAccess: Convert html to pdf using vba

Posted on 2014-01-23
15
Medium Priority
?
2,470 Views
Last Modified: 2014-01-24
I have an Access app that generates a bunch of html files. I need to create pdf copies of the files, and I see online that there are countless potential approaches. Can somebody suggest an efficient way to iterate through a folder in VBA and convert each html file to vba:

For Each F in Folder.Files
If F.Name Like "*.html" Then
Filename = Left(F.Name, InStr(F.Name, ".")-1)
  {Convert to PDF} Filename.pdf
End If
Next F
?
I'd prefer to avoid approaches that open the files in a browser and then print using a PDF driver.

Thanks
0
Comment
Question by:dgmoore1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
15 Comments
 
LVL 39

Accepted Solution

by:
PatHartman earned 1500 total points
ID: 39805011
Going forward, why not export both versions if that's what you need?

To fix up what you have, I wouldn't use Access, I would use Word since Word reads and writes both file types.  Since I don't work with Word VBA and am not familiar with the object model, I start by creating a macro.  So, turn on the macro recorder.  Open the html file.  Save as pdf.  Stop the macro recorder.  That will give you the heart of what you have to do.  Then fix the code to have a loop to read through the directory and do all the files.

If you wanted to run this from Access you could but if this is a one time action.  I would just hard code the path and run it in Word.
0
 
LVL 55

Expert Comment

by:Joe Winograd, EE MVE 2015&2016
ID: 39805023
I can't help you with VBA code, but if you're willing to try a commercial product, here are two possibilities:

(1) HTML2PDF Pilot:
http://www.snapfiles.com/get/html2pdfpilot.html

It has a GUI interface that can process individual files or an entire folder. It also has command line support for use in batch files and/or programs/scripts.

(2) A-PDF HTML to PDF:
http://www.a-pdf.com/html-to-pdf/

This also has both GUI and command line interfaces, as well as a Watched Folder feature.

They both offer 30-day trials. If you're OK with this approach, give them a spin and see if one works for you. Neither is free, but both are reasonably priced ($30 and $39, respectively). Regards, Joe
0
 

Author Comment

by:dgmoore1
ID: 39805251
Pat - thanks for the suggestion, but it's too complex and involves too many moving parts.

Joe, from the vendor's documentation it doesn't look like (1) will work, but (2) looks promising. I'm going to d/l a trial copy and see if I can shell to a cmd window and call the HTML2PDF executable. If that works, it could be the solution I'm looking for. I'll get back here after my test and let you know how it goes. Thanks for the tip!
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:dgmoore1
ID: 39805255
Alas, A-PDF HTML to PDF won't run on 64-bit windows. Back to the drawing board...
0
 
LVL 55

Expert Comment

by:Joe Winograd, EE MVE 2015&2016
ID: 39805428
I don't know what it is that HTML2PDF Pilot can't do for you, but maybe you need the HTML2PDF-X Pilot library instead:
http://www.colorpilot.com/html2pdfaddon.html

Re A-PDF HTML-to-PDF not working on a 64-bit OS, that really surprised me. But I tried it here on W7/64-bit and you are absolutely correct...the installer won't even run! I even tried it with a few compatibility mode settings and it still wouldn't install. I filled in a contact form and sent an email, too, asking when they're going to have a version that works on 64-bit. I'll let you know if and when I hear back from them (I like their products, but not their support, so I'm not hopeful). In the meantime, do you have a 32-bit system that it can run on? Or maybe a 32-bit VM? Regards, Joe
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 39805620
thanks for the suggestion, but it's too complex and involves too many moving parts.
What part is too hard?
1. Add a second OutputTo following the first.  That will save the file as both types.
2. Word actually does what you need to do.  It can open a file of type A and save as type B.  Access doesn't do that.
3. The Macro recorder will write the specific open and save code.
4.  You never said if this was a one-time only effort.

This is the code generated by the macro recorder.  You need to replace the RecentFiles(1).Open with a loop to read each file in the directory.  Use FSO.  The code will be the same in Word as it is in Access.

Sub Macro1()
'
' Macro1 Macro
'
'
    RecentFiles(1).Open
    ActiveDocument.ExportAsFixedFormat OutputFileName:= _
        "C:\TempPat\This is a test.pdf", ExportFormat:=wdExportFormatPDF, _
        OpenAfterExport:=True, OptimizeFor:=wdExportOptimizeForPrint, Range:= _
        wdExportAllDocument, From:=1, To:=1, Item:=wdExportDocumentContent, _
        IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:= _
        wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:= _
        True, UseISO19005_1:=False
End Sub

Open in new window

0
 

Author Comment

by:dgmoore1
ID: 39806416
This is to be part of a daily process that will run on any of several workstations and generate about 75 pdf's. I am aware of the Word capability but was leery about using Word because of past hassles with integrating Word and Access, but it looks like it may work for what I need. I'll be testing it today.

Thanks
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 39806585
If the Access app is generating the files, why not just generate both formats at once?
0
 

Author Comment

by:dgmoore1
ID: 39806612
I probably will - that's not settled yet. I just received the requirement yesterday afternoon and haven't thought it completely through yet. This conversion needs to fit in with a lot of other procedures, so I need to determine where it fits best.
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 39806734
If you generate both formats, all you need to worry about is converting what you have.  You don't need a procedure that you run daily or whatever.
0
 

Author Comment

by:dgmoore1
ID: 39807071
The process runs daily because the underlying data changes daily. We do a scheduled daily update after midnight and push the files to a Sharepoint portal. The html versions of the files are for viewing on the portal, and the pdf's are there for users who need to download printable versions.
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 39807335
What am I missing here?  If you create both file types from Access at the same time (one output statement followed by another), why do you need to run the process we are talking about beyond the once to create copies of some existing files?
0
 

Author Comment

by:dgmoore1
ID: 39807427
My apologies if my replies have not been as clear as they might have been. My process generates a group of html pages in one procedure, and a group of gifs in a subsequent procedure. The html pages display the gifs, and if I convert the html to pdf before it's gifs have been generated the pdf will be wrong. If I generate all of the html and all of the gifs, then iterate through the output folder, the html pages will display the proper gifs and the pdf's will be accurate. I can't generate the gifs before the html because the html files rely on data generated while the gifs are being produced. In any case, I'm accepting your approach as the solution as it appears that it will do the trick. Thanks
0
 

Author Closing Comment

by:dgmoore1
ID: 39807430
This is a daily procedure that runs in Access, so I will control Word from Access through automation.
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 39807480
Thank you.  Now I understand the problem.  You can control the conversion loop from Access and then pass into word the name of one file at a time.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question