writing to text file

[ADK]

ThisWorkbook.Name returns the name of the file name....but with .xls

Is there a way to only return the file name with the extension?


UserName = Environ("USERNAME")
CpuName = Environ("COMPUTERNAME")
WhatOffice = Environ("USERDOMAIN")
MyFullName = ThisWorkbook.FullName
Contents:=True, UserInterfaceOnly:=True
Open "W:\PL10\PDSRlogs" & "\usage-" & ThisWorkbook.Name & ".log" For
Append As #1
Print #1, UserName, WhatOffice, CpuName, Now, MyFullName
'Print #1, Application.UserName, Now
Close #1

 

[Guest]

Maybe the Replace function can help you.

Replace(thisworkbook.Name, ".xls", ".log", 1, -1, vbTextCompare)

 

[Keith74]

Hi

This seems to do the job (assuming the last 4 chars are the file
extension)

left(thisworkbook.Name,len(thisworkbook.Name)-4)

hth

Keith

 

[Tim]

Msgbox Split(ThisWorkbook.Name, ".")(0)

Tim

 

[Dave Peterson]

Watch out if the filename contains dots.

book1.generated.by.tim.on.2007.07.13.xls

 

[Guest]

That method is slick but not foolproof: It is possible to have dots in the
filename as well as folder names. There are some file system functions you
can use

' FileSystemObject requires Microsoft Scripting Runtime Library

Public FSO As New FileSystemObject

If Right(FileName, 1) <> "\" Then FileName = FSO.GetBaseName(FileName)
FileType = "." & FSO.GetExtensionName(FileName)

....etc.

 

[Rick Rothstein \(MVP - VB\)]

Msgbox Split(ThisWorkbook.Name, ".")(0)

Watch out if the filename contains dots.

book1.generated.by.tim.on.2007.07.13.xls

This should work then...

MsgBox Left$(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".") - 1)

Rick

 

[ADK]

This one will work for what I need....thanks