SteveB Posted March 2, 2010 Posted March 2, 2010 Hi Guys 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...IBSS - PA Analysis - spreadsheet.zip
LosOjos Posted March 2, 2010 Posted March 2, 2010 Would you upload the Excel Template you're using for the report please? It will be easier to spot it that way
SteveB Posted March 2, 2010 Author Posted March 2, 2010 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! ExcelIBSS - PA Analysis - spreadsheet.xls
LosOjos Posted March 2, 2010 Posted March 2, 2010 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! 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
SteveB Posted March 2, 2010 Author Posted March 2, 2010 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.
mac_shinobi Posted March 2, 2010 Posted March 2, 2010 mine fails on this line Workbooks.OpenText Filename:=ThePath + "\ReportData.txt", DataType:=xlDelimited, Tab:=True, FieldInfo:=ColumnArray obviously I do not have a copy of the reportdata.txt file
SteveB Posted March 2, 2010 Author Posted March 2, 2010 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.
LosOjos Posted March 2, 2010 Posted March 2, 2010 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!)ExcelIBSS - PA Analysis - spreadsheet V2.xls 1
SteveB Posted March 2, 2010 Author Posted March 2, 2010 (edited) eek , i afraid that has not worked. Still same issue Edited March 2, 2010 by vikpaw Data Protection - screenshot revealed student names
LosOjos Posted March 2, 2010 Posted March 2, 2010 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
SteveB Posted March 2, 2010 Author Posted March 2, 2010 looks like there are lots of elements not compatable
andy_b Posted March 2, 2010 Posted March 2, 2010 Sorry for butting in, but the data in that screen shot looks suspiciously real.
ChrisH Posted March 2, 2010 Posted March 2, 2010 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.
vikpaw Posted March 3, 2010 Posted March 3, 2010 Sorry for butting in, but the data in that screen shot looks suspiciously real. i pulled it.
SteveB Posted March 3, 2010 Author Posted March 3, 2010 Thanks for eveyone's help!! I've removed all the lines that Excel 2003 did not like and now have 1/2 a macro that works. All i need to do is record the rest and tag it on. Brilliant!! As for "pulling" the image, there was no need to. If you are as familiar with SIMS as I am, you would have noticed that the names are that from the Capita Training Database, Green Abbey! Our friend Stan Acton is top of the list :p :p :p :p :p
LosOjos Posted March 3, 2010 Posted March 3, 2010 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. Chris is right, you need to go through and comment out any lines that start ".Theme", that should fix your problem (you comment lines out with the single quote as before with the "On Error" statement). If it still won't work after that, post what you have so far and I'll have another look for you EDIT: Just a few seconds too late lol. Oh well, the offer still stands if you need any more help 1
LosOjos Posted March 3, 2010 Posted March 3, 2010 the names are that from the Capita Training Database, Green Abbey! Our friend Stan Acton is top of the list :p :p :p :p :p I take it you receive your support from SMIS in Smethwick then?
SteveB Posted March 3, 2010 Author Posted March 3, 2010 No, we are the Support Unit for SIMS in Worcestershire.
LosOjos Posted March 3, 2010 Posted March 3, 2010 No, we are the Support Unit for SIMS in Worcestershire. Oh I see, I forget how far the Midlands stretches when I'm in my bubble in Sandwell lol.
SteveB Posted March 3, 2010 Author Posted March 3, 2010 Oh I see, I forget how far the Midlands stretches when I'm in my bubble in Sandwell lol. hehe, you are so far north, you must have a nosebleed . .
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now