Thursday, February 25, 2010

A couple of Excel VBA usefull notes

UserName = Environ("Username")
Domain = Environ("UserDomain")
Combined= Environ("UserDomain") & "\" & Environ("Username")

CurrentDate = Date
CurrentTime = Time

Some file and folder VBA commands

Current folder name: CurDir
Change the active folder: ChDir "C:\My Documents"

File exists test: If Dir("F:\My Documents\My Workbook.xls") <> "" Then ....

Delete a file : Kill "F:\My Documents\My Workbook.xls"

Creates a new folder: MkDir "F:\My Documents\NewPrivateFolder"
Delete a folder: RmDir

Copy a file (the file must be closed): FileCopy "OrgWorkBook.xls", "CopyWorkBook.xls"

How to move a file (the file must be closed):

OldFilePath = "C:\OldFolder\Filename.xls" ' original file location
NewFilePath = "C:\NewFolder\Filename.xls" ' new file location
Name OldFilePath As NewFilePath ' move the file

My thanks to this reference - allbeit a little hard to read beacause of the ad's

No comments: