+ Reply to Thread
Results 1 to 9 of 9

Use current directory variable in a macro

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    06-17-2007
    Posts
    113

    Question Use current directory variable in a macro

    I have this in a few macros:
    C:\Users\Taurus\Desktop\file.pdf

    Is there anyway I can replace:
    C:\Users\Taurus\Desktop

    With the current directory.

    As right now I have a problem if I use the excel file on other computers it cant find the directory.

    Cheers

  2. #2
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Re: Use current directory variable in a macro

    ThisWorkbook.Path
    will point to the path that the workbook containing the code is stored.

    & Application.PathSeparator
    can be used to navagate through folders withen the workbook path.

    Hope that makes sence,
    And help you in some way.

  3. #3
    Forum Contributor
    Join Date
    06-17-2007
    Posts
    113

    Re: Use current directory variable in a macro

    so I got this code for instance:
    Sub pdf()
    '
    ' pdf Macro
    '
    ' Keyboard Shortcut: Ctrl+p
    '
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "ThisWorkbook.Path\try - Copy.pdf", Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
            True
    End Sub
    But replacing the directory with "ThisWorkbook.Path" gives an error.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Use current directory variable in a macro

    Not quite, see below:

    Sub pdf()
    '
    ' pdf Macro
    '
    ' Keyboard Shortcut: Ctrl+p
    '
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            ThisWorkbook.Path & "\try - Copy.pdf", Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
            True
    End Sub

  5. #5
    Forum Contributor
    Join Date
    06-17-2007
    Posts
    113

    Re: Use current directory variable in a macro

    Thanks reps.
    One other thing, would I be able to replace
    \try - Copy
    with whatever is say in cell A2?

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Use current directory variable in a macro

    presumably A2 will not contain the \ , ie will contain: try - copy.pdf, in which case I would suggest:

    ThisWorkbook.Path & "\" & Range("A2").Value, Quality....

  7. #7
    Forum Contributor
    Join Date
    06-17-2007
    Posts
    113

    Re: Use current directory variable in a macro

    hmmm tried it, didnt work:
    Sub pdf()
    '
    ' pdf Macro
    
        MyPath = ActiveWorkbook.Path
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
           ThisWorkbook.Path & "\" & Range("G9").Value & ".pdf", Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
            True
    End Sub
    Iv changed to cell G9 and added .pdf as the cell wouldnt contain it, should work but doesnt.
    I get the error:
    Document not saved...

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Use current directory variable in a macro

    Did it work before you tried to use value in G9 as the file name ?

    If not, this is quite a "generic" error in so far as it could be caused by a number of issues:

    a) network problems (if not running against local drive obviously)

    b) folder permissions

    c) I've heard some people say that broken links in the file can cause issues (ie duff hyperlinks etc...)

    If it worked without issue before you used the G9 value then we'd need to know the content of G9 ... might be unacceptable file name.


    EDIT: all resolved... above moot.

  9. #9
    Forum Contributor
    Join Date
    06-17-2007
    Posts
    113

    Re: Use current directory variable in a macro

    ah ok found the problem, that cell had a "\" in it

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1