Creating a PDF file from Excel using VBA code

[spiwere]

Hi All,

I need some help to be able to accomplish the following:

I have 150+ reports in a folder. Each report has multiple tabs. All I want to be able to do is create a PDF from the first tab of each report and save it as a file in a sub-folder. The PDF file should be named using the file name of each report. The first tab of each report is named “Dashboard”

Could someone please help me with a code that can help me accomplish this. I searched on Google, but couldn’t find something fitting my needs.

My first tab has some data and about 5 to 7 graphs.

Please assist.

[Alf]

Perhaps a macro like this.

Please Login or Register  to view this content.

You must change the sPath to suit your needs and also this part of the code "I:\Temp\" to save the file to the preffered subfolder. When changing these parameters don't forget the last "\" in the commands i.e. "C\Temp\" will work but not "C:\Temp"

Alf

[spiwere]

Wonderful. Thanks a bunch. I'll try it and get back shortly.

Quick question: This will loop through each file and create individual files, right? Just checking as am driving now. Saw your reply so stooped by to convey thank you!

I'll test it once I get back to office.

Kind regards,
S

[Alf]

Yes, macro will loop through all ".xlsx" files in given folder ("sPath") activate sheet "Dashbord" and making a pdf file with the same name as the ".xlsx" file (- the .xlsx part).

The only thing I'm unsure about is how the graph part behave when converting to pdf.

Alf

[spiwere]

The only thing I'm unsure about is how the graph part behave when converting to pdf.

Alf

Dear Alf,

This works like a charm! Just one thing though, is it possible make it somewhat dynamic so that it auto fits the contents? In other words, in some cases the partial graph / table is getting captured in one PDF, and the remaining in the next page. I would like the complete table or graph in one PDF sheet.

Please advise.

Thanks,
Spi

[Alf]

First let me correct two mistakes I made writing this macro:

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

because you have to set action to take before error is found and the correct way to write this statement is "On Error GoTo". This error trapping is for xlsx files that have no "Dashboard" sheet, they should be ignored and when this error occurce macro should jump down to line "skipper:"

But if you excamine the macro you will see first the file is opened, then sheets("Dashbord") should be activated, if there is no "Dashboard" this trows an error and macro jumps dovn to line "skipper:" but the way it's written the "skipper:" line is placed after the "file close command" so this means the file will stay open untill you manually close it.

Therfore change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

in some cases the partial graph / table is getting captured in one PDF, and the remaining in the next page.

I'll have a go at it but it would be easier for me if you could upload one of the xlsx files that spreads ower two pdf pages so I could play around with it.

Alf

[Alf]

Ok I found a way to fit the result to a one page pdf file, but testing I fount that the result greatly depends of how many pages the excel worksheet will create as it is converted to a pdf file. I would assume 3 to 4 pages should work fine anything more than that may be difficult to read.

There is a freebee called "FreePDF Creator" that perhaps could suit your needs, there is also a bit information on why the pdf sheets created from excel looks the way they do as excel uses "Standard settings"

http://www.pdfconverter.com/resource...o-one-page-pdf

The updated code looks like this now:

Please Login or Register  to view this content.

As this should solve your problem could you please mark your thread "Solved"

Alf

Ps

To mark your thread solved do the following:
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved

[spiwere]

[QUOTE

As this should solve your problem could you please mark your thread "Solved"

Alf

Ps[/QUOTE]

Fantastic solution. Your Macro does it all. I really don't need a free converter. It is magical. I processed 273 files exactly! It worked beautifully, and with tremendous speed. Just a small tweak may be needed and I'll be happy to close the thread. What it does is in the last graph, it doesn't take it completely else everything fits perfectly. Do you think there is a way that tweak can be made in the code to read it completely?

Best,
Spi

[Alf]

As I've not see the file (s) that makes this problem it's difficult to tell. You could try to put problem file (s) in a sparate folder and run the first macro to see if you get everything converted to a proper pdf fil.

Since macro works for most of the files I suspect it has to do with layout or worksheet size.

Upload one of the files that works and one that don't and I'll see what I can do.

Alf

[spiwere]

Upload one of the files that works and one that don't and I'll see what I can do.

Alf

Thanks Alf. I completely understand. let me try what you've suggested, and then I'll post the problem files if it doesn't work still. Thanks much!

Best,

Spi

[spiwere]

As this should solve your problem could you please mark your thread "Solved"

Alf

Ps

Dear Alf,

First and foremost, please accept my apologies for taking a little more time to check this out. I wanted to be absolutely sure. It turned out to be an issue with my data layout, as you'd mentioned. I took sometime to fix it, and everthing now seems to per working as expected.

Please accept a sincere thank you. Your solution is just "PERFECT". It has helped me execute my task with speed and accuracy. I'm oblidged. Thank you for your magical solution.

I'm closing the thread as "Solved". I can't do anything else.

Best,
Spi

[Alf]

You are welcome.

Thanks for feedback and rep

Glad this worked for you, but still I do wonder because the the format setting to one page is done in the excel environment, then the page is saved as a pdf file with minimal settings. One would think that the proper way to do this would be to add the "one page" setting in the pdf environment during the "save as pdf" operation.

Alf

[spiwere]

You are welcome.

Thanks for feedback and rep

Glad this worked for you, but still I do wonder because the the format setting to one page is done in the excel environment, then the page is saved as a pdf file with minimal settings. One would think that the proper way to do this would be to add the "one page" setting in the pdf environment during the "save as pdf" operation.

Alf

Dear Alf,

I did not see any issues nor any of my team members using it have pointed any out, just in case we do will it be okay to comeback and post the problem file?

Thanks,
Spi

[Alf]

will it be okay to comeback and post the problem file?

No problem, since I'm a subscriber to this thread and will be for a year at least so any posting done in this thread will send an email to me notifying me of any activity.

Alf