Autroute File Generating Script
So since I have too much free time, I developed some Excel VBA scripts that generate individual Autoroute save files for each route defined in Bagaluth's Hanse Planner excel spreadsheet.  This saves a LOT of time.

However, the extensions I've made to the P4 Planner Bagaluth made are not documented and probably not intuitive to anyone other than me.  It works, but is provided as is with no guarantee or warranty.  I've run out of willpower to play P4 for the next couple months, so if anyone else wants to take the effort to spruce up the presentation...  Well, go for it I guess.  I'll happily upload a better version with credit to you.


Things to note at the outset.
  • It works with P4: Rise of the Hanse, and is untested on the vanilla game.  It has not been tested on the German version, but the likelihood is that it works.
  • This is a VBA Macro enabled Excel workbook now, meaning that you have to be authorized to open macro enabled Excel workbooks to use the signature feature, the generation of saved autoroute (*.atr) files.
  • VBA is a programming language and dicking around with it can potentially make your life difficult.  I have not incorporated any malicious code, but I'm not responsible for anything you might do to wreck your system through poking around with the code.
  • It automagically deletes all the saved autoroute files you have and replaces them with automatically generated ones.  (Actually it deletes all files of any type in the autoroute directory, but unless you are keeping something other than *.atr files there for some inexplicable reason, this should not be an issue.
  • *.atr files are NOT accessed by the game while it is running.  You have to close the game, generate the files and reopen the game completely to get them to appear as saved routes in your saved route menu.
There are three Macros defined in the workbook:

  1. ClearOutFacilityDistribution - 0s out the number of businesses in each town on the sheet "11a) Facility Distribution".  Used when you are starting a new game.  Pressing it after you have been playing for a while is a good way to get irritated.
  2. GenerateRouteFiles - Generates saved routes based on the "11a) Facility distribution", "TownList" and "12 route planning" sheets, plus the worksheets defined for each individual route.  The prices recommended for each difficulty level in "2 Price Table" are hard coded into the VBA.  The route will have the name listed for it on the relevant line of "12 route planning", but without whitespace or hyphens in the menu of saved routes.
  3. PrintCountingHouseList - This prints out a copy of the counting house administrator (from "13b) admin setting calculation") instructions for every town in which you have a warehouse listed in "TownList".  Pressing Alt-Tab to change screens to Excel will cycle through your list of counting houses, and this can make entering the info from the sheet difficult if it is open on the same PC as the game.  This is invalid if you have multiple monitors or a second computer, which I do not.  As such having the lists on paper is very helpful to me.
Here is a list of sheets where I've messed with Bagaluth's original.  The red highlighting of cells you can type in per Bagaluth is now, very dangerously, inaccurate.  You should only need to change things on "11a) Facility distribution", "TownList" and "12 route planning" normally.
  • 11a) Facility distribution - There are now two copies of the Hansa planning table on this sheet.  The second one is Bagaluth's ideal fully developed Hansa, the first is the one that currently exists in your game.  In this table, the first line shows how many businesses of each type the town will use for export once it is fully developed.  The second shows how many it will need for local consumption of each good once they are fully developed.  The third shows how many production buildings of each type are currently in each town, the fourth how many of these buildings you own in each town.  You can get the information for each of these quite quickly by clicking on a building of each type in towns where you have a counting house or a convoy in the harbor. You can press CTRL to toggle blue dots over your own buildings if you need to.  The "Current Total" for line 3 of each house should be all the buildings owned by your competitor in the region and all of yours.  This can also be gotten from the "Production Per Day" sub tab in the Statistics tab in the guild house.   This gives you a list of how many businesses of each type exist in each town, for both you and your competitor, and it doesn't change if you press tab.  Neither method includes businesses under construction.  So looking around each town to see if there are any businesses under construction when you look at each is higly recommended.  You need to have this table filled out correctly to get the cargo loadouts of your route files defined properly.
  • TownList - This is a list of all the towns in the Hansa, with some definition information.  In the upper left hand corner, you should put an "X" next to whichever difficulty level you are playing on.  (And ONLY the difficulty level in question, cells D2, D3 or D4.)    In the upper right hand corner is the directory where your *.atr files are saved.  The German town names are used here and on sheet "11a) facility distribution".  Column C should contain an X if the town in question is both visible on the map and available for trade.  (It may help you to delay listing land only towns until AFTER they are connected to at least one hub town.)  Column D should contain an X if you have a warehouse in the town.  If you do, the captain will load and unload the max based on the definitions of the counting house administrator, as described by Bagaluth.  If you don't, the captain trades at the prices appropriate to that difficulty level and good listed in "2 Price table"  (But these values are hard coded in the VBA).  Column E should have an X if the town has a large shipyard or is a hub.  This tells the captain to use the town for repairs, on the assumption that towns with large shipyards repair more efficiently than those without.  If the town is a Hub, you should have an X there, so at least one town on each route is approved for auto-repairs.  You can just put Xs in the whole column if you don't care where your captain gets repairs.  Column F says which "Current Total line on "11a) Facility distribution" corresponds to this town.  Don't mess with this unless you want to reorder "11a) Facility Distribution" for whatever odd reason.  Column G gives the one byte hexcode corresponding to the town in the *.atr files.  Absolutely do not screw with this.  Column H has an X if the town is one of your four hubs.  Column I has the date of the annual mayoral election in each town for reference, for when you start moving in to build chapels and infirmaries.   This seems to vary from game to game so you'll need to check for yourself and correct as needed.
  • 12 route planning - I screwed with Bagaluth's system rather a lot here.  1st and most important, I take any sea or river route that has a total travel time of more than 10 days and any land route with one more than 40 days, and give each a multiplier proportional to how many days of travel time it has over 10 or 40.  The theory is that you want any convoy to carry enough supplies for 10 days OR the number of days it will have between visits to each town. That way if the other hubs convoys' are lacking a good for a delivery, the convoy making the longer journey can theoretical deposit everything the town needs every X days of its journey.  The travel times are modified based on the type of ships available.  Once hulks and caravels are researched, the travel time for sea routes gets ~7%.  Once river cogs are researched, the river routes slow down ~12%  You can see that cells N4, N5 and N6 should have Xs based on which ship types are researched.  (The "Cartography" tech is not included as I don't know how it factors in.)  The travel times are still partially hard coded owing to the complexity of the formula needed to get the travel time from the list of stops on each line (especially if it skips towns that aren't there).  Based on which ship types are available and the needs of the towns on each route, the number of ships you need for each route's convoy is calculated in the custom sheet for each route. The ships needed vary based on what types are available.  There are non-calculated columns so you can note how many unused ships of each type you have available in each hub.  I have added a pair of land routes for each sea or river town that is also accessible by land, so that these towns have some chance of being supplied if the harbors freeze.  All land routes also have a list of how many wagons are needed for the route, based on the same criteria as the sea and river routes.  I have changed the Bremen hub to Hamburg as a personal preference. Lastly, I've defined a set of "Initial" routes using Cogs or Crayers for the early game when I only have a counting house in Hamburg.  Note - ANY meaningful change to the layout of this sheet will require editing of the VBA for the "GenerateRouteFiles" macro and the references for the matching route specific sheets.  Since you are likely to build a hub at Riga and a Riga RR before getting access to Königsberg, you might want to replace Königsberg with Riga on the Riga RR.  The stops on each route are defined by the values in columns B-g.  (B-I for land routes.)
  • 13b admin setting calculation - This is now mostly automated.  The sheet differentiates between all facilities in the town and the ones you own, and gets the sum of all facilities for the population need calculation automatically.  The sell sheet calculations are unchanged, but the buy sheet is a bit different.  If the town produces a good, AND the amount it produces is adequate to its needs, then the admin is told to buy a ridiculous amount of the good (the total population, in fact) and should not lock the buy sheet quantity.  If the town doesn't produce enough for its needs, there's a 40 day supply specified held locked on the buy sheet.  It knows if there's a spice supply in the town and buys spice if there is.  The sheet draws all of this info from the "11a) Facility distribution" sheet based on the town name typed in cell N5.  The spelling of the town name MUST be identical to that in "TownList".  (ALT + 0246 = ö and ALT + 0252 = ü...).  Because of the preposterously high buy totals for locally produced goods, the "warehouses required to pay no rent calculation" is now invalid.
  • Route Specific Sheets - Each route listed on "12 route planning" has a matching sheet defining its cargo settings.  For example, "HamburgLondon" starts at the Hamburg Hub and stops at Edinburgh, Scarborough, Boston and London in that order.  The needs for loading and unloading in each town are autocalculated based on the names of the town at each stop, which, again, must match the names in "TownList" exactly.  Since Boston has not been built yet in the game I used for this latest version, it has 0 buildings of each type listed in "11a) Facility distribution", and no consumption needs.  
