Save path name and sheet name in a cell
Good afternoon,
Guys, I need a function in VBA that I can select a file and write the file name and path to it in two cells. The file path I got, as per the code below. But the file name no, can anyone help me? For example: Cell B2 = C:User/Documents/Vendas.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
Now, thank you!
2 answers
There are some ways to accomplish this, if you already have the String with the full path of the workbook ThisWorkbook.Path, you can 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 the best way to avoid errors, and mainly because it has more functions that 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
Add the following lines in your code after the line Planilha2.Range("B2") = AbrirArquivo:
NomeArquivo = Mid(AbrirArquivo, InStrRev(AbrirArquivo, "\") + 1)
NomeArquivo = Mid(NomeArquivo, 1, InStrRev(NomeArquivo, ".") - 1)
Plan1.Range("C2") = NomeArquivo
The function InStrRev looks for a certain character in a string, back and forth.
The first line looks for the last \ and takes the file name plus the extension.
The second line looks for . and removes the extension.