Determine type of workbook

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

72,224

Office Version

1. 365

Platform

1. Windows

• Mar 6, 2017

• #2

Usually, the file extension will tell you that (if originally named properly).

Check out the Excel options here: https://technet.microsoft.com/en-us/library/dd797428(v=office.14).aspx

 

Upvote 0

S

stuartgb100

Active Member

Joined

May 10, 2015

Messages

287

Office Version

1. 2021

Platform

1. Windows

• Mar 6, 2017

• #3

Thanks.

I think I need the FileFormat property.

Regards.

 

Upvote 0

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

72,224

Office Version

1. 365

Platform

1. Windows

• Mar 6, 2017

• #4

Your question was pretty generic.

Can you explain exactly what you are trying to do?
Are you trying to get this in VBA to do something with it?
Is this part of a bigger process?

 

Last edited: Mar 6, 2017

Upvote 0

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

72,224

Office Version

1. 365

Platform

1. Windows

• Mar 6, 2017

• #5

You code use something like:

Code:

ActiveWorkbook.FileFormat

to get the File Format of the active workbook.

It will return a numerical code. Here is a document that tells you what they are: https://www.rondebruin.nl/win/s5/win001.htm

 

Upvote 0

S

stuartgb100

Active Member

Joined

May 10, 2015

Messages

287

Office Version

1. 2021

Platform

1. Windows

• Mar 6, 2017

• #6

Thanks,

I had found Ron's thread - that's what led me to fileformat.

I have a project which I wish to distribute via a template.

When a colleague opens the template (or more accurately, a copy of it), they will be
'forced' to choose a save filename and folder. Their file will then be saved with a
.xlsm file extension.

From then onwards, when user opens that file, I need to know if it's the template or a .xlsm file
so that I can control the code.

The only other way I can think, would be to strip out any specific template workbook code before
allowing the save as a .xlsm file.

Thanks again.

 

Upvote 0

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

72,224

Office Version

1. 365

Platform

1. Windows

• Mar 6, 2017

• #7

From then onwards, when user opens that file, I need to know if it's the template or a .xlsm file
so that I can control the code.

Click to expand...

You should be able to identify it by looking at the extension, like this:

Code:

    Select Case Right(ActiveWorkbook.Name, 4)
        Case "xlsm"
            MsgBox "File is macro enabled"
        Case "xltm", "xltx"
            MsgBox "File is an template"
        Case Else
            MsgBox "Something else"
    End Select

 

Upvote 0

S

stuartgb100

Active Member

Joined

May 10, 2015

Messages

287

Office Version

1. 2021

Platform

1. Windows

• Mar 6, 2017

• #8

Brilliant.

Many thanks for your time and interest.

Best Regards.

 

Upvote 0

shg

MrExcel MVP

Joined

May 7, 2008

Messages

21,836

Office Version

1. 2010

Platform

1. Windows

• Mar 6, 2017

• #9

The default action on a template is New, so myTemplate.xltm will open as myTemplate1. It doesn't acquire an extension until it's saved (myTemplate.xlsm/xlsb/csv/whatever).

 

Upvote 0

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

72,224

Office Version

1. 365

Platform

1. Windows

• Mar 6, 2017

• #10

You are welcome!

It looks like that Ron did something similar to my code at the bottom of that link, instead using the FileFormat code instead of the file extension.
But same idea...

 

Upvote 0