1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel 2007 Macro Issue

Discussion in 'Business Applications' started by bacalhoun, Jan 18, 2011.

Thread Status:
Not open for further replies.
  1. bacalhoun

    bacalhoun Thread Starter

    Joined:
    Dec 28, 2001
    Messages:
    2
    I have researched my issue but other threads posted here did not solve my problem. I have an Excel 2007 spreadsheet that was first designed in Excel 2003, and it has a macro that generates 93 spreadsheets with prepopulated data. The macro works, it generates the files but then I cannot open them due to the following error:
    'Excel cannot open the file 'xxx.xlsx' because the file format or file extension is not valid.'
    I have tried changing the macro to save as .xlsm, but get the same result. I have conditional formatting that will not work in Excel 2003, plus most of the people who will be opening the file are on Excel 2007, so I don't want to save in xls format; however, I did change the macro to see if it worked for Excel 2003 and it does.

    Sure hope someone can help.

    Here is a sample from the macro:

    Range("B1:I1").Select
    ActiveCell.FormulaR1C1 = "HP Argentina"
    Range("C2").Select
    ActiveWorkbook.SaveAs Filename:= _
    "C:\Data\P2012 Templates\P2012 Country Summary and Cost Forecast - HP Argentina.xlsx" _
    , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    If the macro is being run in 2007 you should first try changing the FileFormat value that you are passing. Change the portion below.

    So that it reads

    If the workbook is a macro enabled workbook use the line below instead.

    Rollin
     
  3. bacalhoun

    bacalhoun Thread Starter

    Joined:
    Dec 28, 2001
    Messages:
    2
    Hi Rollin, the first option works very well, the macro produces the spreadsheets, and I can open them without error. Thank you for the quick reply, I really appreciate it.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/975422

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice