Getting Current Directory path

[raj08536]

Hi I am trying to run this code to get current path and save to Location worksheet in B1 cell.

It worked perfectly fine couple of time but now it shows currenct path is "C:\Users\pushp\OneDrive\Documents"
Please help.

Sub Get_current_Path()

    'Variable declaration
    Dim sDir As String
  
    'Get current directory
    sDir = CurDir
   
    'Display output on the screen
    MsgBox "Current Directory is " & sDir
   
    Worksheets("Location").Activate
   
   
    Range("B1").Value = sDir



End Sub

 

[Jaafar Tribak]

Did you navigate to the Documents folder via the Open or Save dialogs ?
CurDir returns the last path you browed to.

 

[TotallyConfused]

To Jaafar Tribak:
I have never seen the 'CurDir' command, so I set up a blank workbook and pasted the original poster's code. It worked perfectly. You mentioned that 'CurDir' returns the last path used, so I went to Windows and followed my path down to '...\documents\pictures\' and loaded one of my pictures. Doing this, I assume that 'pictures' would be the last path I used. I then Alt-Tab back to the workbook (which by the way is in totally different sub-folders then the pictures) and ran the code again. It worked perfectly. So unless by Alt-Tab back to the workbook, changed the last path I was in, it would seem to me that 'CurDir' does display the current directory where the workbook is located. I'm assuming that is what the original poster wants to happen.

To Raj08536:
1 ) Are you positive that your workbook hasn't been saved in the path MsgBox displays and not in some sub-folder below '...\documents' that you think it is in?
2 ) Is there any chance you have TWO copies of your workbook, one in the sub-folder you think it is in, and ALSO a copy in the '...\Documents' folder? If this is possible, perhaps you are running the copy in your '...\Documents' folder and that is why Msgbox is displaying that path?

Unless one of the two possibilities listed above are what has happened, I have no idea what is causing your problem. Sorry! I ran your code several times and each time with perfect results.

As a side note:
1) In the future, if you are listing some VBA code, please enclose it in one of the code tabs listed at the top along with indentation. Eg. VBA, </>, etc. This makes it much easier for us to read your code, especially if you have many lines.
2) I also suggest you go through your settings and update them to show the Office Version you are using and your operating system. Sometimes code can vary according to the version of Excel you have.

I'll be following this thread to see if anyone can come up with a solution to your problem.

TotallyConfused

 

[raj08536]

I have fixed my code.

Sub Get_current_Path()

    'Variable declaration
    Dim sDir As String
  
    'Get current directory
  
    sDir = Application.ActiveWorkbook.Path

  
    Worksheets("Location").Activate
   
   
    Range("B1").Value = sDir



End Sub

 

[Jaafar Tribak]

I have fixed my code.

Sub Get_current_Path()

'Variable declaration
Dim sDir As String

'Get current directory

sDir = Application.ActiveWorkbook.Path


Worksheets("Location").Activate


Range("B1").Value = sDir



End Sub

FYI, the workbook Path Property doesn't return the current directory. It returns the name of the directory where the workbook is saved.

 

[Jaafar Tribak]

To Jaafar Tribak:
I have never seen the 'CurDir' command, so I set up a blank workbook and pasted the original poster's code. It worked perfectly. You mentioned that 'CurDir' returns the last path used, so I went to Windows and followed my path down to '...\documents\pictures\' and loaded one of my pictures. Doing this, I assume that 'pictures' would be the last path I used. I then Alt-Tab back to the workbook (which by the way is in totally different sub-folders then the pictures) and ran the code again. It worked perfectly. So unless by Alt-Tab back to the workbook, changed the last path I was in, it would seem to me that 'CurDir' does display the current directory where the workbook is located. I'm assuming that is what the original poster wants to happen.

Hi, CurDir won't change if a folder is navigated to via the UI shell like you did... CurDir changes when browsing to the folder is performed via the excel Save of Open Dialogs.

 

[TotallyConfused]

Hello Jaafar

Thanks for explaining how CurDir works and that it will be the directory that a workbook is saved to, which may or may not be the folder that the user is in at the moment. As I stated previously, this is the first time I've run across the CurDir command. It's always nice to learn something new, though Excel is so vast, I don't think I'll ever encounter all the commands, and most certainly will not be able to remember them all. You're a good teacher. THANKS.

TotallyConfused

 

[Jaafar Tribak]

Hello Jaafar

Thanks for explaining how CurDir works and that it will be the directory that a workbook is saved to, which may or may not be the folder that the user is in at the moment. As I stated previously, this is the first time I've run across the CurDir command. It's always nice to learn something new, though Excel is so vast, I don't think I'll ever encounter all the commands, and most certainly will not be able to remember them all. You're a good teacher. THANKS.

TotallyConfused

FYI, CurDir is not a function\method\command that belongs to the excel object model .. It is a vba function meaning it will also work in other vba hosts such as Word, Access etc... In fact, it is a VBA as well as a VB4/5/6 function.

 

[TotallyConfused]

Hi.
Am I wrong then in thinking that 'Application.ActiveWorkbook.Path' and 'CurDir' return the same information? Somehow that doesn't seem right, because then I have to wonder why there are two that do the same thing. It seems as if they both record the directory where a workbook was saved into. I'll need to set up a test workbook and experiment with these two.
TotallyConfused

 

[Jaafar Tribak]

No.

CurDir and Workbook.Path do not return the same information.

CurDir returns the last folder that the user navigated to from within excel ie: via the Open or Save Dialogs or via the GetOpenFilename and GetSaveAsFilename functions.

Workbook.Path returns the folder where the workbook is saved .

The folder where the workbook is saved on disk is not necessarly the current folder .