How to get the name of the background currently displayed in Excel?

[Janna One Trick]

Hello,

I have a macro in VBA that is on loop and cycles between many pictures I have in a folder as backgrounds for my spreadsheet.
I would like if possible to get the name of the background currently displayed so it writes it in a cell of the spreadsheet.

How could I do that?
Thanks

Here is the vba code:

Option Explicit

Const pth = "D:\Backgrounds\"
Const mfPttrn = "*.jpg"

Private i As Integer, rn As Integer
Private TimeToRun As Date
Private fleTbl

Sub file_names()
    Dim fle As String
    i = CreateObject("Scripting.FileSystemObject").GetFolder(pth).Files.Count
    ReDim fleTbl(1 To i, 1 To 2)
    i = 0
    fle = Dir(pth & mfPttrn, vbNormal)
    Do Until fle = ""
        i = i + 1
        fleTbl(i, 1) = i
        fleTbl(i, 2) = pth & fle
        fle = Dir()
    Loop
End Sub

Sub Macro1()
    Randomize
    rn = Int(UBound(fleTbl, 1) * Rnd + 1)
    ActiveSheet.SetBackgroundPicture Filename:=fleTbl(rn, 2)
    TimeToRun = Now + TimeValue("00:00:10")
    Application.OnTime EarliestTime:=TimeToRun, Procedure:="Macro1"
End Sub

[6StringJazzer]

I have done some research on this and there does not appear to be any built-in property that can tell you the name of the file you used to set the background. However, since you are setting the background in your code, why not just add a line of code to show the file name in a cell at the same point where you set the background?

Option Explicit

Const pth = "D:\Backgrounds\"
Const mfPttrn = "*.jpg"

Private i As Integer, rn As Integer
Private TimeToRun As Date
Private fleTbl

Sub file_names()
    Dim fle As String
    i = CreateObject("Scripting.FileSystemObject").GetFolder(pth).Files.Count
    ReDim fleTbl(1 To i, 1 To 2)
    i = 0
    fle = Dir(pth & mfPttrn, vbNormal)
    Do Until fle = ""
        i = i + 1
        fleTbl(i, 1) = i
        fleTbl(i, 2) = pth & fle
        fle = Dir()
    Loop
End Sub

Sub Macro1()
    Randomize
    rn = Int(UBound(fleTbl, 1) * Rnd + 1)
    ActiveSheet.SetBackgroundPicture Filename:=fleTbl(rn, 2)
    ActiveSheet.Range("A1") = fleTbl(rn, 2)
    TimeToRun = Now + TimeValue("00:00:10")
    Application.OnTime EarliestTime:=TimeToRun, Procedure:="Macro1"
End Sub

[Janna One Trick]

Ha yes indeed it works as intended! Thank you!

Is there a way to only show the name of the file without the extension?

It currently displays the full path and the extension:
D:\Backgrounds\name.jpg

How to make it display only "name"?

[Artik]

I have done some research on this and there does not appear to be any built-in property that can tell you the name of the file you used to set the background.

When you insert an image as the sheet background, Excel saves the file with its own internal name. Does not store the name of the source file. The image is stored in an internal folder \xl\media\ with a name such as image1.png. In the internal file \xl\worksheets\_rels\sheet1.xml.rels we find relation Id and path to image , e.g.

Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="../media/image1.png"/

In the file \xl\worksheets\sheet1.xml we find the relation:

picture r:id="rId2"

.

Artik

[6StringJazzer]

Let's try this:

Const pth = "D:\Backgrounds\"
Const mfPttrn = "*.jpg"

Private i As Integer, rn As Integer
Private TimeToRun As Date
Private fleTbl

Sub file_names()
    Dim fle As String
    i = CreateObject("Scripting.FileSystemObject").GetFolder(pth).Files.Count
    ReDim fleTbl(1 To i, 1 To 2)
    i = 0
    fle = Dir(pth & mfPttrn, vbNormal)
    Do Until fle = ""
        i = i + 1
        fleTbl(i, 1) = i
        fleTbl(i, 2) = pth & fle
        fle = Dir()
    Loop
End Sub

Sub Macro1()
    Randomize
    rn = Int(UBound(fleTbl, 1) * Rnd + 1)
    ActiveSheet.SetBackgroundPicture Filename:=fleTbl(rn, 2)
    ActiveSheet.Range("A1") = FilenameFromPath(fleTbl(rn, 2))
    TimeToRun = Now + TimeValue("00:00:10")
    Application.OnTime EarliestTime:=TimeToRun, Procedure:="Macro1"
End Sub

Public Function FilenameFromPath(path As String) As String

   Dim S As String
   Dim V As Variant
   
   ' Make sure all separators are the same
   path = Replace(path, "/", "\")
   
   V = Split(path, "\")
   FilenameFromPath = V(UBound(V))
   
End Function

[Janna One Trick]

It is strange, it gives an error:
https://i.gyazo.com/552b4d9c5ef8caa2...baff5627e1.png

[6StringJazzer]

Change this declaration as follows

Private fleTbl() As String

The way it is declared is Variant, but it is used only as a String so should be declared as String. Also, the Sub expects a String argument so now it will match.