Save Path Name and Worksheet Name to a Cell

Question

Good afternoon,

Guys, I need a function in VBA that I can select a file and write the file name and its path in two cells. The file path I got, according to the code below. But the file name no, can anyone help me? For example: Cell B2 = C:User/Documents/Sales.xlsx Cell C2= Sales

Public Function AbrirArquivo() As String

    Dim Filtro As String
    Dim Titulo_da_Caixa As String
    Dim Arquivo As Variant

    Filtro = "Todos os Arquivos (*.*),"

    Titulo_da_Caixa = "Selecione o arquivo"

    ChDrive ("C")
    ChDir ("C:\")

    With Application

        Filename = .GetOpenFilename(Filtro, 3, Titulo_da_Caixa)

        ChDrive (Left(.DefaultFilePath, 1))
        ChDir (.DefaultFilePath)

    End With

    If Filename = False Then
        MsgBox "Nenhum arquivo foi selecionado."
        Exit Function
    End If

    AbrirArquivo = Filename
    Planilha2.Range("B2") = AbrirArquivo

End Function

From now on, thank you!

Answer 1

Add after the line Planilha2.Range("B2") = AbrirArquivo the following lines in your code:

NomeArquivo = Mid(AbrirArquivo, InStrRev(AbrirArquivo, "\") + 1)
NomeArquivo = Mid(NomeArquivo, 1, InStrRev(NomeArquivo, ".") - 1)
Plan1.Range("C2") = NomeArquivo

The function InStrRevlooks for a certain character in a string, backwards and forwards. The first line looks for the last \ and takes the name of the file plus the extension. The second line seeks the . and removes the extension.

Answer 2

There are a few ways to accomplish this, if you already have the String with the full workbook path ThisWorkbook.Path, it is possible to manipulate strings to get the desired result, as can be seen in the following code:

Dim x As String
x = "C:\User\Documents\Vendas.xlsx"

Dim nomeArquivo As String
nomeArquivo = Right(x, Len(x) - InStrRev(x, "\"))

Debug.Print nomeArquivo 'Vendas.xlsx

Dim nomeArquivosemFormato As String
nomeArquivosemFormato = Left(nomeArquivo, InStrRev(nomeArquivo, ".") - 1)

Debug.Print nomeArquivosemFormato 'Vendas

Or use the FSO (Filesystemobject), which is best suited to avoid errors, and mainly for possessing more functions which will probably be used with the file.

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim nomeArquivo As String, nomeArquivosemFormato As String
nomeArquivo = fso.GetFileName("C:\User\Documents\Vendas.xlsx")

Debug.Print nomeArquivo

nomeArquivosemFormato = fso.GetBaseName("C:\User\Documents\Vendas.xlsx")
Debug.Print nomeArquivosemFormato