KSP_TreeCraft.xlsm

[XLjedi]

My username comes from too many years of tinkering with spreadsheets.  Recently, when my main PC mobo died... and my backup potato laptop could not really run KSP for several months, it gave me some time to play with a gadget that I wanted for career mode tech tree progression.

This is an excel-based data-scraping tool that I built to help me figure out which tech tree nodes are required for a given .CRAFT file.  The need arose while playing in multiple career modes and wanting to use a hangar of favorite craft that I work on in Sandbox mode.  When I'm playing in a given career, I often want to analyze a craft to see which tech tree nodes I still need to unlock before I am able to use the craft and what that might cost in terms of science research points.

So now, I open the workbook in Excel and start by selecting which tech tree nodes I currently have unlocked in my career.  The workbook stores the locations so I don't need to re-enter them for every craft that I want to look at.  So I click-click-click on each node and light up my tech tree like so:

With the tech nodes that I own lit-up I can now double-click on any one of my saved .CRAFT filenames and it overlays like so:

I see the purple nodes are required for my "Lectric-Jet" craft.  I notice the "Electrics" node (lightning bolt icon) is also a requirement for two of them, so I marked that one as a "Prerequisite" and it lights up blue.  Now I see exactly which nodes I need to unlock if I want to make the craft available in my career.  Also, in the lower righthand corner it compares what I own to what I need and tells me that it will cost 665 science research points to get there.  This particular craft will also require at least a level 2 upgrade to the SPH.

I click a button and instantly generate these nifty JPGs that I now post along with the my craft files on KerbalX so people can see which tech nodes are required for each of my craft.  After running a few dozen of these reports, I was able to see that I may need to rethink some designs or at least work on a few more mid-level career craft to add to my hangar.  

If you're comfortable using Excel, the file is here if you'd like to try it out:

http://xl-logic.com/KSP/excel/KSP_TreeCraft.xlsm

If anyone uses it, I'd be curious to hear how you like it?  There's also some notes and general part database info that I find useful for 3-way cross referencing:  the .CRAFT file "partname", the .CFG filename & directory, and the part "Title" that is used in the SPH/VAB.  Scripts for pulling every .CFG file from every subdirectory, etc...

Good news is, after several months, I was finally able to buy a new mobo/CPU/PSU and DRAM.  So I'm back in action and will actually be able to use this tool while starting a new career or two!

[Cruesoe]

I'm playing around with this after already using Excel to build my TechTree, really interested in the scraping of files to get part data but I just can't get it to  work.

Keep getting a VBA error on the Parts tab.  I can pull the files into the 'Files' tab but thats the only sheet that seems to work for me.

Any tips here @XLjedi?

Edited February 10, 2021 by Cruesoe

[XLjedi]

1 hour ago, Cruesoe said:

I'm playing around with this after already using Excel to build my TechTree, really interested in the scraping of files to get part data but I just can't get it to  work.

Keep getting a VBA error on the Parts tab.  I can pull the files into the 'Files' tab but thats the only sheet that seems to work for me.

Any tips here @XLjedi?

Which version of Excel?

[Cruesoe]

4 minutes ago, XLjedi said:

Which version of Excel?

O365 version 2101.

The current channel I think 

[XLjedi]

Never tried to run it on O365.  

I haven't tried it lately, and there have been a few updates to KSP.   So let me see if my version even works.

[XLjedi]

@Cruesoe I was able to confirm the file still works fine with the latest version of KSP.   I was able to load a 1.11.1 craft file without incident.

I'm using Office Pro 2010

When I have had problems in the past it was most often caused by not updating the craft file folder extensions correctly.  That would be the first place I would look.  Do you have the file directory structures correct for the craft you want to analyze?

I do have a version of O365 that I will also check with the same 1.11.1 craft file just to make sure it's not something wonky with Excel.

Edited February 11, 2021 by XLjedi

[Cruesoe]

9 minutes ago, XLjedi said:

@Cruesoe I was able to confirm the file still works fine with the latest version of KSP.   I was able to load a 1.11.1 craft file without incident.

I'm using Office Pro 2010

When I have had problems in the past it was most often caused by not updating the craft file folder extensions correctly.  That would be the first place I would look.  Do you have the file directory structures correct for the craft you want to analyze?

I do have a version of O365 that I will also check with the same 1.11.1 craft file just to make sure it's not something wonky with Excel.

I'm only really interested in the file scrape of parts. Building my own Unkerballed Start tree and I just want to make it easier on myself to move the parts.  The hardest bit of which is making the database list!

Thanks for checking, I'll see if I can fiddle with it some more.

[XLjedi]

16 minutes ago, Cruesoe said:

I'm only really interested in the file scrape of parts. Building my own Unkerballed Start tree and I just want to make it easier on myself to move the parts.  The hardest bit of which is making the database list!

Thanks for checking, I'll see if I can fiddle with it some more.

There are two concepts the file covers with regard to part scraping.  I pull the part info from the craft file of course, but I also have code associated with the "parts" tab that scrapes all part data from the game itself.  Let me know if you have any questions on either.  

The "Files" tab also includes code that pulls all subdirectories from a give directory, which helps tremendously in that respect.  So I guess there are three tools at play.

I suppose a first step in your case would be to make sure the "E:" file reference at the top of the "Parts" page is correct for your directory structure and then try to identify which line of code in VBA is causing your error.

 

Edited February 11, 2021 by XLjedi

[Cruesoe]

The first run on the part_load errors due to the filename.  Seems like it's trying to read ksp\GameData\.cfg on its first run through the loop and not finding the first filename.

I'll figure it out but unfortunately Work is paying me to do something else in Excel today, how rude.