Range.Merge failing (XL2013)

[AOB]

Hi there,

Bit of a head-scratcher - I have an Access database which uses Excel for reporting purposes. The database was written in Access 2007 and has been in use for a number of years without issue. For clarity, it's a straightforward split database, backend on a network drive, individual frontends on local desktops, in .accde format.

Recently, a number of users have upgraded to Office 2013 and, while the database is generally still functional, the reporting aspect is falling over. The users who continue to use Office 2007 have reported no issues

I've debugged the code on a machine with Office 2013 and discovered that the first error occurs on a line that looks something like this :

sht.Range("A1").Resize(, 2).Merge

So basically taking two adjacent cells and merging them - fairly straightforward stuff. Causes no problems in 2007, but in 2013 it throws a run-time error 1004 : "Application-defined or object-defined error"

But that's unusual because there's nothing wrong with that code? I use late-binding on my deployed code so "sht" is declared as a generic object, as is the Excel Application and any / all associated objects and constants. Plus, at the point of this particular line, Excel has long since been opened and lots of other actions have taken place - so I'm struggling to understand why this particular line would suddenly cause a problem?

Stranger still : In the deployed solution, the reporting process uses a hidden instance of Excel, so the user is spared from seeing all the background work, and then it either displays the instance at the end, or saves the finished workbook for further action, whatever that may be. While I was debugging the above, I made the Excel Application visible again (so I could confirm that everything else to that point had actually taken place, and more importantly, that the line of code in question made sense, i.e. that there was such a range available to be merged etc. etc.)

Once I had made the instance of Excel visible again, and stepped through the line above - no error! I made no changes to the code nor to the workbook it was working on (other than visibility) and suddenly there was no issue. The rest of the code ran without incident.

Anybody have any suggestions as to what on earth is going on?

Thanks!

AOB

 

[ScottR]

Looks like you have a genuine head scratcher. Out of curiosity, are you using application visibility or workbook visibility. I ask because there were changes (details of which I don't recall) to the way workbook level events and variables are handled between 2007 and 2013.

So if you are using workbook visibility you could try application visibility instead.

 

[AOB]

No it's application visibility - the entire instance of Excel is hidden, not just the workbook

I like to use a new/separate instance of Excel so the user is not prevented from working in Excel while the report is being constructed (even though it only takes a few seconds)

 

[AOB]

FYI - problem resolved by changing this :

sht.Range("A1").Resize(, 2).Merge

...to this...

sht.Range("A1").Resize(, 2).MergeCells = True

I can't explain why the first method falls over, especially given Microsoft's own documentation on that method - but the above change appears to get around the problem...