VBA Excel get access path

Hello,

The .FullName property returns the full path of the ActiveWorkbook.Name
The .Path property returns only the folder path

Sub macro3() Dim chemin As String 'Sheets("feuil1").Select chemin = Workbooks(ActiveWorkbook.Name).FullName MsgBox "Full path " & chemin chemin = Workbooks(ActiveWorkbook.Name).Path MsgBox "Folder Name " & chemin Sheets("feuil2").Select Range("C1").Value = chemin End Sub

Choose which of the two paths you want to keep.

A+.

Hello,
I'm looking to do something similar to what is indicated here.
I would like to retrieve a file path, but not from the file I'm working on, rather from any file, by selecting it through a file explorer window.

I'm being quite specific because I had found it before but can't seem to locate it again.

Here is an example,

the use of Application.GetOpenFilename is the main function
the rest is string manipulation.

Everything is broken down here, it can be useful.
but we can simplify.

Sub test2() Dim chemin As String Dim nomFichier As String Dim cheminComplet As String Dim dummy As String cheminComplet = Application.GetOpenFilename If cheminComplet <> "false" Then ' 1 the extension dummy = cheminComplet While Right(dummy, 1) <> "." ext = Right(dummy, 1) & ext dummy = Left(dummy, Len(dummy) - 1) Wend dummy = Left(dummy, Len(dummy) - 1) ' here we eliminate the . ' the file name While Right(dummy, 1) <> "\" nomFichier = Right(dummy, 1) & nomFichier dummy = Left(dummy, Len(dummy) - 1) Wend ' the path chemin = dummy End If End Sub

Sub PathExpole() Dim MonItem As String Dim MonPath As String Dim MonFichier As String Dim i As Integer MonItem = "C:\temp\temp2\fichier.xls" i = InStr(1, StrReverse(MonItem), "\", vbTextCompare) If i <> 0 Then MonPath = Left(MonItem, Len(MonItem) - i) MonFichier = Right(MonItem, Len(MonItem) - i - 2) End If MsgBox MonPath & " - " & MonFichier End Sub

Hello,

10 years later...

Function Choix_Repertoire() As String Dim Fd As FileDialog Set Fd = Application.FileDialog(msoFileDialogFolderPicker) Fd.Show On Error Resume Next Choix_Repertoire = Fd.SelectedItems(1) End Function

Calling code:

Dim rep As String rep = Choix_Repertoire If rep <> vbNullString Then MsgBox rep Else MsgBox "User cancellation" End If

--
Best regards,
Franck

To get the result in a cell without using macros, it's a bit complicated but it works.

CELL("filename") gives the path + the file name + the extension and + or - the sheet name

Result: C:\Excel\[test.xls]Testsheet or C:\Excel\test.xls
I believe this depends on the versions.

The goal afterwards is to process to retrieve the path.

In either example, YOU MUST KNOW THE FILE NAME

For example 1:
LEFT(CELL("filename");SEARCH("[";CELL("filename"))-1)
This gives C:\Excel\

For example 2:
LEFT(CELL("filename");SEARCH("test.xls";CELL("filename"))-1)
This gives C:\Excel\

And if we want it to work for both examples:

IF(ISERROR(SEARCH("");CELL("filename")));LEFT(CELL("filename");SEARCH("test.xls";CELL("filename"))-1);LEFT(CELL("filename");SEARCH("[";CELL("filename"))-1))

There you go.

Hello Lermitte222,

Actually, I rushed a bit, here is my piece of code, and I can't insert the path of the opened file into a cell of another workbook:

Private Sub CommandButton1_Click() Sheets("feuil1").Visible = True Dim chemin As String Sheets("feuil1").Select chemin = Workbooks(ActiveWorkbook.Name).FullName Sheets("feuil2").Select Range("C1").Value = chemin ' or Range("C1").Value = Workbooks(ActiveWorkbook.Name).FullName

and it doesn't put anything in C1?

Thank you for your help,

Aurelie

Hello,
I understood better..

Sub ChChPath() Dim path As String path = ThisWorkbook.FullName Sheets("Sheet2").Select Range("C1").Value = path 'or.. Range("C1").Value = ThisWorkbook.FullName End Sub

See you!

Thank you Lermitte, I've given up, really, I'm not getting anywhere, I've turned to another solution, but the same, I'm facing a problem that seems to annoy more than one. The famous copy of a sheet from workbook X and paste the sheet onto workbook Y by coding in VBA... it still seems like a crazy thing........ thank you very much for the time you've spent but I don't want to abuse.
kisses and thank you, Aurelie.

Hello again,

normally with this code I should be able to write the path in cell C1 of sheet 2 and I have nothing, frankly, I've twisted the code every which way and I don't understand? I don't know if it works for you? I'm on Excel 97, could it be because of that?

Thanks anyway for the time you are giving me.

aurelie

Lermitte222,

with the step-by-step mode, it displays the path in the tooltip but doesn't show anything, I think there's an error 1004 issue, even when making a copy of the file to then extract the path, it doesn't work, it seems more complicated than I thought! Sorry for the trouble you're going through.

aurelie.