How do I get the CURRENT Filepath from Excel?

[Mister H]

Hi All:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I currently use this as a Tempfile Path when emailing a spreadsheet (Ron DeBruins code)<o></o>
<o></o>
TempFilePath = Environ$("temp") & "\"<o></o>
<o></o>
Is there a code that will simply use the Path that the Open File is in?

For example if I have the filed saved on my desktop that would be the TempFilePath, If I have it the current open file saved in H:\Docs then that would become the TempFilePath, If the current file is saved in C:\Admin\MyDocuments then that would become the TempFilePath, etc...
<o></o>
I am trying to make the code generic as some people don't have access to the path Environ$("temp") & "\"<o></o>
<o></o>
No matter what the path is of the users current open file they will have access to that folder on their computer so I can then change make the email code work for ALL users. Hope this makes sense<o></o>
<o></o>
THANKS to anyone that can assist,<o></o>
Take Care,<o></o>
Mark <?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape id=_x0000_i1025 style="WIDTH: 11.25pt; HEIGHT: 11.25pt" alt="0" type="#_x0000_t75"><v:imagedata o:href="http://www.mrexcel.com/forum/images/smilies/icon_smile.gif" src="file:///C:\DOCUME~1\HugginM\LOCALS~1\Temp\msohtml1\01\clip_image001.gif"></v:imagedata></v:shape><o></o>

 

[VoG]

Hi Mark

TempFilePath=ThisWorkbook.Path

 

[Mister H]

THANKS VoG

I will give that a try and see if it works for the users.

Have a GREAT weekend,
Mark

 

[tusharm]

{snip}No matter what the path is of the users current open file they will have access to that folder on their computer so I can then change make the email code work for ALL users. Hope this makes sense{snip}

They may have read-only access. Trying to write to a read-only folder / network share will fail.

 

[Mister H]

Hi tusharm:

THANKS for commenting. I will be emailing the file to the users and they in turn need to save it somewhere on their computer or somewhere on a shared drive. I am hoping that this will mean that wherever they save it to they will have full access meaning that VoGs suggested code should do the trick. What do you think? Do you foresee any problems using

TempFilePath=ThisWorkbook.Path

THANKS Again,
Mark

 

[tusharm]

It *should* work, at least in most cases. I have had instances where people have double-clicked the file from within their email message. In such a case, the system creates a copy of the file in some folder and opens that copy. However, the user may *not* have write priviledges to that folder (i.e., it requires an elevated priviledge to write to that folder).

Hi tusharm:

THANKS for commenting. I will be emailing the file to the users and they in turn need to save it somewhere on their computer or somewhere on a shared drive. I am hoping that this will mean that wherever they save it to they will have full access meaning that VoGs suggested code should do the trick. What do you think? Do you foresee any problems using

TempFilePath=ThisWorkbook.Path

THANKS Again,
Mark

 

[Mister H]

Hi tusharm:

THANKS for the input it is GREATLY Appreciated

I will make certain to include instructions that the user must SAVE the file to their computer and then the problem SHOULD be solved

Take Care,
Mark