IFTA Reporting

I suppose not hard coding pointers, error handling, and search algorithms are outside the scope of a 300 level VBA class. By the way, OLE is the process of embedding one file within another. Like putting a video in a power point slide. Using an external data source is not that.

The problem with VBA is that it enables a lot of bad programming habits for people that dare to venture into the macro editor.

I don't have a problem with linking to external data sources. You have to do it the right way to avoid unpredictable output. I just refuse to spend the time to do it if I'm only linking a few files on a report I run 4 times a year. Easier to copy/paste and use formulas on an IFTA report. Remote data access in that case turns something simple into a Rube Goldberg contraption.

A more appropriate use was a networking project I did a couple years ago. We needed to see user network port authentication over time to make sure a desktop update had been applied widely enough not to affect users after a scheduled change to higher port security. The network engineers set up an automated port scan 2x a day on thousands of Cisco switches at hundreds of sites, dumping each scan of each switch into a text file placed in a directory structure with a known naming convention. I wrote the summary template with embedded VBA code that would go out, collect the data in tabular form, and build pivot tables to summarize it.

It took a couple of hours to design the method and write the code that produced hundreds of reports over the duration of the project, taking a site code entry and one mouse click each time. Done manually, it would have taken the average admin at least 30 minutes each report. That is, if they even knew how to use advanced formulas and data functions.

 

I find this funny only because it's the one lesson in system design that aspiring I/T solution architects often miss. Often a solution that incorporates no technology is the best method to get the task done.

I'll never forget one particular exercise in one of those classes. The project was to design a system for a sandwich shop. Turns out the method using paper slips and pencils was the most efficient and least costly way to get it done. I got an A on that one LOL.

 

It's kind of both.

http://www.businessdictionary.com/definition/object-linking-and-embedding-OLE.html

You can link or you can embed.

VBA while powerful is not the same as developing with something (aging here) like pascal, C++, Fortran or other things like that. I did some of that years ago enough to learn I didn't like it and don't do it.

If anything, VBA helps to simplify repeatable tasks for me within Office. Because I do have some VBA coding in my spreadsheets, I rarely share them. I use them to add the columns/rows I might want or need as I use a spread sheet.

Even my business plan has excel spreadsheets embedded in it that refreshes each time it opens automatically. I don't have to muss with going into the spreadsheet and doing it manually.

OpenOffice I have found you can do many of the similar things, but when you start to share things with people who use office, you have compatibility issues mainly with formatting. Became such an issue, I stopped using OpenOffice.

I mention all this because in my trip reporting spreadsheet, I have macros written into it. They add rows to my fuel and mileage tables and enter the date in them automatically for me. VBA automated all those tasks for me.

 

IFTA reporting doesn't have to be complicated. For a small operation of only a few trucks, just get a trip sheet copy it onto a large envelope. At the beginning of the trip, put on the date, origin and beginning odometer reading. At each stateline crossing, enter the jurisdiction and stateline crossing. Some drivers that did well on stateline odometer readings told me that they used a recorder at each crossing and entered them at stops. Enter the fuel purchases on the trip sheet. Put the receipts into the envelope. Total the trip sheets and enter into a simple spreadsheet or on tapes for the quarter. Make sure there are not gaps in the odometer readings between trips. If you have one a week, that is 12-13 envelopes each quarter. Some states require both odometer readings and routes of travel. Keep the trip sheets, receipts and spreadsheets/tapes attached to a copy of your return.

If you go with a gps system, be sure to download the gps information on a regular basis. It may be accuarate but if you can't document it, it doesn't count in an audit.