[AccessD] New excel workbooks

John Colby jwcolby at ColbyConsulting.com
Sun Jan 22 16:59:27 CST 2006


Thanks, I used them! 


John W. Colby
www.ColbyConsulting.com 


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Saturday, January 21, 2006 7:01 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] New excel workbooks

On 21 Jan 2006 at 14:01, John Colby wrote:

> Actually, after testing I have determined that workbooks are stored 
> using the file name minus the path.  Thus X:\test.XLS would create a 
> workbook that you could access using app.workbooks("test.xls").  This 
> is a little inconvenient since it means that if I need to access a 
> workbook in the collection by name, I need to have code that strips 
> out the filename, so I can feed that in.  Nothing beyond my ability or 
> anything, just extra work to handle it.
> 

To save you a bit of time, here are my standard filename functions:

Function Directory(Fullpath As String) As String Directory = Left$(Fullpath,
InStrRev(Fullpath, "\")) End Function Function FileName(Fullpath As String)
As String
    FileName = Right$(Fullpath, Len(Fullpath) - InStrRev(Fullpath, "\")) End
Function Function FileRoot(FileNm As String) As String If
InStr(FileName(FileNm), ".") > 0 Then
    FileRoot = Left$(FileNm, InStrRev(FileNm, ".") - 1) Else
  FileRoot = FileName(FileNm)
End If
End Function
Function FileExt(FileNm As String) As String If InStr(FileName(FileNm), ".")
> 0 Then
    FileExt = Right$(FileNm, Len(FileNm) - InStrRev(FileNm, ".")) Else
    FileExt = ""
End If
End Function--
Stuart


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list