VBA Function/Command to Retrieve File Format Enumeration Information?

[MEUserII]

I am trying to figure out if there is a VBA command/VBA Macro in Excel that from an opened Excel file will retrieve the "file format enumeration information" of the Excel file opened? Specifically, retrieve the following file format enumeration information: file format enumeration name, file format enumeration value, file format enumeration description, and file format enumeration extension?

For reference, I have linked the list of "file format enumerations" from Microsoft identifying the Excel: file format enumeration names, file format enumeration values, file format enumeration descriptions, and file format enumeration extensions:
Link: https://docs.microsoft.com/en-us/office/vba/api/Excel.XlFileFormat

For reference, I have listed below examples for what the retrieved file format enumeration information should be:

Opening the Excel file "TEST1.xls" retrieves the following information:
File Format Enumeration Name: xlExcel9795
File Format Enumeration Value: 43
File Format Enumeration Description: Excel version 95 and 97
File Format Enumeration Extension: *.xls

Opening the Excel file "TEST2.csv" retrieves the following information:
File Format Enumeration Name: xlCSVWindows
File Format Enumeration Value: 23
File Format Enumeration Description: Windows CSV
File Format Enumeration Extension: *.csv

Opening the Excel file "TEST3.xlsx" retrieves the following information:
File Format Enumeration Name: xlWorkbookDefault
File Format Enumeration Value: 51
File Format Enumeration Description: Workbook default
File Format Enumeration Extension: *.xlsx VBA Fuinction/VBA Command to Retrieve File Format Enumeration Information?

 

[RoryA]

Re: VBA Fuinction/VBA Command to Retrieve File Format Enumeration Information?

Please take a minute to read the forum rules on cross posting and then comply with them. Thanks.

 

[MEUserII]

Re: VBA Fuinction/VBA Command to Retrieve File Format Enumeration Information?

Please take a minute to read the forum rules on cross posting and then comply with them. Thanks.

Read the forum rules, specifically, Rule #13 Cross-Posting and the referenced blog. As referenced in the forum rules here is the relevant information.
Cross-Posted?: Yes
Link to Cross-Post: https://www.excelforum.com/excel-pr...ieve-file-format-enumeration-information.html

I will make this thread the primary post for this question, and update the other post that this post is the primary post since I frequent this forum more often; thank you for the information as I didn't realize there could be a shared community of users here and on other Excel forums.

For ease of consolidation, since this thread is the primary; I'll contribute here.

You’ve got all the information, so make a lookup table and read from it using the extension of the file name. There’s no native VBA function to return that information.

Yea, but here is the catch: the same file extension can have multiple file formats associated with it. For example, .csv has four file format enumerations identified by Excel: xlCSV (value: 6), xlCSVMac (value: 22), x1CSVMSDOS (value: 24), and xlCSVWindows (value: 23)

That is the issue I am stuck on; I know you mentioned there isn't a VBA function that could do this, but I am sure there is either some: VBA Macro, or VBA command, or Excel formula; that can to at least retrieve the file format enumeration value.
If at least I could retrieve the file format enumeration value then I could write a lookup table to populate the other corresponding items (file format enumeration name, file format enumeration description, and file format enumeration extension) based on the file formation enumeration value retrieved which you are right on.
Any ideas on this?

 

[RoryA]

Re: VBA Fuinction/VBA Command to Retrieve File Format Enumeration Information?

I know you mentioned there isn't a VBA function that could do this, but I am sure there is either some: VBA Macro, or VBA command, or Excel formula; that can to at least retrieve the file format enumeration value.

On what grounds are you sure of that? A CSV file for example is just a text file - it doesn't contain any special information or metadata. The csv files produced using any of those constants other than xlcsvmac are identical (the Mac version uses a different new line character, but you could only tell that by parsing the file).