I'm writing a formatting macro that I only want to run on .txt or .csv files.
This mostly a safety feature for other users so if they accidentally press
the wrong combo of hot keys they don't have a macro run that will ruin the
spreadsheet they are currently working on.

Is there an object property I can write into the code that will return the
file type of the active workbook?

ActiveWorkbook.FileType doesn't work.

Did you try .fileformat?

Cliff Edwards

BookName = ThisWorkbook.Name
Booktype = Mid(BookName, InStr(BookName, ".") + 1)


"cla99009" wrote:

I'm writing a formatting macro that I only want to run on .txt or .csv files.
This mostly a safety feature for other users so if they accidentally press
the wrong combo of hot keys they don't have a macro run that will ruin the
spreadsheet they are currently working on.

Is there an object property I can write into the code that will return the
file type of the active workbook?

ActiveWorkbook.FileType doesn't work.

Since:

=CELL("filename",A1) in a worksheet cell will return the full path/filename,
lets use a helper cell (say Z100) to get us that extension:

Sub whatAmI()
dq = Chr(34)
s = "=CELL(" & dq & "filename" & dq & ",A1)"
ActiveSheet.Range("Z100").Formula = s
v = Right(Range("Z100").Value, 3)
MsgBox (v)
End Sub

--
Gary''s Student - gsnu200798


"cla99009" wrote:

I'm writing a formatting macro that I only want to run on .txt or .csv files.
This mostly a safety feature for other users so if they accidentally press
the wrong combo of hot keys they don't have a macro run that will ruin the
spreadsheet they are currently working on.

Is there an object property I can write into the code that will return the
file type of the active workbook?

ActiveWorkbook.FileType doesn't work.

On the off chance someone uses a period in the filename (eg
File.Name.Extension), the OP might also consider using InStrRev

BookName = ThisWorkbook.Name
Booktype = Mid(BookName, InStrRev(BookName, ".") + 1)


"Joel" wrote:

BookName = ThisWorkbook.Name
Booktype = Mid(BookName, InStr(BookName, ".") + 1)


"cla99009" wrote:

I'm writing a formatting macro that I only want to run on .txt or .csv files.
This mostly a safety feature for other users so if they accidentally press
the wrong combo of hot keys they don't have a macro run that will ruin the
spreadsheet they are currently working on.

Is there an object property I can write into the code that will return the
file type of the active workbook?

ActiveWorkbook.FileType doesn't work.

Thanks for the pointers.

The mid function doesn't work because if a new workbook hasn't been saved
yet, it doesn't have a file type extension, which would cause an error in the
macro. I ended up using If Right(ActiveWorkbook.Name, 3) < "csv" Then Exit
Sub

It works just fine now, no errors, no messes.

"JMB" wrote:

On the off chance someone uses a period in the filename (eg
File.Name.Extension), the OP might also consider using InStrRev

BookName = ThisWorkbook.Name
Booktype = Mid(BookName, InStrRev(BookName, ".") + 1)


"Joel" wrote:

BookName = ThisWorkbook.Name
Booktype = Mid(BookName, InStr(BookName, ".") + 1)


"cla99009" wrote:

I'm writing a formatting macro that I only want to run on .txt or .csv files.
This mostly a safety feature for other users so if they accidentally press
the wrong combo of hot keys they don't have a macro run that will ruin the
spreadsheet they are currently working on.

Is there an object property I can write into the code that will return the
file type of the active workbook?

ActiveWorkbook.FileType doesn't work.

glad you got it working. although if there is no period in the filename, mid
will return the entire string and not error out due to the fact that Instr
and Instrrev return 0 when they don't find the period.



"cla99009" wrote:

Thanks for the pointers.

The mid function doesn't work because if a new workbook hasn't been saved
yet, it doesn't have a file type extension, which would cause an error in the
macro. I ended up using If Right(ActiveWorkbook.Name, 3) < "csv" Then Exit
Sub

It works just fine now, no errors, no messes.

"JMB" wrote:

On the off chance someone uses a period in the filename (eg
File.Name.Extension), the OP might also consider using InStrRev

BookName = ThisWorkbook.Name
Booktype = Mid(BookName, InStrRev(BookName, ".") + 1)


"Joel" wrote:

BookName = ThisWorkbook.Name
Booktype = Mid(BookName, InStr(BookName, ".") + 1)


"cla99009" wrote:

I'm writing a formatting macro that I only want to run on .txt or .csv files.
This mostly a safety feature for other users so if they accidentally press
the wrong combo of hot keys they don't have a macro run that will ruin the
spreadsheet they are currently working on.

Is there an object property I can write into the code that will return the
file type of the active workbook?

ActiveWorkbook.FileType doesn't work.