retrieving template filename ? "Xavier" <xminet[ at ]remove-this.hotmail.com> 2/14/2007 9:53:22 PM 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

Re: retrieving template filename ? "Sharad" <s.m.naik.no-spam[ at ]gamil.com> 3/2/2007 2:31:50 PM 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. "Xavier" <xminet[ at ]remove-this.hotmail.com> wrote in message news:uo$4NKIUHHA.4756[ at ]TK2MSFTNGP06.phx.gbl... [Quoted Text] > 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 >

Re: retrieving template filename ? "Xavier" <xminet[ at ]remove-this.hotmail.com> 3/11/2007 5:56:48 PM 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 "Sharad" <s.m.naik.no-spam[ at ]gamil.com> wrote in message news:OD9zDeNXHHA.2256[ at ]TK2MSFTNGP02.phx.gbl... [Quoted Text] > 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. > > > "Xavier" <xminet[ at ]remove-this.hotmail.com> wrote in message > news:uo$4NKIUHHA.4756[ at ]TK2MSFTNGP06.phx.gbl... >> 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 >> > >

Re: retrieving template filename ? "ruic" <ehanover[ at ]tellurian.com> 3/16/2007 3:18:20 PM 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! -- Rui "Xavier" <xminet[ at ]remove-this.hotmail.com> wrote in message news:uo$4NKIUHHA.4756[ at ]TK2MSFTNGP06.phx.gbl... [Quoted Text] > 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 >