Excel Stats file problems

[Win2Kuser]

Hey y'all, got a bit of a problem here...

CBB has tried to run this stats file and gets loads of errors refering to:

Compile Error;
Can't find Project or Library,

It then bombs out with the debugger flagging up a function macro that calls from the main page.

This is before the file has even loaded and results in being unable to view the main page.

I think it could be down to permissions, or it's perhaps trying to load the data from here rather than there (if that makes sence).

What I need I suppose is a couple of you with Excel experience to load the file to see if you can replicate the problem and maybe shed some light on it.

The file itself works fine here, and I have tried it on several other machines without problem. If I try it on my Lappy though, it says that a copy is already open and that I should close one, or rename it - WTF! I'm not sure if the problem lies here or not.

The strange thing is that a slightly older version will work for CBB, but the internals have not really changed that much from that to the latest one, certainly not enough to make a difference with the part it crashing out on.

The file itself is geared up to use Excel 2000, but should technically run on Excel XP and Excel97.

If anyone would like to help in finding the problem, please PM me and I'll pm back a url to get it. I don't want to post a link as it's not yet ready for other teams to use yet.

Each file is 2.5 meg big, and there are 3 that need to be tested...

Any help with this would be great...

TIA

Mark.

[dannychuckle]

i can have a go - ygpm

[Win2Kuser]

tgpm back :D
Thanks.

[dannychuckle]

looks like this line (in Module1, Function name(member) As String) is the problem:

c = Mid(member, t, 1)

can't see why atm tho!

[dannychuckle]

ignore above! I got the error you reported, but then Excel says it repaired something. The log shows this:

Errors were detected in file 'C:\Documents and Settings\Danny\Local Settings\Temp\TheNewStats2.xls'
The following is a list of repairs:

Repairs were made to Query Table 'Total_44' on '[TheNewStats2.xls]Temp'.

using excel xp sp3

[dannychuckle]

its calling Microsoft Voice Text! Should it?

[Wizzard~Of~Ozz]

I think Excel XP may be the problem, Running Excel 2000 here and it works perfectly..

[Win2Kuser]

Can you actually get into the file Danny? or does the debugger keep flashing up errors?

It only seems to effect the functions for some reason. The function is called from the page Yest, on the left, the member name is name(P1)
P1 holds say "Win2Kuser", the name function goes through character by character changing any '~'s into '-'s so for example Wizzard~Of~Ozz finishes up like Wizzard-Of-Ozz as Excel doesn't like the use of '~'s and ignores any character after one of them.

The anoying thing is that the exact same file works here on several of my machines. Even Woz himself ran it, and it worked fine. The other puzzling thing is that version 10.1 that CBB can run without problem contains the exact same code and function routines.

Maybe it's the way it's saved :confused:

If I sent you a Word document, it's fair to assume that you could open it up in word and read it, so therefore an Excel file should work the same way...

[Wizzard~Of~Ozz]

this is what I get using the midnight update. (Works Perfectly)


Find a Drug Daily Team Stats XPC #2091

