writing to text file

A

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
 
V

Vergel Adriano

Maybe the Replace function can help you.

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

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
 
D

Dave Peterson

Watch out if the filename contains dots.

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

Charlie

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.
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top