VBA to create Excel sheet and a copy as PDF

[MichaelC]

Hi -


I have an Access 2003 application that creates various reports for our company. It creates some of them in Excel and some Access reports are put into PDF using Lebans ConvertToPDF code. Right now I am creating a new set of reports that will be in Excel. I need to have these Excel sheets also create a PDF copy that will be placed in a seperate directory. I've experimented with different things but not having a lot of luck with this. It needs to be automated so I can't have the user being prompted for where to save the PDF file.

After the VBA code has created the Excel document, but before it closes the Excel application, .PrintOut seems to be the best option (Adobe PDF print driver). But I havent been able to find a way to automatically pass the filename to the application so the Save As box is not brought up. If I use print to file I don't see any way to get a proper PDF format. Is there a way to make this work? Not sure that is matters, but I am using Adobe 9. Any help is greatly appreciated!!!

This is what I'm trying:
objXL.Application.Workbooks.Add
set objActiveWkb = objXL.Application.ActiveWorkbook

....code to create the layout of the report

objActiveWkb.Worksheets(intCurSheet).Printout
objActiveWkb.Close SaveChanges:=True, Filename:=strFileName

[June7]

Try:
http://www.excelguru.ca/node/21
http://www.tek-tips.com/viewthread.c...1249943&page=1

[MichaelC]

Thanks! The second link you gave had a solution further down on the page that worked using Distiller. Creates a log file for some reason, but I can deal with that. Appreciate the info