I want insert watermark for all files in VBA

[quangthay]

Hi everybody,
I want insert watermark for all files in VBA but I haven't found a solution yet.

 

[smozgur]

Welcome to the MrExcel Message Board!

Hi everybody,
I want insert watermark for all files in VBA but I haven't found a solution yet.

How about looping through .xlsx files in the selected folder, opening them one by one, applying the watermark by using the .PageSetup property, and save & close each workbook.

Something like the following as a starting point:
Notes:

• You need to change the image file path with yours: strImageFile
• The current code applies the watermark to all worksheets in the workbook. If you have multiple sheets and only want to apply the watermark to the first sheet, then the code needs to be changed for that.
• The current code is looking for the .xlsx files only. If you have other Excel files with different extensions, then the code needs to be changed for that as well.

Please test it first with sample workbook files since the code is going to overwrite the files in the selected folder.

Sub InsertWatermark()
Dim strImageFile As String
Dim dlgFolder As FileDialog
Dim strFile As String
Dim wrk As Workbook
Dim sht As Worksheet

    strImageFile = "C:\Somewhere\Pictures\watermark.jpg"
    
    Set dlgFolder = Application.FileDialog(msoFileDialogFolderPicker)
    
    With dlgFolder
        .Show
        If .SelectedItems.Count Then
            strFile = Dir(.SelectedItems(1) & "\*.xlsx", vbNormal)
            Do While strFile <> ""
                Set wrk = Application.Workbooks.Open(.SelectedItems(1) & "\" & strFile)
                For Each sht In wrk.Worksheets
                    With sht.PageSetup
                        .CenterHeaderPicture.Filename = strImageFile
                        .CenterHeader = "&G"
                    End With
                Next sht
                wrk.Close True
                strFile = Dir
            Loop
        End If
    End With
End Sub

 

[quangthay]

Thank you very much.
I tested the program but it shows problem
Although i try to fix it by going to trust center but it still doesn't work

 

[johnnyL]

Try removing the '?' in that first line of code below the 'Dim' section.

 

[quangthay]

Try removing the '?' in that first line of code below the 'Dim' section.

I removed the "?" before but still not available

 

[smozgur]

After removing the mistyped ? as @johnnyL explained, please rerun the code and when the execution halts with the error, click on the Debug button and let us know the highlighted line in the code pane. We are looking for the code line causes the error.
I assume it is about the image file folder, may be the folder permissions or something like that, but it would help if we know the exact error line.

 

[quangthay]

Thank you for your feedback
I removed the ? redundancy and moving images to another folder avoids security issues
now it has problems about CenterHeader.

After removing the mistyped ? as @johnnyL explained, please rerun the code and when the execution halts with the error, click on the Debug button and let us know the highlighted line in the code pane. We are looking for the code line causes the error.
I assume it is about the image file folder, may be the folder permissions or something like that, but it would help if we know the exact error line.

 

[quangthay]

and this is the site they took me to

Develop solutions and customize Excel

Find how-to content, sample code, SDK and API documentation, VBA references, training, and technical articles for developing solutions and customizing Excel.

learn.microsoft.com

 

[johnnyL]

@quangthay You will get that error if you don't have a default printer set.
Do you have a default printer set?

 

[quangthay]

Thank you very much
I have run the program successfully after using it with the printer.
Wishing you a productive day.
Once again thank you so much smozgur and johnnyL.