June 2, 2016 at 9:04 am
Is it at all possible to create an Excel file (.xlsm) from data in a SQL 2014 database that would also include VBA macros, data validation and form controls?
I can have a template of the Excel file with much of this in place.
Alternatively, can you update an existing Excel file in a known location?
The requirement is to have a budget template that has an updated list of vendors available in a drop down list. The list of vendors is maintained in SQL. The form control and VBA controls what a user would be able to delete/add. The sheet would eventually be imported back into the database.
I'd rather have all of this functionality performed from an application, but the client wants the impart capability as well.
June 2, 2016 at 9:11 am
tnpich (6/2/2016)
Is it at all possible to create an Excel file (.xlsm) from data in a SQL 2014 database that would also include VBA macros, data validation and form controls?I can have a template of the Excel file with much of this in place.
Alternatively, can you update an existing Excel file in a known location?
The requirement is to have a budget template that has an updated list of vendors available in a drop down list. The list of vendors is maintained in SQL. The form control and VBA controls what a user would be able to delete/add. The sheet would eventually be imported back into the database.
I'd rather have all of this functionality performed from an application, but the client wants the impart capability as well.
SSIS can update the existing Excel file; assuming it's not open at the time, of course.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 2, 2016 at 9:17 am
You can try and use OPENROWSET to the Excel spreadsheet. I have had different results in it but should work.
Alternatives would be to just export data via script to CSV and then either have user import into that Excel file or do it with a script.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply