VBA Excel get access path Solved

Question

Hello,

how can I retrieve the path of a directory and store this path "c:\folder\...." in a cell of a sheet?
Otherwise, I thought about opening the workbook, saving it as, and thereby retrieving the path and then deleting the file, but that's a more cumbersome solution. If there's a syntax that can get the filename and allow me to insert it into a cell, that would be great. I can't find anything on the subject in the forum.

Thanks to everyone for your help.
aurélie.

Configuration: Windows XP Internet Explorer 7.0

lermite222 · Accepted Answer

Good evening, To know the path of the file  dim Path as string Path = Application.Path 'or.. Range("A1").value = Application.Path See you!

aurelie76 · Answer

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

amigo · Answer

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+.

lermite222 · Answer

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!

aurelie76 · Answer

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

aurelie76 · Answer

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.

aurelie76 · Answer

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.

maestro38 · Answer

Sub PathExpole() Dim MonItem As String Dim MonPath As String Dim MonFichier As String Dim i As Integer MonItem = "C:	emp	emp2\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

sirefalas · Answer

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.

Bidouilleu_R · Answer

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

Nounours · Answer

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.

pijaku · Answer

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

VBA Excel get access path

12 réponses

Windows spotlight is not working.

Samsung j5 unable to answer

Scam of the suvedene highway

Remove 360 antivirus

My pc's screen is flickering.

Fujitsu siemens laptop won't start anymore

Wearline: what do you think?

Eko tank smartwatch

Ibiza sound reviews

Only one of my iphone headphones works. what to do?