JnF's SWGC Tool v1.70.02
 

If one of the moderators would sticky this, it would be much appreciated.


JnF's SWGC Tool v1.70.02 - link deleted. Files will no longer be available after 8/20/05 so that I may use the online space for other projects.

Open the SWG_Stuff folder and Select JnFs_SWGCTool_1-70-02.zip from the file list.


1.66.91 - Made several modifications to speed up the Schematics worksheet, and fixed some misspellings. Also added a Factory and Harvestor calculator that I have been using for nearly a year now; it is based very closely on the calculator used here on SWGCraft website.

There is also the beginnings of a resource pricing calcuator that bases off of the current resource stats as compared against the defined Stat Caps. This is configurable by the user entries of Max CPU you would ever consider charging and with 3 ratio settings that look at how many stats are in the 90% range and the 95% range.


1.70.01 - Added support for up to 9 ingredient lines in Schematics. This was mainly for Master ShipWright schematics. Revamped the SWGCraft Resource Data Import so that last line of data was not skipped (Thanks to Sloejack for posting that code in another thread). Added ability for user to configure the number of resource ranks that can considered in Schematics (anything over 5 *will* slow down the tool however). Added User configuration options and on-off toggle for new resource pricing module. Fixed rounding problem on Theoretical Maximums on WTH Today sheet. Some performance tweaking.

1.70.02 - Bug Fixes. Auto Stock Update function. Updated Theoretical Maximums on resource classes to take into account the JtL resource stats.

Possible Future Updates:

1) More performance tweaks. Biggest concern is the Ranking page.

Please check the operation of the tool closely, especially on schematics. Since I added some new columns to several worksheets, I am concerned I might still have some cell references that need to be updated. Also please let me know about any areas that still seem like slow performers. There is one area that I know of for sure and that is the Resource Sort function; there is a sub-program that is called twice and I have not yet tracked down why that is necessary.

Constructive Feedback welcome. :cool:

Nice work
 

I will try the new tool out tomorrow when I wake up.
ZG

Hi, great tool, iv been using it since you released this version and its great, will be watching this tool closely :). Yes i know its not yours, i ment overall its a great tool and wish to see what you add to make it even better.

Thanks....however Fooman, Jenner and Sloejack get 99% of the credit. I'm just trying to polish a few areas of the tool at this point; it was already in darn good shape. I picked this up as a good way to get my Excel and VBA skills back up to snuff.

I'm looking at what Sloejack just posted in the other thread for the Resource download and will get that into place before I release 1.70. Maybe by tomorrow...

Feb. 4, 2005 - V1.70.01 is available for download. Link is in first message of this thread.

New Feature Idea
 

Strongbow,
Two ideas/questions:

1. How hard would it be for the results of the Generate Summary button on the WTH Summary sheet be listed in descending order of the time the resource has been in shift? For example, the oldest resource in the summary is at the top and the newest is at the bottom? I can do it manually with the sort descending button, but was thinking it might be just a few more lines of code to add it to the generate macro.

2. I would like to be able to create another summary sheet (using a macro) that is just a copy of the what to harvest today sheet except the only rows that are copied are ones that have resources that are better than mine. No data manipulation would be required, just a straight copy of the entire row to another sheet.

Regards,
ZG

Fairly simple....to make it useful for everyone, how about adding a combo box that lists options for Resource Name (ascending) (Default Sort), Days In (ascending), Days In (descending)? Generate Summary would put the items in the selected order when it completes and then user can change that option and press a "Apply Sort" button



Not sure how hard this would be. Scan thru the resource names on WTH Summary, then link over and all instances on WTH Today and check the both the Primary and Secondary Experimental Values. As soon as you find a match where the new resource exceeds those values, copy the line over to the new sheet (called "My WTH Summary" ,maybe). I have the tool set to also show less 'desireable' resources in cases where my inventory on my current stuff gets too low; should that be taken into account as well and copy those entries over too?

Or...how about not create a new sheet and simply place a marker beside those rows in WTH SUmmary that are better than your current stock (or use conditional formatting to change the resource name color or something similar)? I think this can more easily be done as the Summary is generated to begin with.

hi, i have been having problems with the original lately, for some reason the sheet works fine for about 5 days then all of a sudden whenever i try to update the resources it says you have the latest file even though i know that i havnt got the latest, i dont know if this is fixed in the latest version but if its not this bug is very annoying for me.

Try F9
 

Sometimes that happens to me. If you hit F9 to do a manual calculation, that may fix the problem.
ZG

I've not seen this problem yet.

I do not have the spreadsheet here at work, but if you unhide the worksheets there is one that has the filenames for the downloads for each galaxy. At the top of the sheet there are 2 timestamp fields. delete the contents of the one on the right, then try downloading again.

Also try re-calculating using the F9 key. It is very conceivable that a process you are using is leaving the calculations in manual mode. If you can give me a run down of the last activities you were performing before you got this error, it might help.

I love the speed mod's you made to the tool, its much much faster pulling up schematics now!

