Obtain/Return FilePath/Name in VBA

  • Hi


    Does anyone know the code for obtaining the filepath of the current workbook? Tried a search and can't find anything on this.


    Many thanks
    Johno

  • Re: Obtaining filepath using VBA


    Depends on what you mean by "current". For the path of the workbook that is running the code use:

    Code
    ThisWorkBook.Path


    and for the active workbook in the workspace use

    Code
    ActiveWorkBook.Path

    Cheers, Glenn.


    Beauty is in the eye of the beer-holder.

  • Re: Obtaining filepath using VBA


    hi John,


    how about:

    Code
    MsgBox ThisWorkbook.Path

    Cheers
    ___________
    Xlite :sheep:
    [st]All you need to learn VBA is an internet connection and Ozgrid.com[/st]

  • Re: Obtaining filepath using VBA


    Hi


    What I am trying to do is save the workbook under a different name, "new name". But I need the workbook to be saved in the same folder (or destination) as the old one, but with a different name. Also, I don't want the filepath to be part of the new filename.


    The code I have so far is shown below.


    Code
    Dim newWB As workbook
    Dim newfile As String
    
    
    newfile = "new name"
    
    
    Set newWB = activeWorkbook
    newWB.SaveAs Filename:=(newfile), FileFormat:=xlNormal


    Thanks to anyone that can help


    Johno

  • Re: Obtaining filepath using VBA


    how about something like...

    Code
    newfile = activeworkbook.path & "\new file.xls"

    D ;)


    :sailboat: [SIZE=1]Better a bad day on the water than a good day in the office[/SIZE]

  • Re: Obtaining filepath using VBA


    Quote from DaveR (UK)

    how about something like...

    Code
    newfile = activeworkbook.path & "\new file.xls"


    Better to use system info


    Code
    newfile = activeworkbook.path & Application.PathSeparator & "new file.xls"

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!