Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2009
    Posts
    79

    Unanswered: Issue with Workbooks Add method in VBA

    Hi All,

    I have recently upgraded to Excel 2007 version but still when I used the below code in my macro by default it opens a excel 2003 workbook which has only 65536 rows.

    Code:
    Workbooks.Add
    I have found a solution for this .i.e. if I go to Excel Options -> Save-> Save in this format I can select the format .i.e. .xlsx.

    But can we write some line of code which will take care of the above .i.e by default it will launch the excel 2007 workbook as I want to send my macro to other users and I don't want them to go and change the default workbook save option in their excel. Please help...


    Thanks a lot for your help in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The solution depends on what application is running your VBA code, system settings (such as default workbook type), etc.

    The simple answer is to call the SaveAs method after your Workbooks.Add and specify the file extension there. If the client machine supports the XLSX file format (in other words the client has Excel 2007 or later installed) you'll be able to save the file as an XLSX. If the machine does not have Excel 2007 or later installed, the SaveAs call will not hurt anything but it won't help either.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Aug 2010
    Posts
    1
    I think Just save it and install another application which can open that certain file.

  4. #4
    Join Date
    May 2012
    Posts
    1
    Hi guys,

    I came accross that issue and this is how to solve it by programming:

    Application.Workbooks.Add("C:\test\Excel2007wkb.xl sx")

    Save an empty Excel 2007 file and use it as template in workbook add function, as workbook.add always copies a template in background, and by default chose the Excel 2003 or 2007 depending on the format last used in save as. Just force the function to use a specific template (does not have to be xltx extension nor be placed in template folder)

    Thanks
    Julien

  5. #5
    Join Date
    Sep 2009
    Posts
    79
    Thanks a lot Julien for your help.

Posting Permissions

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