I'm having some strange problems since switching. I backed up my resources, unzipped your version of the tool, and restored them. Now when I update the available resources from the control panel my WTH screen doesn't show the days available. Instead I get a big #REF right there. I also can't update my resources from schematic screen, when I do a factory run. I just get an error. Did I do something wrong ?

Something possibly related is a warning message saying a formula is unprotected on my resource screen. I've deleted the tool and installed it twice, restoring from the same backup file, and get the same error every time I download the new resources. Any suggestions as to what I can do to fix this are appreciated! Keep up the awesome work!

This thread is in the wrong place...
 

Strongbow,
This thread (for version 1.70.01) ended up under the v1.66.91 thread, making it hard to find for people. Perhaps a moderator can move it or may need to post again in a brand new thread.
ZG

I just tried to download this version and it doesn't seem to be working for me. I get the Yahoo page, but when I click on SWG_Stuff I get a 'The page cannot be displayed" message.

This problem is caused by this formula in cell X3:

=IF(ISNA(VLOOKUP(U3,#REF!$T$9:$AX$1500,31,FALSE)), "", VLOOKUP(U3,#REF!$T$9:$AX$1500,31,FALSE))

If you paste this formula into cell X3 instead and then copy it all the way down the column it should fix it:

=IF(ISNA(VLOOKUP(U3,SWGCparsed!$C$9:$AG$1500,31,FA LSE)),"", VLOOKUP(U3,SWGCparsed!$C$9:$AG$1500,31,FALSE))

Regards,
ZG

P.S. There is no space is the word FALSE in the first half of the formula, that is a display error that I can't seem to fix in using the edit button on this message.

Awesome! Thank you very much, I'll give it a shot.

Nice to see others after me, Jenner, Sloejack are keeping the tool alive!

Fooman

Thanks Fooman. I'll try to do my predescesors justice. BTW, is there a way to change the overall title of this thread?

Dexeter, apparently I missed a reference when I copied the new sheets from my working version into the live version. I'll get that updated post haste and replace the downloadable version. ZG's post pretty well nailed it down and I will see if there are any other culprits.

Currently I am working on the GrindResources function. As I have stated in other posts, I noticed a problem in the ShipWright Schematics where my 997 OQ Lube Oils were not being considered in the Inert Petrochem slots on ships. Since they do not have all 4 stats specified by the schematic, Lube Oils are ignored because the result of the calculations ranks them too low. This is a problem in how the weighting routines are calculated, and if it is happening here, I am fairly confident it is happening elsewhere too. It is simply taking me a while to map out this function since it is also recursive in nature. I probably would have had it done yesterday if it were not for unplanned maintenance on our systems at the office that I had to supervise for 14 hours.

RawlingSC - I was able to pull up the download file from one of my anonymous accounts a few minutes ago, so the page is working now. Perhaps Yahoo was doing some maintenance or something.

Thanks for the quick response. I tried making the changes myself and I guess botched something up. I'm eagerly awaiting the updated zip on your website. I may have something configured wrong in Excel, I don't know very much about it (I haven't changed any of the default settings though). I can't download the new CSV file now, when I used to download it with no problems. Excel keeps coming up with a Stop message (or error?) Perhaps its a sign that I need to not touch the crafting tool until I can download a fixed version ? :)

I've updated the file and it is available for download now.

I'm embarassed to say that this bug was staring me in the face in my copy of the tool and I never saw it. I'll have to fire my QC department (me <G>)

dont know if its me but can anyone else confirm that the schematic for blast layer is missing low grade ore from the schematic ? great tool and the new additions are very handy :) nice work

I just downloaded the latest version. I set up to make a run of 5 vibro knucklers. The first thing I noticed is when I hit the update stock button the number for the factory run isn't populated anymore. I put the 5 in it and hit the update stock now button and got a run time error 9, subscript out of range. I haven't dug into it yet, but may get a chance at some point if you don't find it first.

Nice catch on this formula mistake :
=IF(ISNA(VLOOKUP(U3,SWGCparsed!$C$9:$AG$1500,31,FA LSE)),"", VLOOKUP(U3,SWGCparsed!$C$9:$AG$1500,31,FALSE))

However if you use :
=IF(ISERROR(VLOOKUP(U3,SWGCparsed!$C$9:$AG$1500,31 ,FA LSE)),"", VLOOKUP(U3,SWGCparsed!$C$9:$AG$1500,31,FALSE))

instead it will make a blank cell in the lines that dont currently have any resources in spawn...just to give it a cleaner look.

STRONGBOW....Thank you for picking up and revising this tool!
/bow

einstein

I downloaded the newest fix, and I don't have the #ref errors anymore. Something weird happens when I try to download the newest .csv file for me. It recognizes that a new version is available, which I download. Once the file has been downloaded, the processing starts, and then I get "stop" messages with an option to hit ok. This happens repeatedly until the program is done with whatever its doing. Does this happen for anyone else? I'm not sure if the import was ever completed or not.

Cute...I left one of my conditional breakpoints in place. All that does is throw a message box up when a certain condition is reached; I used this to stop processing aftera certain condition is reached so I could go into break mode and step thru the processes manually.

Hit OK and it will continue processing normally (unless that condition arises again). I'll re-update this when I get home tonight.

Corrected version is up now

I downloaded the latest file last night. When I try to download the current resources, I get an error. I click on the 'Download now' button then the spreadsheet switches to the SWGC Import page and a dialog box pops up with the following message:

An error occured while reading the data-file.


Any ideas? I'm not much of a VB guy, but the message leads me to believe its having difficulty reading the downloaded file.

Did you set up a directory for the download and point the tool at that directory from the Control Panel?

FYI...according to the latest download for Intrepid, the new Theoretical Max for SR in Copper is 1000. This is due to the JtL Copper stats. We have one in spawn now that has 988 SR

Yes, I did. I used the browse button to point to an already existing directory.

edit - btw, not sure if it makes a difference in helping think of answers, but I have been using a previous sloejack version.

Which Galaxy are you in? I will set my tool to that to see if it is specific to that.

I'm on Kettemoor

Ok..this is somewhat troubling. I copied the file to another machine and it ran fine. However, an earlier version of this spreadsheet still works ok on the first machines. I've rebooted and tried again.

Anyone have any ideas?

Thanks in advance.

I've not found a problem with the program, in relation to your galaxy.

Backup your data, pull a fresh copy of the tool and restore your data to that.

I would also try creating a new download directory and pointing the tool to that. (try this first)

If still having a problem then I will ask that you zip up your tool, complete with data, and e-mail it to me so I can debug first-hand.

Strongbow,

I can send you my file if you wish, but I'm not sure that will prove anything since I put the same file on a different machine and it worked on that machine. The machine that doesn't work is Office 2000 while the machine that does work is Office 2003. Could that be anything?

The currentresource file does get downloaded on the machine that has problems. I can open it up within Excel, just not when the VB script is calling it for some reason.

I sent you a PM.

OK, then it could very well be the changes made to the import (in order to import all lines of the download file) went beyond the capabilities of Office 2000. I know this version will not work with Office 97 (as stated on Tech Support worksheet by previous programmers). I run Office 2003, so it is not possible for me to check it on earlier versions.

As an amusing note, it doesn't work in OpenOffice either. Aside from the fact that I doubt OpenOffice supports VBA (big surprise, eh?), it apparently exceeds OO's row count limitations (!), so you can't even read the entire file.

[edit; fixed lame thinko]

--S

"An error ocurred while reading the data-file"
 

Hello there friends,

I just downloaded this tool to give it a try and when try to download the current resources I get the error mentioned in the subject line. Does anyone know why and how to work around it or fix it?

Thanks in advance

Zad MacLeod
Master Weaponsmith
Gorath

I have another problem to report, hopefully Strongbow isn't tired of hearing from me yet. :) When I report something I can't help but wonder if I'm doing something wrong, operator error is a killer. The previous errors I've had have all gone away. I try to use the schematic screen to pick a weapon to make, and show the best resources, and it just shows a list of the weapons. Where before there was a pulldown screen, there is now just a straight list. No place to choose anything at all.

Another odd thing is on the control panel when I pick my server (Starsider), I get the initial list on the pulldown menu, but it shows up to Scylla. Then there is a big blank spot in the list, maybe 6 or 7 lines long. At first I thought the list got chopped off, but if I just type in Starsider it finds it, so I scrolled down further and the rest IS there.

I'm using Microsoft Office 2003 w/ the latest updates, as far as I know. I also don't know very much about Excel or programming in VB, although being able to tinker with this program makes me want to learn.

Nope, never tired of hearing from you folks; just been very pre-occupied with my real job this week.

I'll take a look at all of the reported issues this weekend. It's a 3 day weekend for me, so I should have plenty of time to fix this stuff and to get my honey-do's taken care of.

Hi Zad,

A few questions. Did you put the zlib.dll (included in the zip file) in your system32 directory?

Did you create a directory for your downloads and point the tool at it from the Control Panel?

What version of Office (or Excel) are you running? It appears that the current version of the tool now requires Office XP or higher.

Hello I was wondering if there was a list of the numbers you use for the resources so when you are entering your older resources into the table you dont have to type in the stats manually. Thanks

I was wondering that same thing powerlineman420, I spent all kinds of time on SWGCraft looking for some kind of complete list to download.


Now the reason I'm really here...

How is the price per unit figured?

It seems as if some resource types are weighted differently than others and I really like how fair the pricing seems with the scale set the way you have it. The price per unit is the price I'm using on my vendor for most resources, but not for all of them, so I got to thinking... and I wonder if you could have different formulas for different resource types.

I'd like to use the PE/500 = actual power per resource unit *1.5 = sale price formula for radioactive for instance, and a slightly more complicated formula for animal resources.

Thanks a lot, this thing's killer.


Peace