-3

I am working on files used to monitor health plan data, and each type of report comes in the same template. I have created macros to automate the oversight of the files (find errors, gaps in data, logical improbabilities, etc). Now that I have that in place and the code has been cleaned up -- I am trying to automate the application of those macros to these files. For instance, for one type of report I have 10 client files I have to review. Currently I am going through the painstaking process of opening each file and dropping in the macro, applying it to the file, removing the macro (so that the clients cant take my code), and saving the resulting file. I repeat that process for each client -- and then do a similar process for many other reports. I know there has to be a better way, I am wondering if anyone has experience in this field and might be able to point me in the direction of how to achieve it. I use R studio for another process we automated and I believe I could utilize it for this process as well -- just need to find a jumping off point.

Manual intervention will still be needed to review the results of the macros, but I am hoping to eliminate the unnecessary manual touch points

Really appreciate any advise / knowledge you can share

Experiment 628
  • 1
  • Sounds like you should look into AddIns – Storax Apr 10 '20 at 16:40
  • You can use the Excel macro, to open another Excel file, manipulate the data, save it and then close the file. – Dave2e Apr 10 '20 at 16:45
  • If you show a sample of your existing code I'm sure the required change is not major: you just need to point your code to the ActiveWorkbook/ActiveSheet. – Tim Williams Apr 10 '20 at 17:04
  • @TimWilliams I am not sure we are on the same page. My Code does currently point towards the active sheet. My problem is that I have 10 different workbooks that I need to apply my current code to. Lets say this one report is called ERC -- so I have Client1ERC, Client2ERC... and so on. I want to be able to apply that code all ten files without having to open them individually, drop the code in, and apply. These are monthly reports, so I am currently going in every month and doing this manual process for 180+ files. I have about 15 macros and each applies to a specific report type – Experiment 628 Apr 10 '20 at 17:23
  • 1
    If your macro always uses "ActiveSheet" (for example) it's not clear why you would need to copy it into the workbook you need it to operate on. That's why I suggested you post a representative bit of your code so we could see what you're currently doing. – Tim Williams Apr 10 '20 at 18:48

1 Answers1

1

Unless the macro you've written contains some very specific functions that don't have Python equivalents, I'd recommend simply abandoning VBA and manipulating your Excel sheets in Python via xlwings or openpyxl. If your data is very "database-like" in that the top row is simply column headers and every additional row contains nothing but data aligned to those headers, you can also use pandas to process the data as well.

If you do need access to those functions built directly into Excel that don't have Python equivalents, you can use win32com to communicate with Excel via Python. This library basically drives Excel via its COM interface. You can then either use the COM libraries directly to execute an equivalent of your VBA file from within Python, or if you prefer to stick with VBA, you can simply paste your VBA code into your Python script and inject it into workbook like in this example. From there, you can also remove your VBA code from the Excel sheet as shown in this example.

A pure VB solution would involve essentially making these same calls to inject and subsequently remove the CodeComponent in an environment outside your Excel workbook.

You may find it vastly easier to solve problems like this with a popular scripting language like Python since the support community around it is much larger than VBA's. VBA tends to be unpopular among developers and thus its support community also tends to be small. Large support communities also mean well-maintained and highly-convenient libraries such as the aforementioned xlwings and openpyxl.

Nate Gardner
  • 1,577
  • 1
  • 16
  • 37