Save as PDF

TorrO

Board Regular
Joined
Feb 13, 2003
Messages
118
Office Version
  1. 2013
Platform
  1. Windows
Hi

in macro under I have fixed path+Name.
My issue is that i dont know how to get macro to pick up path+name of open woorkbook

I need to save pdf in same folder as excel is saved, and Name (not .xlsm but .pdf extension)

So i need macro to read path and filename then save pdf with same name and path but with PDF extension

Here is my fixed path+name
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"E:\Google Drive\Price enquiry\2020.10.28. House1\2020.10.28. House1.pdf"


===========================================================================

Sub LagrePDF()
'
' LagrePDF Macro
'

'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"E:\Google Drive\Price enquiry\2020.10.28. House1\2020.10.28. House1.pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 595.7142519685, _
320.3571653543, 366.4285826772, 98.5714173228).Select
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = _
"HUSK Å GI NYTT NAVN PÅ PDF"
Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 26).ParagraphFormat. _
FirstLineIndent = 0
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 26).Font
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.ObjectThemeColor = msoThemeColorDark1
.Fill.ForeColor.TintAndShade = 0
.Fill.ForeColor.Brightness = 0
.Fill.Transparency = 0
.Fill.Solid
.Size = 18
.Name = "+mn-lt"
End With
Application.Wait (Now + TimeValue("0:00:10"))

Selection.Delete
End Sub
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Try this:
VBA Code:
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Replace(ActiveWorkbook.FullName, ".xlsm", ".pdf", Compare:=vbTextCompare), _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
PS please use VBA code tags.
 
Upvote 0
Try this:
VBA Code:
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Replace(ActiveWorkbook.FullName, ".xlsm", ".pdf", Compare:=vbTextCompare), _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
PS please use VBA code tags.
Thanks John_w worked perfectly.
There is no warning if pdf file exists, for my use that is ok, for others it may be problematic.

When I first get this good help, is there an option to:
1: Copy pdf to clipboard?
2: Open target directory in explorer?
 
Upvote 0
There is no warning if pdf file exists, for my use that is ok, for others it may be problematic.

Replace my code with this:
VBA Code:
    Dim PDFfullName As String
   
    PDFfullName = Replace(ActiveWorkbook.FullName, ".xlsm", ".pdf", Compare:=vbTextCompare)
    If Dir(PDFfullName) <> vbNullString Then
        MsgBox "PDF not created because " & PDFfullName & " already exists.", vbExclamation
    Else
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfullName, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        MsgBox "Created " & PDFfullName, vbInformation
    End If

1: Copy pdf to clipboard?

Do you mean the PDF contents or the full file path and file name?

2: Open target directory in explorer?

Incorporate this code into your procedure or call this routine:
VBA Code:
Public Sub Open_File_Explorer_Folder()

    Dim folder As String
    Dim Sh As Object, ShWindow As Object
    Dim i As Variant
    Dim foundWindow As Boolean
   
    'Folder that File Explorer will open, if not already open
   
    folder = ActiveWorkbook.Path
   
    If Right(folder, 1) = "\" Then folder = Left(folder, Len(folder) - 1) 
   
    Set Sh = CreateObject("Shell.Application")
    foundWindow = False
    i = 0
    While i < Sh.Windows.Count And Not foundWindow
        Set ShWindow = Sh.Windows(i)
        If StrComp("file:///" & Replace(Replace(folder, "\", "/"), " ", "%20"), ShWindow.LocationUrl, vbTextCompare) = 0 Then foundWindow = True
        i = i + 1
    Wend

    If Not foundWindow Then
        Shell "explorer.exe " & folder, vbNormalFocus
    End If   
    AppActivate Mid(folder, InStrRev(folder, "\") + 1)

End Sub
 
Upvote 0
Solution
Wow

you are gold

What I ment by 1: Copy pdf to clipboard?

The new pdf file is copied to clipboard so I easy can go to mail and paste it into a new mail.
 
Upvote 0
You didn't really answer my question. There are 2 methods of copying the PDF to the clipboard:

You can copy the PDF file itself to the clipboard (as a file link) and paste it into an email as an attachment.
Or you can open the PDF and copy its contents to the clipboard and paste its contents into the email body.

The first method is easy, by attaching the PDF to an Outlook email with Outlook VBA code (the clipboard isn't used).

The second method is very difficult and would require Windows API programming.
 
Upvote 0
@John_w You are an expert. I did not know it was 2 options.

Copy of PDF is to copy the newly stored pdf file to clipboard and then I make a new mail and paste it as a pdf document in the mail.
Regarding Outlook VBA i have problem on my computer to send attachments from Adobe, I get ddl error. So my idea to a workaround is to copy to clipboard.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top