Save as PDF

[TorrO]

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

 

[John_w]

Try this:

    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.

 

[TorrO]

Try this:

    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?

 

[John_w]

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:

    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:

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

 

[TorrO]

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.

 

[John_w]

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.

 

[TorrO]

@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.