Solved Only the Document, not the Path

[sergio vieira]

Good afternoon everyone. I have a listbox with about 30 different documents. These appear with the full path like this:
c:\Patrigest\Properties\5467-RC--36 - 0\Documents\Invoice_Receipt_Pinto Ribeiro.pdf.

What I wanted was for the result to be just:
Invoice_Receipt_Pinto Ribeiro.pdf. In other words, I only want to view the document, and not the path to it. I would appreciate your help. Thank you.

 

[Gasman]

Use InstrRev() and Right() functions.

 

[Pat Hartman]

Either create a function to extract only the file name or add a second column so you can save the values in two fields from the beginning which is the best solution.

 

[sergio vieira]

Thank you, but i'm a beginner in VBA. I don't know ito make it

 

[Gasman]

Thank you, but i'm a beginner in VBA. I don't know ito make it

Well Google those functions.
Do not expect people to create everything for you. You will learn nothing that way. :-(
Alternatively ask ChatGPT.

 

[theDBguy]

Can you post the Row Source for your listbox?

 

[DickyP]

Use the file system object.

 

[Gasman]

In fact chatgpt offers even a simpler method.

Sub ExtractFilename()
    Dim fullPath As String
    Dim fileName As String

    fullPath = "C:\Users\JohnDoe\Documents\examplefile.txt"
    fileName = Dir(fullPath)

    MsgBox "The filename is: " & fileName
End Sub

What I was thinking of

Sub ExtractFilename()
    Dim fullPath As String
    Dim fileName As String
    Dim lastSlash As Long

    fullPath = "C:\Users\JohnDoe\Documents\examplefile.txt"
    lastSlash = InStrRev(fullPath, "\")  ' Find the last backslash
    fileName = Mid(fullPath, lastSlash + 1) ' Extract the filename

    MsgBox "The filename is: " & fileName
End Sub

 

[sergio vieira]

This is the code i have in my listbox

Private Sub Form_Load()
Dim Caminho As String
Caminho = "c:\Patrigest\Imóveis" & "\" & Form_frmimovel.txtSeparar.Value & "\" & Form_frmimovel.txt1.Value

If Len(Caminho) <> 0 Then
Me.Lista6 = Trim(Caminho)
Me.Lista6.RowSource = ""
Me.Lista6.RowSource = mostrarArquivosWSH(Caminho)
End If
End Sub

The RowSource=ListValue

 

[theDBguy]

This is the code i have in my listbox

Private Sub Form_Load()
Dim Caminho As String
Caminho = "c:\Patrigest\Imóveis" & "\" & Form_frmimovel.txtSeparar.Value & "\" & Form_frmimovel.txt1.Value

If Len(Caminho) <> 0 Then
Me.Lista6 = Trim(Caminho)
Me.Lista6.RowSource = ""
Me.Lista6.RowSource = mostrarArquivosWSH(Caminho)
End If
End Sub

The RowSource=ListValue

View attachment 119281

Can we see the code for the mostrarArquivos() function, please? Also, can you tell us what is the current value in txt1?

 

[sergio vieira]

Im sorry.

Function mostrarArquivosWSH(nompasta) As String
Dim Fso As Object 'New FileSystemObject
Dim Pasta As Object 'Folder
Dim arquivos As Object 'Files
Dim arquivo As Object 'File
Dim Devolve As String
On Error GoTo mostrararquivosWSH_Err
Set Fso = CreateObject("Scripting.FileSystemObject")
Set Pasta = Fso.GetFolder(nompasta)

Set arquivos = Pasta.Files
For Each arquivo In arquivos
Devolve = Devolve & nompasta _
& "\" & arquivo.Name & ";"
Next
Set arquivos = Nothing
mostrarArquivosWSH = Devolve


Set Pasta = Nothing
Set Fso = Nothing

mostrararquivosWSH_Exit:
Exit Function
mostrararquivosWSH_Err:
MsgBox "Não existem documentos dentro da Pasta", vbCritical, "Aviso de erro"

Resume mostrararquivosWSH_Exit

End Function
'*****************************************

txt1="Documentos" (Documents"

 

[theDBguy]

Im sorry.

Function mostrarArquivosWSH(nompasta) As String
Dim Fso As Object 'New FileSystemObject
Dim Pasta As Object 'Folder
Dim arquivos As Object 'Files
Dim arquivo As Object 'File
Dim Devolve As String
On Error GoTo mostrararquivosWSH_Err
Set Fso = CreateObject("Scripting.FileSystemObject")
Set Pasta = Fso.GetFolder(nompasta)

Set arquivos = Pasta.Files
For Each arquivo In arquivos
Devolve = Devolve & nompasta _
& "\" & arquivo.Name & ";"
Next
Set arquivos = Nothing
mostrarArquivosWSH = Devolve


Set Pasta = Nothing
Set Fso = Nothing

mostrararquivosWSH_Exit:
Exit Function
mostrararquivosWSH_Err:
MsgBox "Não existem documentos dentro da Pasta", vbCritical, "Aviso de erro"

Resume mostrararquivosWSH_Exit

End Function
'*****************************************

txt1="Documentos" (Documents"

Obrigado. Try this one:

Function mostrarArquivosWSH(nompasta) As String
Dim Fso As Object 'New FileSystemObject
Dim Pasta As Object 'Folder
Dim arquivos As Object 'Files
Dim arquivo As Object 'File
Dim Devolve As String
On Error GoTo mostrararquivosWSH_Err
Set Fso = CreateObject("Scripting.FileSystemObject")
Set Pasta = Fso.GetFolder(nompasta)

Set arquivos = Pasta.Files
For Each arquivo In arquivos
Devolve = Devolve & arquivo.Name & ";"
Next
Set arquivos = Nothing
mostrarArquivosWSH = Devolve


Set Pasta = Nothing
Set Fso = Nothing

mostrararquivosWSH_Exit:
Exit Function
mostrararquivosWSH_Err:
MsgBox "Não existem documentos dentro da Pasta", vbCritical, "Aviso de erro"

Resume mostrararquivosWSH_Exit

End Function

 

[sergio vieira]

It works but it doesn't remove the complete Path.

 

[theDBguy]

It works but it doesn't remove the complete Path.

What does it show now? Can you post a new screenshot?

 

[sergio vieira]

Thank you.

 

[The_Doc_Man]

You have FileSystemObject in your code. Did you try this?

GetFileName method (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

This would be a part of your solution, perhaps.

 

[moke123]

I would assume you would want to open the file from the listbox also.
You need a list box with a couple columns and set your column count and widths to hide the path.

something like this

 

[theDBguy]

Thank you.
View attachment 119286

That's after using the code I posted?

 

[sergio vieira]

Yes. That's after using the code You posted.

 

[sergio vieira]

I would assume you would want to open the file from the listbox also.
You need a list box with a couple columns and set your column count and widths to hide the path.

something like this

I Do not work with accdb.I use access 2003. Thank you