CODEWITHSUNDEEP
excelpdfcommand-lineautomation
blunders
blunders

Reputation: 3669

In Excel, is it possible to automate reading of CSV data into a template and printing it to PDF from the commandline?

Current process conception is:

  1. DATA GENERATION: Data is generated via a standard process; meaning that though the values change, the location of the data is stored in the same location (cell A1's value will change, but the "label/mapping" of that data is static, or not changing.)
  2. DATA STORED: Data written to a single file location in delimited form (CSV,Tab, etc.) or to a MySQL database; meaning data is written and processed in serial, not in parallel; meaning a new job/task is not started until the current one is done; if it's possible to do in parallel that's fine, but it's not a requirement.
  3. DATA LOADED INTO TEMPLATE: Using an existing excel template, the data is read from the static file or database location, and the data is "loaded" into the template; meaning that the data location mapping is static.
  4. PDF GENERATION: The PDF is created and given a filename based on data from the CSV file and stored to a set directory/folder location.

(If it's not clear, all of this should be automatically done and if the template calls are made/mapped to the same datastore locations - the excel template should be able to just be opened, edited and saved without having to create a custom template for the process.)

TARGET_OS: OS-X-10.5, Mac-Office-2011 (mainly due to the templates being created in Mac-Office-2011, though if needed the process could be run on CentOS 5.3 or Window Server 2008)

QUESTIONS: Please comment if you have questions, I've generated docs before writing to OpenOffice ODF XML, zipping the file, and printing the PDF (although, can't recall the command-line PDF tool I used.)

Upvotes: 1

Views: 2866

Answers (1)

orangepips
orangepips

Reputation: 9971

If comments on the original question are correct and Mac does have Visual Basic for Applications embedded, then you can accomplish this all inside of Excel (tutorial link). It can do things like talk to a database, read and write cell values, and I believe shell out to other applications (e.g. PDF generation). Be forewared though, at least one road to hell is paved with Excel VBA.

Greatest roadblock I encountered is no easy way to ensure different users on different computers opening the same workbook (or a copy of that workbook) work.

Instead I would have users upload and download Excel files to and from a web app. You, and ultimately your users, will be much happier doing it that way.

Upvotes: 2

Related Questions