VBA and AppleScript

AppleScript
#1

I create from Bank etc, 4 Excel.csv files which I regularly have to reformat prior to posting into my Accounts as Journalised Entries. To this end I have written VBA Macros to perform the reformat tasks. I hoped to use AppleScript, not only to call the four files and their VBAs, which of course I can simply paste into blank Macros but for each CSV actually make AppleScript invoke Developer in Excel, select a blank Macro, give it a standard name and run it.

I have tried to record this path but have not captured any helpful script.

I would be so glad if anyone knows how to make Script DB record these “Actions” upon Excel and/or point me towards some Code which will get me started.

Why did I use VBA? Well it seemed easier, because its Recorder is comprehensively helpful. Of course, I would have much preferred to reach even the current stage in AppleScript, but there were manny Coding Gaps requiring search-trial-error.

#2

I can probably help as I’ve done a lot of VBA work. But I’m not clear on your workflow.

  1. Why do you need a blank Macro?
  2. Are each of your 4 macros different, or just operate on different files?
  3. Perhaps if you could provide the manual steps of your workflow, we could suggest some solutions.

AFAIK, Script Debugger can’t record scripts.
However, it is easy enough to invoke a VBA macro from an AppleScript.

Do do much development and testing, I would need some real-world sample data and your Excel VBA macros.

#3

Script debugger and record scripts, but only for applications that are “recordable” and there aren’t many of those.

#4

Thank you indeed. Of course that is just what I have discovered, but even sympathy is some comfort. I come from 30+ years experience with Computers; I started with 7029, then Pascal and a little later C and its ++. In those days, recording didn’t exist but at least one could access comprehensive dictionaries of command structures. Where is such support for AppleScript? I have Rosenthal’s Second edition, but found it silent on my questions. This void leads to the airing of quite childish enquiries like mine now to Forum, to which I am nonetheless profoundly grateful for the interest shown in my plight. Apple’s treatment of Automation and now its Time Capsule family is incomprehensible. Clearly, the Company has decided that it has other fish to fry and can afford to turn its back on the professional and/or semi-pro market.

#5

My Acknowledgement
Firstly, very many thanks indeed for picking up my problems.

  1. I don’t know any other way to apply a standardised macro to successive Work Sheets
  2. You will see from the “two” examples enclosed that the Macros are specific to the class of downloaded CSV
  3. The following is a snapshot of my automation so far.

My Test Samples. I am sorry I discovered at the end that communication channels to the forum do not permit my transferring these files to you. If you have any other conduit I can send you the necessary attachments.

Using Apple Computer and System 10 12 6

I have prepared foreshortened (for your convenience) test files with some substitutions in narrative detail for security. These files represent downloads from my Bank and Credit Card. There are at the moment two other sources which are broadly the same. These I haven’t bothered to send you because the automation problems are the same for all data sources.

To use the examples,
Save following Files to Documents:

a) Work1.Pages
b) Work2.Pages
c) Workbook1&2.Scptd

Save following Files to Downloads:

d) WorkBook1.csv
e) WorkBook2.csv

By running c) all four files will be loaded to the Screen

Take d) and invoke Macros with Developer in Excel’s Menu
select Macros
Name the Macro as Work1 and press return
Copy and paste Work1 into the Macro (don’t duplicate the Sub Name and End Sub)
Run or step through the Macro, which reformats the csv for WorkBook1, ready for uploading to my Accounting software.
Do likewise for e).

Don’t worry about Comments in the draft Macros Work1 and Work2. These are just development history which I have yet to tidy up.

My aim, of course is to embrace all stages with a single Apple Script (item c). However, I don’t know how to begin to translate Work1 and Work2 into Apple Script or even Call the ‘VBA’ within a Scripted Commands file such as c).

The initial download of WorkBooks 1 & 2 has, I think, got to be a manual operation, because logging into the Bank, Credit Card or whatever is protected in most if not all cases by demands for a random selection of characters from passwords and identity strings. Further, successive Downloads must have a Common Name which is handed on from job to job.

Sidere

#6

Just zip the files. Then you can upload them.

#7

Mark has upgraded my Memebership so that I can send the files.zip relating to my text.

Archive.zip (1.1 MB)
Sidere