Hey guys. I'm working on something for my grandparents right now. They sell land for a living because neither of them have a retirement. They have all of their payment history in an excel spreadsheet for each "customer" in a separate tab of the same file. So my problem is that I want to make an applescript or automation thingy that will print out all of the payment historys for each customer updated to the current date. Do you know if this can be done and how?
Got a tip for us?
Let us know
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.
macOS Apple Script or something else with excel
- Thread starter cmwarre
- Start date
- Sort by reaction score
Excel will do just fine, if you know how to use it. For one thing, rather than have a separate tab for each customer, they should all be on one worksheet, with a column for Customer Name, to distinguish between them. Then you can use filtering to print data for selected customers, or sorting to print data for all customers.
You can bang a nail into a wall with a shoe if you know how to use it. It doesn't mean it's particularly well suited for the task...
You can bang a nail in a wall with an ICBM, too, but that doesn't mean it's appropriate, either. For the simple task that the OP described, Excel is a very appropriate and capable tool. A database is overkill, if that's all the OP wants to accomplish.
I wouldn't equate something like Bento with an ICBM. There are lightweight database solutions out there which frankly make a lot more sense than a spreadsheet for managing multiple accounts with multiple properties, and payments over time.
The OP is looking for a solution for their grandparents. Expecting grandparents to master a database, no matter how simple, compared to doing something much simpler in Excel, is inappropriate.
I've spent more than a decade designing, building and managing databases for corporate clients, and I wouldn't recommend one in this case, unless the grandparents are unusually computer literate.
That's one opinion, from someone who is computer literate. You're making assumptions that the grandparents find it easy to learn new software. It's quite possible they know only the basics of Excel and would find it difficult to learn new software. For someone computer literate, I'm sure there are many applications that can provide advanced functionality, but for what the OP described, it's not worth installing and learning new software, when the solution is very simply accomplished in software they already have and know how to use.
Would you also recommend someone with iTunes get a new application to sort their music collection by artist name, rather than use that existing functionality in iTunes?
If, as you make out, they only know the basics of Excel the solution you proposed would be harder than using Bento.
I was thinking the OP could do the initial setup, and the grandparents could do the data entry. It's pretty trivial to do, especially once it gets set up (which would also be pretty trivial in this case).
If you believe that, then you don't know how to use Excel. Adding a column for "Customer" with the customer name isn't difficult, unless you don't know how to point and click or type. In fact, the whole solution in Excel is faster and easier that downloading and installing any other software and getting familiar with using it.
But hey, if you get a commission for promoting Bento, knock yourself out!
Yet it obviously is not as simple as that if you had read the thread. They would have to completely restructure their worksheet. Which, as well as being time consuming, would probably result in human error.
If you are going to have to do such a massive reorganisation then it makes sense to go with the correct solution rather than just making do with a half baked one.
But as you say, I get paid for every time I mention Bento in one of my posts which explains why I mention it all the time. Oh wait...
My advice is for the OP to take a look at Bento (there's a free trial), see if you can make enough sense of it to put together a couple of tables to track customers, properties and payments. If you think you can figure it out, and that your grandparents would be able to keep it going once you show them how to add in the payments and any new customers/properties they may sell, then they will be a lot better off in the long run with this strategy. It is more flexible and will let them potentially do other things with their data down the road if they ever need to (e.g. adding a comments field for each payment so they can make notes if a payment was late, or some other pertinent info).
***runs off and cashes his imaginary check from Bento***
***runs off and cashes his imaginary check from Bento***
As i'm not supposed to force anyone to using particular software, here's my approach.
In fact, you can do AppleScript with Excel files, if the program's version is up to 2004. Since Excel 2008 doesn't support AppleScript any more, there is no easy way to that. You find a reference
here.
Because of AppleScripting Excel is not easy to handle for the unexperienced Scripter i do often prefer 'preparing' Excel files using 'xls2csv', which is part of the catdoc port. Installing MacPorts via the MacPort site
and then gettin' catdoc will do the trick.
On the command line, type 'man xls2csv' to see the syntax and how to use, separating the individual sheets in your output.
Properly converted to a CSV, your Script will be able to read in the contents of this file, line-by-line and field-by-field, so you should be able to get out the information needed. I'm not assuming any given 'formatting', but in case the sheets are more or less identical, that's maybe what you want.
As i have no idea how familiar you are with embedding 'external command calls' in AppleScript, i don't know how much further info you'll need ...
In fact, you can do AppleScript with Excel files, if the program's version is up to 2004. Since Excel 2008 doesn't support AppleScript any more, there is no easy way to that. You find a reference
here.
Because of AppleScripting Excel is not easy to handle for the unexperienced Scripter i do often prefer 'preparing' Excel files using 'xls2csv', which is part of the catdoc port. Installing MacPorts via the MacPort site
and then gettin' catdoc will do the trick.
On the command line, type 'man xls2csv' to see the syntax and how to use, separating the individual sheets in your output.
Properly converted to a CSV, your Script will be able to read in the contents of this file, line-by-line and field-by-field, so you should be able to get out the information needed. I'm not assuming any given 'formatting', but in case the sheets are more or less identical, that's maybe what you want.
As i have no idea how familiar you are with embedding 'external command calls' in AppleScript, i don't know how much further info you'll need ...