[RESOLVED] VBA ThisWorkbook.Path problem-VBForums
Results 1 to 3 of 3

Thread: [RESOLVED] VBA ThisWorkbook.Path problem

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jun 2006
    Location
    Best Place on Earth
    Posts
    363

    Resolved [RESOLVED] VBA ThisWorkbook.Path problem

    I have a Workbook which writes the results to another spreadsheet which
    is saved in a folder under the one in which it resides.

    I have been using the folowing code to build up the filename for the results
    spreadsheet :-

    VB Code:
    1. ThisWorkbook.Path & "\Archive\" & ArchiveWbkName

    This was working fine, then I discovered that some of the users developed
    problems when they created a shortcut on the desktop for launching the
    Initial Workbook.

    For them the ThisWorkbook.Path is returning the path of their Desktop.
    i.e.
    C:\Documents and Settings\xxxxx\Desktop
    instead of the path where the Workbook is actually stored, i.e.
    T:\Reports\Excel\DailyUpdates

    Is there any way I can retrieve the correct Workbook's Path when someone
    launches it from a shortcut.
    Signature Under Construction

  2. #2
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,920

    Re: VBA ThisWorkbook.Path problem

    Hi Torc

    I created a workbook in C: and in the worklbook open event i put this code


    vb Code:
    1. Private Sub Workbook_Open()
    2.     MsgBox ThisWorkbook.Path
    3. End Sub

    then I created a shortcut in the desktop and opened it and it gave me the message "C:" so I believe that creating a shortcut is not the problem...

    Can you check three things for me....

    1) Right Click on the shortcut and click on the properties see where does this shortcut point to?
    2) Also it's unlikely but see if they have a 'copy' of the main file on the desktop?
    and the last thing that i can think of....
    This was working fine, then I discovered that some of the users developed
    problems when they created a shortcut on the desktop for launching the
    Initial Workbook.
    3) Check the code (use msgbox to display the result) where the file is exactly being created....

    hope this helps...
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Jun 2006
    Location
    Best Place on Earth
    Posts
    363

    Re: VBA ThisWorkbook.Path problem

    Hi koolsid,

    Turns out the unlikely second thing was the culprit.

    They had done a copy to their Desktop, instead of doing Create Shortcut.

    They gave they Icon the name Shortcut to... which is what was confusing me.

    The simple things confuse me too much.

    Thanks.
    Signature Under Construction

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.