Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    This code (below) works fine as far as creating a file with the proper filename. When I open the file I get the Excel error message "The file you are trying to open, 'Monthly_Payroll_Expense_FTE_2010-03-04.xls, is in a different format than specified by the extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?" Followed by Y/N buttons.

    I am working in Excel 07 and trying to make the file backwards compatible for our Excel 03 users. When I open the file all appears well, even print ranges. But, when an 03 user opens the file there is only one worksheet and a bunch of gobbledegook like ASCII characters.

    How can the macro be modified to Save-As with an xls (for 97 - 2003 versions) extension so that all users can open the file?

    The macro:
    -selects a destination folder
    -begins a filename
    -concatenates a text formatted date from cell N1 to the end of the filename
    -adds ".xls" to the end of the filename as an extension
    -saves the file without user input

    Dim sFName As String
    sFName = "H:\DATA\Payroll_Monthly_FTE\Monthly_Payroll_Expen se_FTE_" & Range("N1") & ".xls"
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs (sFName)

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ron de Bruin has written a nice page on how to determine the filetype:
    http://www.rondebruin.nl/saveas.htm
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Works Great! Thanks for the tip Jan.

    I had to fumble around a bit but finally got this code to work:

    Dim sFName As String
    sFName = "H:\DATA\Payroll_Monthly_FTE\Monthly_Payroll_Expen se_FTE_" & Range("N1") & ".xls"
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs (sFName), FileFormat:=56

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •