Insert picture JPG in footer

[sm789]

Hello everybody!

I need to add a picture (jpg) file as a footer and then save the file as a pdf.

I got this done:

Sub Macro1()
With ActiveSheet.PageSetup.RightFooterPicture
       .FileName = "/Users/mac/Desktop/Picture1.jpg"
   End With
   ActiveSheet.PageSetup.RightFooter = "&G"

    Sheets("1111").Select
    Range("Print_Area").Select
    
    'You can specify the range rather than Print Area. In which case you need to rem the line above
    ' and remove REM from below two lines
'            Set rr = ActiveWorkbook.Sheets("1111").Range("a1:f55")
'            rr.Select
    
' Retrieve settings
                WhereTo1 = Range("Z67").Value
                WhereTo2 = Range("AA67").Value
                Stamp = Range("C68").Value
    
' Check if Stamp-field has any value, if not, add the current date.
                If Stamp = "" Then Stamp = Date

' Assemble the filename
                sFileName1 = WhereTo1 & Stamp & y & ".pdf"
     
' Check if the File Already Exists
             If Dir(sFileName1) = "" Then
             GoTo SaveFile1:
            Else
            End If
    
    Select Case MsgBox("This file exists. Continue & overwrite?", vbYesNo Or _
        vbExclamation Or vbDefaultButton1, "")
        Case vbYes
        GoTo SaveFile1:
        
        Case vbNo
        GoTo SecondFile:
        End Select
 
' Save the File as PDF

SaveFile1:
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sFileName1, Quality _
                :=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                OpenAfterPublish:=False
        
' Assemble the second filename

SecondFile:
                sFileName2 = WhereTo2 & Stamp & y & ".pdf"

' Check if the File Already Exists
                If Dir(sFileName2) = "" Then
                GoTo SaveFile2:
                Else
                End If
    
    Select Case MsgBox("This file exists. Continue & overwrite?", vbYesNo Or _
        vbExclamation Or vbDefaultButton1, "")
        Case vbYes
        GoTo SaveFile2:
        
        Case vbNo
        GoTo Continue:
        End Select


        '' Save the Second File as PDF
         
SaveFile2:
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sFileName2, Quality _
                :=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                OpenAfterPublish:=False
        
'' End of PDF creation

Continue:

End Sub

My question is that at the beginning of this code, I have specified a filename. I would like the code to ask me to choose the file. Is it at all possible? I am a Mac user so don't know if that is at all an issue.

Thanks a ton//

 

[Shortmeister1]

If I was doing this in Windows, it would look something like this.

    Dim strFile As String
    strFile = InputBox("Enter file including full path")
    MsgBox fileWithPath
    
    ActiveSheet.PageSetup.RightFooterPicture.Filename = fileWithPath
    'etc. etc. etc.

 

[Dan_W]

I think the GetOpenFilename method works with Mac VBA too, so if you adjusted @martinshort's helpful code above slightly, you could do something like

Dim strFile As String
StrFile = Application.GetOpenFilename
ActiveSheet.PageSetup.RightFooterPicture.Filename = strFile

 

[sm789]

I think the GetOpenFilename method works with Mac VBA too, so if you adjusted @martinshort's helpful code above slightly, you could do something like

Dim strFile As String
StrFile = Application.GetOpenFilename
ActiveSheet.PageSetup.RightFooterPicture.Filename = strFile

Thank you for your help! The first part works very well. I checked with MsgBox the value of strFile was accurate with complete path. However, it did not add that image file as Right Footer. Am I missing anything?

 

[sm789]

If I was doing this in Windows, it would look something like this.

    Dim strFile As String
    strFile = InputBox("Enter file including full path")
    MsgBox fileWithPath
   
    ActiveSheet.PageSetup.RightFooterPicture.Filename = fileWithPath
    'etc. etc. etc.

Thank you. I wanted the code to open a finder window and allow me to choose the file rather than enter the path and filename. Dan W's suggestions of Application.GetOpenFilename works but it is not adding the image file as right footer. The code returns no error though..

 

[jolivanes]

Just an additional line to Dan's code.

Dim strFile As String
strFile = Application.GetOpenFilename
    With ActiveSheet.PageSetup
        .RightFooterPicture.Filename = strFile
        .RightFooter = "&G"    '<---- Add this line
    End With

 

[sm789]

Just an additional line to Dan's code.

Dim strFile As String
strFile = Application.GetOpenFilename
    With ActiveSheet.PageSetup
        .RightFooterPicture.Filename = strFile
        .RightFooter = "&G"    '<---- Add this line
    End With

Thank you Jolivanes and thank you Dan. The additional line did the trick.

Much appreciated.