run time error '438' - Object doesn't support this property or method

[Hathman]

Hi all,

I have what looks like a compatibility problem between Excel 2003 and Excel 2007. The gist of it is that I spawn data from MS Access to MS Excel and kick off an Excel macro to do a LOT of formatting. When my users have Excel 2003 it halts the formatting macro and gives them the run time error '438' - object doesn't support this property or method. If my users have Excel 2007 then it completes successfully. Where the code halts is random so I can't seem to glean any knowledge from the location it fails. I'm sure this has to do with my vba references and the fact that I need to assign different ones for different versions of Excel. How do i figure this out? I tried to take a copy of the excel spreadsheet, assign Excel 2003 (Excel 11.0) references and run that but it still failed. Maybe it's something else? Here are the references I currently have:

• Visual basic for applications
• Microsoft Excel 12.0 Object Library (I tried replacing with 11.0)
• OLE automation
• Microsoft Office 12.0 Object Library (I tried replacing with 11.0)

Any ideas?

[Bob Phillips]

You would need to show the code for us to make any headway on this.

[Hathman]

Sure thing. I didn't post this originally because it's lengthy and the crash rarely occurs in the same place (if ever). It seems to crash most frequently within the EnterMTDFormulas subroutine (at bottom) but it rarely ever crashes on the same line. This is what leads me to believe it's a 'reference' issue between the two different versions of Excel.

Code is in attached word document because for some reason the forum wouldn't accept my post with it pasted directly in.