+ Reply to Thread
Results 1 to 3 of 3

2003 opens new sheet, 2010 opens new workbook

  1. #1
    Registered User
    Join Date
    12-09-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Question 2003 opens new sheet, 2010 opens new workbook

    The setup we've got is this (I didn't write this of course far too clever for me):

    A web app gets a load of data and writes it to a file as Excel data on a shared drive. There is also a second Excel file which has several worksheets with different report layouts as well as a VBA macro file.

    The last thing the web app does is this:

    Response.Clear();
    Response.ContentType="application/x-msexcel";
    Response.AddHeader("Content-Disposition", "attachment; filename=" + localFileName);
    Response.WriteFile(fileName);
    Response.Flush();
    Response.Close();

    Where the filename is the name of the VBA spreadsheet. I think this opens this sheet but I'm not sure.

    The VBA macro is in the Auto_Open event so it runs as soon as the spreadsheet is opened.

    The formatted file is then saved on the shared drive.

    As I understand it the instance of Excel that is run is the one on the person's PC that is using the web app.

    When I try using the web app from a laptop that has Office 2010 on it, it never comes back with the spreadsheet and just hangs.

    If I open the VBA macro spreadsheet with Excel 2003 the first thing that happens is that a new worksheet called GetFile is created. The VBA then starts copying stuff from the other worksheets and only fails when it tries to open the data file.

    When I do the same thing with Excel 2010 it creates a new workbook and falls over as soon as it tries to access the other worksheets.

    I think that the same thing is happening when the web app opens the VBA spreadsheet, although I can't be sure as debug in Visual Studio won't go into Excel.

    I can't find an option in 2010 that says anything like 'open new workbook / worksheet when...'

    I've also drawn a blank on Google and the Microsoft on-line 2010 documentation.

    Is this new behaviour in 2010? A bug, a set up problem, or something else

  2. #2
    Registered User
    Join Date
    12-09-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: 2003 opens new sheet, 2010 opens new workbook

    It's got a bit clearer now thanks to a more productive debugging session.

    The failing code is this:

    Workbooks.Open Filename:="C:\Shared Documents\Book2.xls"

    ActiveWorkbook.Sheets(1).Select

    ActiveWorkbook.Sheets(1).Move After:=ThisWorkbook.Sheets(4)

    It fails on the Move statement in 2010 but not in 2003.

    Putting in MsgBox statements shows that the ActiveWorkbook is the one I want to copy from and ThisWorkbook is where I want to copy to.

    I get an error message that the move can't be done because the size of the area I want to copy from doesn't match the size of the area I want to copy to.

    So really all that web stuff is just noise - ignore it

  3. #3
    Registered User
    Join Date
    12-09-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: 2003 opens new sheet, 2010 opens new workbook

    For the enlightenment of future generations, the problem is solved if you save all the documents involved, i.e. the VBA workbook and the one that is being opened, as xlsm and xlsx documents respectively. So you open Excel 2010 and save the VBA document as a macro enabled spreadsheet and the other one as an Excel spreadsheet.

    That gets rid of that error message anyway

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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