Excel how to find the default file extension

0 votes

I'm trying to develop an application that uses Python and PyWin32 to create Excel files, and I'd like to allow the user to save the file using the default format and extension for the version of Excel they are currently using. The "Open XML Workbook" format, which has the ".xlsx" extension, maybe the default format, depending on the version of Excel they are using. In other instances, the ".xls" extension and standard Excel format could be used. Additionally, the user can set Excel to utilize a different default format.

Although I am aware of where to locate the default format (Application.DefaultSaveFormat), I am unable to figure out where to find the format's default extension. My file names frequently begin with periods even before the extension; for example, if the fundamental file name is "filename.BOM," the actual file name should be "filename.BOM.xls" or "filename.BOM.xlsx," depending on the format that is being used.

Everything would be great if I didn't use a double period in the file name. Therefore, Workbook.SaveAs("filename") would generate a file with the name "filename.xlsx" if the default format is "Open XML Workbook". However, when you use Workbook.SaveAs("filename.BOM"), a file with that same name is created. When Excel detects a period in the file name, it does not apply for the default extension.

My only solution is to save a temporary file, extract the extension from it, and then remove the temporary file, but that sounds like a rather kludgy workaround. Does anyone have a more effective answer?

from tempfile import mktemp
from os import path
from os import remove as delfile
class excel:
    def __init__( self):
        self.app = DispatchEx( "Excel.Application" )
    def saveas_default_ext_format( self, workbook, filename):
        # filename - file name with path but without extension

        tmpname = mktemp()

        alerts = self.app.DisplayAlerts
        self.app.DisplayAlerts = False
        workbook.SaveAs( tmpname)
        self.app.DisplayAlerts = alerts

        tmpname = self.app.ActiveWorkbook.FullName
        x, ext = path.splitext( tmpname)
        fullname = filename + ext
        workbook.SaveAs( fullname)

        delfile( tmpname)

        return fullname
Jan 5, 2023 in Others by Kithuzzz
• 38,010 points
279 views

1 answer to this question.

0 votes

Make a dict of xlfileformats:extensions and use that to do a lookup:

from tempfile import mktemp
from os import path
from os import remove as delfile
class excel:
    def __init__( self):
        self.app = DispatchEx( "Excel.Application" )
        self.dct =     {51:'xlsx',
                        52:'xlsm',
                        50:'xlsb',
                        56:'xls'
                        }

    def saveas_default_ext_format( self, workbook, filename):
        # filename - file name with path but without extension


        fullname = '.'.join((filename, self.dct[self.app.DefaultSaveFormat]))
        workbook.SaveAs( fullname)

        return fullname
answered Jan 5, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

How do I set the default paste special in excel to paste only values

I paste the values with a keyboard ...READ MORE

answered Nov 6, 2022 in Others by narikkadan
• 63,420 points
2,802 views
0 votes
1 answer

How to change the default number format in Excel?

Excel Tips, Learn Excel \ Raghu R Setting ...READ MORE

answered Dec 17, 2022 in Others by narikkadan
• 63,420 points
5,350 views
0 votes
1 answer

How to get the excel file name / path in VBA

Use FullName, for example: strFileFullName = ThisWorkbook.FullName ...READ MORE

answered Dec 23, 2022 in Others by narikkadan
• 63,420 points
354 views
0 votes
1 answer

EXCEL-Function to find how many people does the oldest female live with

You can try something like this: =ROWS(FILTER(B11:B9291, T11:T9291=MAXIFS(T11:T9291,G11:G9291,2))) It ...READ MORE

answered Jan 13, 2023 in Others by narikkadan
• 63,420 points
418 views
0 votes
2 answers
+1 vote
2 answers

how can i count the items in a list?

Syntax :            list. count(value) Code: colors = ['red', 'green', ...READ MORE

answered Jul 7, 2019 in Python by Neha
• 330 points

edited Jul 8, 2019 by Kalgi 4,023 views
0 votes
1 answer
0 votes
1 answer

How to find out how many rows and columns to read from an Excel file with PHPExcel?

Solution: $file_name = htmlentities($_POST['file_name']); $sheet_name = htmlentities($_POST['sheet_name']); $number_of_columns = htmlentities($_POST['number_of_columns']); $number_of_rows ...READ MORE

answered Oct 23, 2022 in Others by narikkadan
• 63,420 points
6,650 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP