Modding tools: Access vs. Excel

[Ed Kolis]

I know it might seem kind of strange to use MS Office applications to mod SE4, but they are really good at storing & manipulating data... The question is, should I use Access or Excel?

I'll list a few advantages of each:

Access:
-Stores data in named fields rather than arbitrary rows/columns
-Type checking and referential integrity ensure that all fields contain valid data
-Forms can be designed to input data more easily (without having to scroll all over the place)
-Many-to-many correspondence ideal for use with Abilities

Excel:
-Easier for calculating series of similar items (just Fill Down a formula)
-Can record macros that generate VBA code which can then be modified to do whatever you want
-I'm more familiar with VBA for Excel than for Access

Any thoughts? It would be cool to have a master database that you can edit with all the power of a database utility, but I just don't know enough about VBA to make the connections to import and export the data to the .txt files, which is the important part!

[Taera]

im sorry to go OT a little, but why do you people use such advanced tools for modding?

[Zarix]

Even little more OT. I prefer to use OpenOffice. It can do pretty much everything that I need but doesn't cost awfully like MS Office. OpenOffice could be better choice for a modding too because it is easier to get.

[Fyron]

Originally posted by Taera:
im sorry to go OT a little, but why do you people use such advanced tools for modding?<font size="2" face="Verdana, Helvetica, sans-serif">It is extremely useful for making mods with things like Tech Grids or complete changes to the entire game. http://forum.shrapnelgames.com/images/icons/icon7.gif

[capnq]

If you're modding something that involves a lot of numbers, like weapons or culture bonuses, a spreadsheet can help you see whether things are balanced. (I haven't used anything this complex myself, but my mods haven't been that ambitious.)

[PvK]

I used spreadsheets for designing my Proportions mod, to help with all of the calculations and to get statistics easily and to compare things, to graph the population effect curves, and so on.

I hate MS Abscess, BTW. http://forum.shrapnelgames.com/images/icons/icon12.gif

PvK

[zen.]

I usually go with Excel. I know that Access can compile data into charts that will look like like a spreadsheet, but it might just be overkill. Then again, I suck at using Access. http://forum.shrapnelgames.com/images/icons/tongue.gif

Just use whatever is most comfortable. If you ever plan on releasing the code/database to the community, though, Excel is the more prevalent app.

zen

[Gryphin]

I use Access to store notes on SEIV in general.
To take advantage of Excells Calculations and Acccess DB functions:
Do the data manipulation in Excel and link the file to Access.
I have not tryed this and do not know how well it would work.
I would be happy to work with you on this.

[Ed Kolis]

Excellent idea! http://forum.shrapnelgames.com/images/icons/icon10.gif

I went ahead and ran a test to see whether if I linked an Excel spreadsheet into Access, and modified the data in Access, the spreadsheet would be changed, and indeed it was, which is good...

I also went ahead and created an Excel macro that loads any SE4 data file into a spreadsheet, assuming that the spreadsheet has appropriate column headings (what would be the fields of the database - "Long Name", "Short Name", "Tonnage", etc.) for the data file being loaded. It works on CompEnhancement.txt quite nicely! http://forum.shrapnelgames.com/images/icons/icon10.gif Now to write the export macro, and create all the worksheets to store the data for all the various SE4 files...

Then, once that self-contained modding tool is complete, I can create an Access database linked to the spreadsheet for those of us who have Access as well as Excel - then we can REALLY rock! http://forum.shrapnelgames.com/images/icons/icon10.gif

Thanks for the idea of the spreadsheet/database link! http://forum.shrapnelgames.com/images/icons/icon10.gif I wouldn't have thought of using BOTH!

Edit: You use Access for "general notes" on SE4? How? http://forum.shrapnelgames.com/images/icons/confused.gif Like notes on empires you encounter, so you can easily search for what you need?

[ December 08, 2002, 03:21: Message edited by: Ed Kolis ]

[Phoenix-D]

"I also went ahead and created an Excel macro that loads any SE4 data file into a spreadsheet, assuming that the spreadsheet has appropriate column headings (what would be the fields of the database - "Long Name", "Short Name", "Tonnage", etc.) for the data file being loaded. It works on CompEnhancement.txt quite nicely! Now to write the export macro, and create all the worksheets to store the data for all the various SE4 files..."

I haven't figured Excel out yet, but this sounds like it would be *really* useful. Would you mind posting it when you get it finished?

Phoenix-D

[geoschmo]

Yes please Gryhin, if it's not too much trouble. That does sound slick.

[Gryphin]

Ok, I'll try to clean it up by Jan 8, 03. It won't be a lot of troulbe, I just have to apply myself. It should only take about 4 Mid game SEIV turns. (that is how I measure time).

[Ed Kolis]

OK, here's the spreadsheet... currently I only have a worksheet for the weapon mounts, but if you change the column headings and the name of the file being read in the macro then you can read any SE4 data file...

