retrieving template filename ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Xavier - 14 Feb 2007 21:53 GMT

Hello,

I currently face an (apparently) odd issue. Googling was of no help.

I do have an order form template. When I create a new Excel workbook based
on this template, I would need to retrieve (with VBA) the filename of the
template. I wonder if there is any built-in function allowing me to do this
? Just to give you an example:

\\myserver\myshare\templates\orderform.xlt (right-click & New) ->
c:\something\order1.xls: I need some code in order1.xls that would return me
"\\myserver\myshare\templates\orderform.xlt".

Any help or pointer would be appreciated,

Xavier

Reply to this Message

Sharad - 02 Mar 2007 14:31 GMT

Hi Xavier,

I too once had to do it and could not find a way.
So I used a work around as under:

Added code in the template itself in before save event,
if the name of the file being save is .xlt, then it would write the name of
the file (template) in a defined cell.
Then during run time you can retrive the template name from that cell.

> Hello,
>
[quoted text clipped - 12 lines]
>
> Xavier

Reply to this Message

Xavier - 11 Mar 2007 18:56 GMT

Hi Sharad,

Thanks a lot for your suggestion. Unfortunately, in my case it won't help
me: I want to detect if a user works with a (local) copy of the template of
with the original one. If I follow your suggestion, a local copy will
contain the location of the original template until the local copy is opened
and saved.

Thanks again for your suggestion.

Xavier

> Hi Xavier,
>
[quoted text clipped - 22 lines]
>>
>> Xavier

Reply to this Message

ruic - 16 Mar 2007 16:18 GMT

How about this function:

Function FileOrFolderName(InputString As String, ReturnFileName As Boolean)
As String
' returns the foldername without the last pathseparator or the filename

Dim I As Integer, FolderName As String, FileName As String

I = 0
While InStr(I + 1, InputString, Application.PathSeparator) > 0
I = InStr(I + 1, InputString, Application.PathSeparator)
Wend
If I = 0 Then
FolderName = CurDir
Else
FolderName = Left(InputString, I - 1)
End If
FileName = Right(InputString, Len(InputString) - I)
If ReturnFileName Then
FileOrFolderName = FileName
Else
FileOrFolderName = FolderName
End If
End Function

The feeding it Application.DefaultFilePath for the InputString!

Signature

Rui

> Hello,
>
[quoted text clipped - 12 lines]
>
> Xavier

Reply to this Message