HTML Auto Team/Member stats Non Framed (http://win2kuser.gotdns.com/xpcstats)
HTML Auto Team/Member stats Framed Version (http://woo.gotdns.com/framestats)
Team XPC Member Info/Maps (http://woo.gotdns.com/memberinfo)

If you would like to see your name here, and help find cures for Cancer, HIV and SARS, please click here (http://www.xtremepccentral.com/forums/showthread.php?s=&threadid=6912)

Milestones:
LaurenU2: 55 Years
PresterJohn: 33 Years
Gliddend: 125 Thousand Hits
jmebonner: 4.5 Million Points
SoopaStar: 125 Thousand Hits
DannyChuckle: 7 Years
sao95: 25 Thousand Points and 200 Thousand Molecules


Today's stats for
Saturday Saturday, September 25, 2004
New New New | Total Total Total | CPU Time Week CPU
was # Member Name Jobs Hits Points | Jobs Hits Points | Yrs:days Total Rating
=== === =============== ==== ======= ======= | ===== ========== ========== | ======== ========= ======
Trimmed



New Members: Members Left?
None None

[Win2Kuser]

Originally posted by Wizzard~Of~Ozz
I think Excel XP may be the problem, Running Excel 2000 here and it works perfectly..

Was that version 10.6 you ran Woz?

10.1 is the only version that CBB can run, but the functions are identicle on both versions, the only differences are to the main form, some internal calculations, some added routines for end of week calculation, and tweaks here and there, the basic code remains the same.

This is bizzare :(

[dannychuckle]

i was gettin the errors so I cancelled out two lines that were giving the error and then went into Tools>References.

MS Voice Text flagged as missing. I unchecked that and then went back into VB and un-cancelled the two lines that I'd edited out. I'm not getting any VB errors now, but the file is still full of #N/A's.

[Wizzard~Of~Ozz]

Stats Rev 10.6 - Compiled by Win2Kuser

Yup.

[Win2Kuser]

Yeah, I managed to simulate that on my lappy. After I loaded it, it told me that another copy was open and that I had to kill one or save as a different name.

The N/A's are from the Yest page, the name function stops returning valid member names and returns N/A's

I still have yet to see why the lappy won't run it, but 6 other computers here will. Same OS, same version of Excel2000.

[Wizzard~Of~Ozz]

Could be a VB runtime, VB Programs will crash, Excel will do stupid things..

[dannychuckle]

after i unchecked 'MS Voice Text' I downloaded total.csv and ran the macro. It seemed to be working till excel crashed (I'm fairly sure that was unrelated - am still setting stuff up after moving house).

[Win2Kuser]

maybe thats it, just checked and it was unabled in the references for some reason - don't remember adding that.

I removed it, and the Microsoft scripting runtime and saved it.

The only references that are enabled are:
Microsoft Basic for Applications
Microsoft Excel 9.0 Object Library
OLE Automation
Microsoft Office 9.0 Object Library
Microsoft Forms 2.0 Object Library

It could be that the file is saved with the voice and scripts enabled, and unless they are enabled on the machine running them it bombs out...

Thanks Danny, one step closer to fixing the bloody thing :D

Maybe try downloading a fresh file and re-run it, it will have all the functions intact, and no N/A's...

[Cowboybooter]

Regarding versions, I have it running on both Excel XP and Excel 2000, mostly the same issues! :)

One continues!

:D

Bob

[dannychuckle]

I re-extracted from the zip file so it was the original file, then the error came up. I apostrophe'd out that line (whats it called when you do that with ' in VB? I keep wanting to say rem'd...):

c = Mid(member, t, 1)

and then unchecked MS Voice Text. Changed the paths in settings and then ran it and its worked perfectly :)

gotta say thats an impressive file you've got there!

[Win2Kuser]

Excellent :D

Thanks Danny.

Did you remove the apostrophe, or is it still commented out. Also, on the main page, is there any N/A's?
Thanks.

If you did a full run i.e. 'prepare for output' it will have saved that file with the new numbers, and after the sort, but not with the milestones etc.

It can be re-loaded and ran tomorrow so that the data it has now becomes yesterdays, then it brings in the new data and compares.

If you load the saved file backup, do you still get errors, or has that been fixed now that you have saved it locally?

Thanks again.

[dannychuckle]

oh yeah - went back and uncommented before I ran it! No errors on the main page - everything looked fine and the prn output looked kinda right in notepad (wasn't sure what to open it with -dreamweaver didn't like it!).

No errors when I reload the saved version- I'll download the next totals tomorrow and rerun just to check tho.

[Cowboybooter]

I'm getting there - I just got 10.6 running!:D

:)

Bob

[Win2Kuser]

Sweet thanks Danny.

When you run tomorrows, when you prepare for output, it should automatically sort by week total and save the Weekend.prn file into whatever directory you set in the settings for the main save. It will then go back and do the rest of the sort.

and e' viola, End of week top 20 :D

I haven't tested this yet though :rolleyes:

Should work though :)

Thanks again :D

[Win2Kuser]

Originally posted by Cowboybooter
I'm getting there - I just got 10.6 running!:D

:)

Bob

Nice one :D

Did you do the same as Danny?

Still can't quite put my finger on this though, but it's bound to be something simple :D

[Cowboybooter]

Okay, 10.6 completed!

I'll await the update and run again, same as Danny!

Now we have another reason for the WWW thread!:D

Danny, there is a thread called ( strangely enough ) test, in the testing forums, if you want to post your prn there tomorrow!

Fingers, legs and eyes crossed here!

:)

Bob

[Cowboybooter]

Did you do the same as Danny?

Not quite, using VB in earnest once again, I love the debuggers in it!

End result seems the same though, only time and csv update will tell!

:)

Bob

[Win2Kuser]

Did you have to change any code to get it to work?

I was thinking of creating a bit of code to do the ~ to - conversion during the import, that way, the function can be removed.

It is strange that the function would bomb out like that, it's such a simple call.

Oh well...

[Cowboybooter]

Yeah, it's completely baffling as to why it bombed out in the Function! I can use Left, Right or Mid as normal calls anywhere else, the code syntax is fine, etc etc!

I'll do a comparison of the code once it's working here, as I am intrigued to know why!

:)

Bob

[Win2Kuser]

I think it might be running that function before it has run the other functions for milestones etc, it's possible that the name is invalid N/A, so can't get the len of it.

It bombs out on t perhaps because t would be blank.

Just a guess.

Thing is, if I change it to a code routine in the import section, will it bomb out on a diferent function???

When I get more time (laughs out loud - baby coming!), I'll start converting the functions into code routines. Functions are all well and good, but they are crude and unreliable it would seem. :)

If it does it again, it might be worth bringing up the immediate window and typing:
print member & t
to see what the values are.

Might even be worth putting in a 'If member="" then end', or an 'on error' blah de blah.

The file contains no error trapping, so it might not be a bad idea to include some - I'll look into it :)

Thanks for your patience and persiverance :D

[Win2Kuser]

Talking of stats, the midnight GMT csv has just updated...

[Cowboybooter]

Downloaded!

YGPM, Win2K

:)

Bob

[Win2Kuser]

Danny and Bob:

YGPM
:D

[Wizzard~Of~Ozz]

Glad to see you got it running..

' in VB is the same as REM (which also works in batch files) they are remarks. (comments)

// in PHP is also a remark.

# is comment for comments in config files.

[Cowboybooter]

One appears to have made a little headway!

:D:D

Bob