If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

[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:

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.

Re: VBA ThisWorkbook.Path problem

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

vb Code:

PrivateSub Workbook_Open()

MsgBox ThisWorkbook.Path

EndSub

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

A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solvedMicrosoft MVP: 2011 - 2015IMP Links : Acceptable Use Policy, FAQMyGear:
ACER R7 (Win 8.1+Office 2013+VS2013) || Sony VPCCB-45FN with a Win10+Office 2010. || Mac Book Pro (10.6.8) with Office 2011