0

Currently we have a process that our IT department has to do manually on a weekly basis that we really need to automate. We have a few of our employees that get paid a different way. The hr department has to send us the employee names and dates and we have to connect to a MySQL database. Put the correct employee numbers and dates in a script we have set up. Execute the script. And then send the hr department an email back with the table it generates.

I was thinking is there a way to automate a SQL script in Excel using macros or something similar. That way we could send the hr department the spread sheet. They go in the spreadsheet input the employees and dates in cells. run the script and let it give them their data back?

Any input on this would be greatly appreciated. Thanks!

Community
  • 1
  • 1
user3250714
  • 1
  • 1
  • 1

2 Answers2

1

There are many different things you could pursue, depending on you what need done exactly. If I was in your shoes I would discuss with your IT dept about SSIS packages, http://en.wikipedia.org/wiki/SQL_Server_Integration_Services . With SSIS you can upload excel docs, have it run the queries you need and then export the spread sheet with the updates.

However, you will need someone knowledgeable of SQL servers and SSIS to set this up.

Vin Vill
  • 24
  • 5
1

You could integrate the SQL query to a VBA script within your Excel file, but you would definitely have to code that.

Take a look a this example to get automated query from SQL Server:

Using Excel VBA to run SQL query

Community
  • 1
  • 1
guiguiblitz
  • 407
  • 4
  • 10