I'll add a brief explanation of the main VBA function for producing route files in a post below.
WriteRouteFile Function


  • RoutePlanningLineNumber - Numeric reference to the line number on the sheet "12 Route planning" containing the definition of the route.
  • RouteDestinationFilename - String filename to write the *.atr file.  The game likes the format ""p4r5.atr", where the second number is a unique identifier for the file.  If there are 63 autoroute files that are generated, the first will be "p4r1.atr", followed by "p4r2.atr", up to "p4r63.atr".  The function will cheerfully overwrite an existing file with a new one, which may make an unreadable corrupted file if the old one was a bit longer or shorter, so make sure the file in question doesn't exist before you run this, and don't try to write to the same file twice in one batch.
  • RouteType - Enumeration defining what type of route this is.  eSeaRoute (0) for open sea trade routes; eRhineRiverRoute (1) for routes from North Sea hubs to towns on the Rhine; eBalticRiverRoute (2) for routes with Novgorod and Thorn as destinations; and eLandRoute (3) for wagon train routes.
  • RouteWorksheet1 - An Excel VBA object reference to the worksheet defining the loading orders at the hub for the route.
  • RouteWorksheet2 - (Optional) Since Baltic River Convoys go to either Thorn or Novgorod and are loaded specifically for them at Riga or Königsberg, they need two worksheets, one for each destination.


WriteRouteFile 29, TargetDirectory & "p4r11.atr", eSeaRoute, ActiveWorkbook.Sheets("HamburgLondon")

This takes the route definition from line 29 of the "12 route planning" sheet and the "HamburgLondon" sheet, generates a route using the sea route logic, and writes it to "p4r11.atr".

Notes:  The game can tolerate a lot of nonsense from *.atr files.  It doesn't differentiate between sea and land routes, and throws out any stop that can't be reached from the starting point before you can load the route.  My function does do a certain amount of garbage exclusion though.  It won't generate route files for land routes where the destination doesn't exist yet, for example, and skips cases where one would travel from a town to itself.
Obviously, if you don't understand what the good Bagaluth was doing in the original Hanse planner, this ain't gonna make a whole lot of sense.


Users browsing this thread: 1 Guest(s)