+ Post New Thread
Page 1 of 2 12 LastLast
Results 1 to 15 of 21
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 ...
  1. #1
    SteveB's Avatar
    Join Date
    Jun 2008
    Location
    Midlands
    Posts
    131
    Thank Post
    19
    Thanked 4 Times in 4 Posts
    Rep Power
    13

    Macro + Excel 2007 + SIMS

    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...
    Attached Files Attached Files

  2. #2

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,578
    Thank Post
    1,473
    Thanked 1,227 Times in 835 Posts
    Rep Power
    753
    Would you upload the Excel Template you're using for the report please? It will be easier to spot it that way

  3. #3
    SteveB's Avatar
    Join Date
    Jun 2008
    Location
    Midlands
    Posts
    131
    Thank Post
    19
    Thanked 4 Times in 4 Posts
    Rep Power
    13
    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!
    Attached Files Attached Files

  4. #4

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,578
    Thank Post
    1,473
    Thanked 1,227 Times in 835 Posts
    Rep Power
    753
    Quote Originally Posted by SteveB View Post
    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

  5. #5
    SteveB's Avatar
    Join Date
    Jun 2008
    Location
    Midlands
    Posts
    131
    Thank Post
    19
    Thanked 4 Times in 4 Posts
    Rep Power
    13
    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.

  6. #6

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,946
    Thank Post
    3,479
    Thanked 1,095 Times in 1,008 Posts
    Rep Power
    372
    mine fails on this line

    Code:
    Workbooks.OpenText Filename:=ThePath + "\ReportData.txt", DataType:=xlDelimited, Tab:=True, FieldInfo:=ColumnArray
    obviously I do not have a copy of the reportdata.txt file

  7. #7
    SteveB's Avatar
    Join Date
    Jun 2008
    Location
    Midlands
    Posts
    131
    Thank Post
    19
    Thanked 4 Times in 4 Posts
    Rep Power
    13
    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.


  8. #8

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,578
    Thank Post
    1,473
    Thanked 1,227 Times in 835 Posts
    Rep Power
    753
    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!)
    Attached Files Attached Files

  9. Thanks to LosOjos from:

    SteveB (2nd March 2010)

  10. #9
    SteveB's Avatar
    Join Date
    Jun 2008
    Location
    Midlands
    Posts
    131
    Thank Post
    19
    Thanked 4 Times in 4 Posts
    Rep Power
    13
    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

  11. #10

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,578
    Thank Post
    1,473
    Thanked 1,227 Times in 835 Posts
    Rep Power
    753
    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

  12. #11
    SteveB's Avatar
    Join Date
    Jun 2008
    Location
    Midlands
    Posts
    131
    Thank Post
    19
    Thanked 4 Times in 4 Posts
    Rep Power
    13
    oooo getting somewhere . . .
    Attached Images Attached Images

  13. #12
    SteveB's Avatar
    Join Date
    Jun 2008
    Location
    Midlands
    Posts
    131
    Thank Post
    19
    Thanked 4 Times in 4 Posts
    Rep Power
    13
    looks like there are lots of elements not compatable

  14. #13

    Join Date
    Apr 2007
    Location
    Birmingham
    Posts
    143
    Thank Post
    6
    Thanked 45 Times in 39 Posts
    Rep Power
    23
    Sorry for butting in, but the data in that screen shot looks suspiciously real.

  15. #14
    ChrisH's Avatar
    Join Date
    Jun 2005
    Location
    East Lancs
    Posts
    4,995
    Thank Post
    120
    Thanked 286 Times in 263 Posts
    Rep Power
    108
    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.

  16. #15

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,820
    Thank Post
    734
    Thanked 1,448 Times in 1,201 Posts
    Rep Power
    361
    Quote Originally Posted by andy_b View Post
    Sorry for butting in, but the data in that screen shot looks suspiciously real.
    i pulled it.



SHARE:
+ Post New Thread
Page 1 of 2 12 LastLast

Similar Threads

  1. [MS Office - XP] Excel Macro - If Cell Value
    By ahuxham in forum Office Software
    Replies: 0
    Last Post: 18th December 2009, 11:27 AM
  2. Excel 2007
    By wesleyw in forum How do you do....it?
    Replies: 7
    Last Post: 25th June 2007, 06:39 PM
  3. Excel 2007
    By wesleyw in forum Windows
    Replies: 0
    Last Post: 22nd June 2007, 04:31 PM
  4. help with excel macro needed
    By oyoung in forum Windows
    Replies: 0
    Last Post: 30th March 2007, 11:27 AM
  5. Excel Macro - Edexcel GCE Exams
    By steve in forum Educational Software
    Replies: 0
    Last Post: 16th May 2006, 03:49 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •