Excel 2010 will not run through VBA created 2007

[ccampb1346]

Many thanks to jaslake for all the help in the past on this project.

I recently had to get a new computer which has Excel 2010, previous version was 2007. When I run the macro/VBA it gets most of the work done except for the create monthly summary portion it just force closes. It's put me in a bit of a bind and I've spent countless hours adjusting all settings I can find and reading online about compatibility.

Do you have any insight or know of anything that might need to be changed within the code? Since it does run most of the code it makes me think perhaps something needs to be changed for 2010 specifically within that portion which causes it to force close.

Many thanks in advance!

[6StringJazzer]

I want to reproduce this but I don't know what what to do to be able to see the problem occur, and I want to understand what problem you are seeing.

Which code are you running? How are you initiating it? What does "it just force closes" mean?

[jaslake]

Hi Charles

It'll take me a bit to get up to speed on what the Code is doing. I've changed operating systems and, although I probably have the original project somewhere on backup, finding it could be a royal pain.

I do have Excel 2010 so, please point out in what Module and where and what line of Code is causing the issue.

[ccampb1346]

On the statement tab the 'Run Forecast' button initiates the protocol to run all of the various code. On my machine it goes through all of the steps except on the last one 'create monthly summary' it creates the file titled with the month (ex. 0414) and formats it with the headings in the master spreadsheet tab '0213' but at that point when it should populate the various data it just force closes.

I do not know how to locate the error in the code.

[judgeh59]

I'm thinking the FileFormat:=51 is causing the problem....but Jazzer and jaslake bring up a good point....can you step through (F8) the Create Monthly Summary Module and find out what line is actually causing the problem?

[ccampb1346]

I'm thinking the FileFormat:=51 is causing the problem....but Jazzer and jaslake bring up a good point....can you step through (F8) the Create Monthly Summary Module and find out what line is actually causing the problem?

Once I've stepped through how do I know which line is creating the problem? Thanks!

[judgeh59]

I guess if you can step through that module the problem is not is that module....I guess I got the impression that you knew it was "forcing a close" in the Create Monthly Summary module...you can literally just keep pressing F8 until something fails and then noting what line of code that was....you can setup break points to let the code to a certain point and then step through....

[ccampb1346]

I guess if you can step through that module the problem is not is that module....I guess I got the impression that you knew it was "forcing a close" in the Create Monthly Summary module...you can literally just keep pressing F8 until something fails and then noting what line of code that was....you can setup break points to let the code to a certain point and then step through....

It closes on the Open Customer Files window, LN 70 Active Workbook.Close False

[jaslake]

Hi Charles

I can't run your Code from start to finish without commenting out some lines of Code as there is a File missing on my platform "Workbooks("Internal Summary Template.xlsx") and perhaps others. However, the premature Close of the File is caused by this Line of Code In Module OpenCustomerFiles, Sub selectfiles(spath)

Please Login or Register  to view this content.

In the Immediate window it evaluates to "Microsoft Office Excel 2007 Workbook"

That's what triggers the File Close later in the Sub. Change it in the Code...see what happens...

FYI: On my platform, the same issue is exhibited in Excel 2007

[ccampb1346]

Hi Charles

I can't run your Code from start to finish without commenting out some lines of Code as there is a File missing on my platform "Workbooks("Internal Summary Template.xlsx") and perhaps others. However, the premature Close of the File is caused by this Line of Code In Module OpenCustomerFiles, Sub selectfiles(spath)

Please Login or Register  to view this content.

In the Immediate window it evaluates to "Microsoft Office Excel 2007 Workbook"

That's what triggers the File Close later in the Sub. Change it in the Code...see what happens...

FYI: On my platform, the same issue is exhibited in Excel 2007

I found that line of code but I do not see the immediate window reference to Excel 2007? How do I find and change it?

Thanks!

[jaslake]

Hi Charles

Put a Break Point at that Line of Code. Run the Code...the Code will pause at the Line. Open the Immediate Window (View--->Immediate Window).
In the Immediate Window type----> ?file.type --->Enter.

What's it say? Change that Line of Code to whatever it says.

[ccampb1346]

Hi Charles

Put a Break Point at that Line of Code. Run the Code...the Code will pause at the Line. Open the Immediate Window (View--->Immediate Window).
In the Immediate Window type----> ?file.type --->Enter.

What's it say? Change that Line of Code to whatever it says.

Thanks so much, it is FIXED! Had to change 'Microsoft Office Excel Worksheet' to 'Microsoft Excel Worksheet'.

[jaslake]

You're welcome...glad I could help.

If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.