• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Command Button code and code to save/convert the active sheet to PDF

G

GN0001

Member
Hello to all,

I need to insert a common button in every sheet of my workbook. When the end user clicks on it, can save/covert the active sheet to PDF. I think a VBA code should take care of this.

Any help is really appreciated it.

Thanks,

G
 
The macro recorder sayeth:


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

"C:Book1.pdf", Quality:=xlQualityStandard, _

IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _

True


office 07
 
Thank you Dan_1

I inserted a button command button from Active x Control and I inserted the code you have sent me, it gives me error.


I recorded a macro, then I inserted a button form control and then I linked the button form control to the macro, it worked. But I need to get the code corrected. Can you please help me with correcting the code. Also, when I record the macro, I saved the file as pdf on my desktop. Do you think does this work for everybody? Because everybody has a desktop.

Thank you for your help.

Guity
 
[pre]
Code: 
Sub exportaspdf()
Dim desktoploc As String
Dim filename As String
Dim mypath As String

desktoploc = CreateObject("WScript.Shell").SpecialFolders("Desktop")
filename = ThisWorkbook.Name
mypath = desktoploc & "" & filename

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=mypath, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True

End Sub
[/pre]
 
I have not tried this code yet, but I recorded a macro and it worked. The macros shows the path for the location where I save the excel workbook, the path for the file belongs to my own computer. If I send the excel workbooks to others, does this path change and work for other end users? I will get back to you.

G
 
Ehhhh. I'm not real Windows smart, but I think the desktop is located beyond the user name, like C:/users/SomeGuy/desktop/. So unless all of your users are logged in as SomeGuy, the recorded code might not work.


The code I posted above accounts for this.
 
Hello dan_1

I got a very big, big credit from my boss.....

It worked on my computer at home, then it worked for all the members of our team at work.I should send the workbook to bunch of clients and as you say desktop is beyond the username, having considered this logic, then it should work for everybody. And I saved the code in all open workbooks.

Thank you for your help.

Guity
 
Hello Dan ! This is brilliant. Could you please let me know - if I would like to print only a range in an active sheet. Thanks.
 
I'm no expert here:


You should be able to replace the active sheet with any range you want.


ActiveSheet.ExportAsFixedFormat


As for incrementing per print, I think that would require you to put a ticker somewhere in a cell in the workbook. Name it something like printcounter or something. Then:


Sub exportaspdf()

Dim desktoploc As String

Dim filename As String

Dim mypath As String

dim vprintcounter as integer


vprintcounter = range("printcounter").value


desktoploc = CreateObject("WScript.Shell").SpecialFolders("Desktop")

filename = ThisWorkbook.Name

mypath = desktoploc & "" & filename & vprintcounter


[WhateverRangeYouWant].ExportAsFixedFormat Type:=xlTypePDF, filename:=mypath, _

Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _

:=False, OpenAfterPublish:=True


End Sub
 
Hello Members ! Please help.


In the above code, if the "vprintcounter" is 4, the file name would be abcd-4.

I would like to have zero padding preceding 4, like abcd-00004.
 
Change the one line of code to this:

[pre]
Code: 
vprintcounter = Format(range("printcounter").value,"00000")
[/pre]
This is similar to using custom cell formats, it's forcing the value to use a 5 digits format.
 
You must log in or register to reply here.
Back
Top