www.geocities.com/edwardkolis/se4/SE4ModderWorkbook.zip (http://www.geocities.com/edwardkolis/se4/SE4ModderWorkbook.zip) (11 KB)

Don't forget to set cell A1 in the "Path" worksheet to the path of your data files, otherwise you'll probably get bunches of errors http://forum.shrapnelgames.com/images/icons/icon10.gif

I'm probably going to rework this, though, since I realized it won't work for things like the weapon damages where there are really 20 entries in one, and the way I handled the "array" entries (like tech requirements and such) is not optimal - if there are more than 10 (or however many you make headings for) it just ignores the rest...

[Gryphin]

I use Access to store just about everything so I can look it up. I use Word for composing and then transfere it.
I am only in one PBW game. In my sew low games I try to be in the habbit of recording anything I will want to retrieve. This requires a lot of dicipline. Not my forte.
I have tryed to store "Tip, Tricks, and Traps" along with ideas for mods and such.
I try to dessige it so I can look up:
Strategy > Expansion > Build >
How To > Planet > Capture
How To > Drones > Use

I guess I could clean it up so others could use it. I'm pretty bad at getting projects done. Quite a few of my ideas don't work so it would be a use at your own risk thing.

My background is:
I do Desktop Support for about 120 people. I spent 2 years of my life supporting Word for Micro$ponge. I spent another year supporting various other MS products.

[Krsqk]

Don't forget about the optional lines in CompEnhancement.txt. It shouldn't be too hard to make allowances for them when reading/writing the file, though.

[Graeme Dice]

Originally posted by Ed Kolis:www.geocities.com/edwardkolis/se4/SE4ModderWorkbook.zip (http://www.geocities.com/edwardkolis/se4/SE4ModderWorkbook.zip) (11 KB)
<font size="2" face="Verdana, Helvetica, sans-serif">Would it be possible to make this file available again?

[Ed Kolis]

Oops, I forgot about Geocities' no remote linking rule... I'm trying to find another free hosting service, one where I can upload via FTP so I don't have to manually select every single file I upload...

Anyway, try this: www.geocities.com/edwardkolis/modder.htm (http://www.geocities.com/edwardkolis/modder.htm)

[Andrés]

I use Excel and Word to mailmerge into se4 data files.

[Graeme Dice]

Originally posted by Andr&eacutes Lescano:
I use Excel and Word to mailmerge into se4 data files.<font size="2" face="Verdana, Helvetica, sans-serif">I'm wondering how you go about getting the data from Excel into the data source for the mail merger? I've got a whole bunch of components designed in Excel and it would be great to be able to keep their relationships automatically adjusted whenever I make a change to the base component.

[Haven]

OK, here's the spreadsheet... currently I only have a worksheet for the weapon mounts, but if you change the column headings and the name of the file being read in the macro then you can read any SE4 data file...

www.geocities.com/edwardkolis/se4/SE4ModderWorkbook.zip (http://www.geocities.com/edwardkolis/se4/SE4ModderWorkbook.zip) (11 KB)

Don't forget to set cell A1 in the "Path" worksheet to the path of your data files, otherwise you'll probably get bunches of errors <font size="2" face="Verdana, Helvetica, sans-serif">I tried to retreve the spreadsheet but I get Sorry, the page you requested was not found.<font size="2" face="Verdana, Helvetica, sans-serif">

[Andrés]

This is an example of using Excel and Word

http://se4kdy.cyberwars.com/files/sci-fi-cross/sci-fi_x-over_xls'.zip

[jimbob]

bump!

I'm going to try out Andre's system, but I'd love to use the other also. Is Jan 8th still on the go?

[Gryphin]

Hm, Jan 8th. I think on a PERT or GANT chart it is called, "Slippage". Lets see what I can pull off this weekend.

[Haven]

Bump http://forum.shrapnelgames.com/images/icons/icon7.gif
</font><blockquote><font size="1" face="Verdana, Helvetica, sans-serif">quote:</font><hr /><font size="2" face="Verdana, Helvetica, sans-serif">OK, here's the spreadsheet... currently I only have a worksheet for the weapon mounts, but if you change the column headings and the name of the file being read in the macro then you can read any SE4 data file...

www.geocities.com/edwardkolis/se4/SE4ModderWorkbook.zip (http://www.geocities.com/edwardkolis/se4/SE4ModderWorkbook.zip) (11 KB)

Don't forget to set cell A1 in the "Path" worksheet to the path of your data files, otherwise you'll probably get bunches of errors <font size="2" face="Verdana, Helvetica, sans-serif">I tried to retreve the spreadsheet but I get Sorry, the page you requested was not found.<font size="2" face="Verdana, Helvetica, sans-serif"></font><hr /></blockquote><font size="2" face="Verdana, Helvetica, sans-serif">Would you please repost this spreedsheet.

[Ed Kolis]

Oh, all right... I thought I did but I'll try again, let's see if Spaceports supports remote linking...

http://galileo.spaceports.com/~ekolis/se4/SE4ModderWorkbook.zip

[Mephisto]

Not found error...