MIS Systems Thread, Macro + Excel 2007 + SIMS in Technical; Hi Guys
I've recorded a Macro in Excel 2007 and created a report for Persitent Absence to be used with ...
2nd March 2010, 12:59 PM #1
Macro + Excel 2007 + SIMS
I've recorded a Macro in Excel 2007 and created a report for Persitent Absence to be used with SIMs. Was quite happy with it, until Excel 2003 users tried to use it. It requires a PA UDG to be set up.
now i get an error 438: object doesn't support this property or method when it tries to run in Excel 2003
I cannot figure out which part of the macro excel 2003 doesn't like. Any Excel Gurus out there willing to give me some help.?
anyone. . . . anyone.... bueller...
2nd March 2010, 02:08 PM #2
Would you upload the Excel Template you're using for the report please? It will be easier to spot it that way
2nd March 2010, 04:56 PM #3
Thanks in advance for any help. Its bound to be a command that is new to 2007, but as i only record macros i really not sure whats new and whats not!
2nd March 2010, 05:00 PM #4
Don't thank me just yet lol, I'll have a look at it for you. When you get the error, which line does it highlight in the VBA code when you press debug? Should help me find the problem quicker if you know
Originally Posted by SteveB
2nd March 2010, 05:02 PM #5
It doesn't give me an option to debug unfortuntely! It stops when it highlights cell l3 though as that is where the selection is when it crashes.
2nd March 2010, 05:12 PM #6
mine fails on this line
obviously I do not have a copy of the reportdata.txt file
Workbooks.OpenText Filename:=ThePath + "\ReportData.txt", DataType:=xlDelimited, Tab:=True, FieldInfo:=ColumnArray
2nd March 2010, 05:14 PM #7
No you may not. You would need to be on a SIMS client station. The best way to see it working would be to import the rptdef into SIMS, Create a User Defined Group called PA, add a couple of kids then run it.
2nd March 2010, 05:22 PM #8
Got a bit of a hunch, try this version and tell me if it works, I haven't got a machine with 2003 handy to try it (make sure you keep a copy of your original before you use it though!)
2nd March 2010, 05:30 PM #9
eek , i afraid that has not worked. Still same issue
Last edited by vikpaw; 2nd March 2010 at 07:58 PM.
Reason: Data Protection - screenshot revealed student names
2nd March 2010, 05:34 PM #10
Argh OK that's that theory out then! OK let's enable debugging to see if we can get an idea what's causing the problem.
Open up the template, scroll down to the routine called "Sub Auto_Open()" and the next line says "On Error GoTo ErrorHandler", place a sinlge quote (') at the start of that line to comment it out, save the template and try running the eport again.
This time when you get the error you should have a 'Debug' option, which will take you back in to the code and highlight the problematic line, post a screenie here when you get there please
2nd March 2010, 05:40 PM #11
oooo getting somewhere . . .
2nd March 2010, 06:07 PM #12
looks like there are lots of elements not compatable
2nd March 2010, 07:27 PM #13
Sorry for butting in, but the data in that screen shot looks suspiciously real.
2nd March 2010, 08:35 PM #14
Excel 2003 will not support .ThemeColor as a font property no doubt. Comment the lines out by putting an apostrophe before it. This is setting a colour for the font in that range.
3rd March 2010, 08:05 AM #15
i pulled it.
Originally Posted by andy_b
By ahuxham in forum Office Software
Last Post: 18th December 2009, 11:27 AM
By wesleyw in forum How do you do....it?
Last Post: 25th June 2007, 06:39 PM
By wesleyw in forum Windows
Last Post: 22nd June 2007, 04:31 PM
By oyoung in forum Windows
Last Post: 30th March 2007, 11:27 AM
By steve in forum Educational Software
Last Post: 16th May 2006, 03:49 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)