0

I'm generating a csv file from a database using a web application developed in PHP. I've created a batch file to copy the csv file from the server directory to another server upon generation. I have also created macros to save it as an xlsx file, format the table and perform calculations on the data. All of this does not need to happen every time the file is open, just when it is exported.

I would like to work these macros into the batch file so that all of this happens automatically upon generation, rather than generation, copying to the appropriate folder then having to open it and manually executing the macros.

What I'm thinking my order of operations will be are:

  1. Generate csv file

  2. Convert to xlsx with macro

  3. Use macros to format and perform calculations

  4. Copy xlsx file to appropriate folder on another drive

  5. Delete xlsx file on the server the web application is on

I'm having trouble figuring out if I can or how to export the excel macros and use them as standalone files to be used by the batch file. Is what I'm wanting to do possible? Am I approaching this from a wrong direction? Any help, even just a, "Hey look into this," would be greatly appreciated.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Hurroo
  • 9
  • 2
  • @BigBen I saw that post. Thats what I'm trying to base what I currently have off of. However, I'm not sure if it's possible to have just a standalone macro not tied to a workbook. Also, when testing with macros in the workbook on my local machine, I can't seem to get it to work with a csv file. – Hurroo Jul 27 '18 at 17:56
  • 1
    It is not possible to have just a standalone macro, but as that answer points out, you can use VBScript, or leverage the [`Workbook.Open` event](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/workbook-open-event-excel). _"I can't seem to get it to work with a csv file"_ - maybe add what you've tried to your question? As is, we can't tell what problems you've been having. – BigBen Jul 27 '18 at 17:58
  • Alright. Thank you very much. – Hurroo Jul 27 '18 at 18:00
  • 2
    Instead of using php,Excel macro,batch and vbscript I'd consider using a modern script language able to perform most of that in one go: PowerShell. –  Jul 27 '18 at 18:14

0 Answers0