0

how can i make to automatically every day run my excel file, reload data from db and append in existing data.

Do I need to use vba script or...???

dev
  • 171
  • 1
  • 13

1 Answers1

0

I have an idea that might work,

  1. Write a script in any backend language of your choice (Java, Python...) that creates an active connection to the database
  2. Run it to periodically query a database table(MySQl,..) and store newly entered data into variables
  3. Continuously append that data into the excel file as you normally would any file.

Most languages have packages to handle excel files, I think it should do it. Good luck

Rohit Pothuraju
  • 83
  • 6
  • what language do you prefer? – Rohit Pothuraju Jun 05 '17 at 12:15
  • java language Java – dev Jun 05 '17 at 12:17
  • This is a basic tutorial to read/write excel files it uses Apacke POI lib that pretty much handles most Microsoft documents. https://www.mkyong.com/java/apache-poi-reading-and-writing-excel-file-in-java/ – Rohit Pothuraju Jun 05 '17 at 12:24
  • To append to the excel file, after the creation of the workbook, sheets. Use getworkbook and getsheet to retrieve existing sheet and then addcells to append data https://stackoverflow.com/questions/5509073/appending-data-in-an-excel-file – Rohit Pothuraju Jun 05 '17 at 12:25
  • Yes, it should be more easy as VBA is specifically designed for Microsoft applications. But yeah, similar procedure I would say. Connect to the database server, query the table and store in a excel sheet, then in a separate loop query the table upon modification and append the data to the same excel sheet – Rohit Pothuraju Jun 05 '17 at 12:36