+ Reply to Thread
Results 1 to 9 of 9

XL2010 VBA Saving in .XLSM - Should be XLS

  1. #1
    Registered User
    Join Date
    07-01-2010
    Location
    Dallas, TX
    MS-Off Ver
    365 PC and Mac
    Posts
    64

    XL2010 VBA Saving in .XLSM - Should be XLS

    Hello- I'm pretty poor at VBA, but I'm hobbling along. Hope this makes sense and thx for the help in advance.

    I have a line of code that is supposed to be saving the file as an .xls file, but its saving it as xlsm:

    Please Login or Register  to view this content.
    - Thought the fileformat 56 would save as xls.

    This is in excel 2010 on a PC. Any thoughts?

    Thx!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: XL2010 VBA Saving in .XLSM - Should be XLS

    Its saving as .xlsm because the file has a macro in it - thats how 2007 onwards saves.

    If you insist/force it to save as .xlsx, it will remove the macro for you
    Last edited by FDibbins; 08-13-2013 at 06:24 PM.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    07-16-2012
    Location
    Ontario Canada
    MS-Off Ver
    Excel 2010
    Posts
    124

    Re: XL2010 VBA Saving in .XLSM - Should be XLS

    What does your variable fileName contain?
    Does it end with .xls?

    Even if FileFormat:=56 (xls), if the file name is specified with xlsm, it might save as an xlsm file.

  4. #4
    Registered User
    Join Date
    08-30-2012
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: XL2010 VBA Saving in .XLSM - Should be XLS

    Quote Originally Posted by jason_lee_91 View Post
    What does your variable fileName contain?
    Does it end with .xls?

    Even if FileFormat:=56 (xls), if the file name is specified with xlsm, it might save as an xlsm file.
    I'd also agree that if the filename has .xlsm in it then that's why it's not being renamed to .xls.

  5. #5
    Registered User
    Join Date
    07-01-2010
    Location
    Dallas, TX
    MS-Off Ver
    365 PC and Mac
    Posts
    64

    Re: XL2010 VBA Saving in .XLSM - Should be XLS

    Ugh... Bummer. Yes, the file type is indeed xlsm ... There is no way to force it to be xls or xlsx?

    In case it helps, its an XLSM file that creates a new workbook and then tries to save this new workbook in xls format.

    Thank you for all the replies. I really appreciate it.

  6. #6
    Forum Contributor
    Join Date
    07-16-2012
    Location
    Ontario Canada
    MS-Off Ver
    Excel 2010
    Posts
    124

    Re: XL2010 VBA Saving in .XLSM - Should be XLS

    just edit the name.
    Im not exactly sure how you built the string but you could even just do

    Please Login or Register  to view this content.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: XL2010 VBA Saving in .XLSM - Should be XLS

    Sorry, I mis-read your request. If you want to save it as .xls, just change the file-type to excel 97-2003 .xls

    @ jason. changing a file extension like that could cause it to become corrupt or to not open. If a 2007-onwards excel file contains VBA, it MUST have a .xlsm extension, you cannot just "change" it to .xlsx

  8. #8
    Registered User
    Join Date
    07-01-2010
    Location
    Dallas, TX
    MS-Off Ver
    365 PC and Mac
    Posts
    64

    Re: XL2010 VBA Saving in .XLSM - Should be XLS

    Thx everyone. I thought by using 'FileFormat:=56' this is telling xl to save it as 97-2003. Am I using the wrong fileformat #?

    Thanks again.

  9. #9
    Registered User
    Join Date
    08-30-2012
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: XL2010 VBA Saving in .XLSM - Should be XLS

    That's the correct fileformat type. If the new workbook name has the .xlsm in it, then just change the save name to .xls and use your current fileformat 56 type.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro that alows only saving as .xlsm or .pdf funtion
    By Taislin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-04-2013, 06:23 PM
  2. Saving XLSM as XLSX and removing buttons
    By mrmarchuk in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-09-2012, 04:15 PM
  3. Can't get xlsx and xlsm to start XL2010--only XL2007
    By DesertDude in forum Excel General
    Replies: 0
    Last Post: 07-04-2011, 09:53 PM
  4. Disabling saving in anything but XLSM
    By czaya in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-22-2011, 03:53 PM
  5. Saving an xlsm file as xls
    By ajaykgarg in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-09-2010, 09:45 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1