• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 107
  • Last Modified:

Excel macro sheet auto run and save to xlsx

I have a Excel macro sheet that when it opens will pull data from our SQL server and update some data we have on the sheet.  I have it now being auto emailed everyday to a group of people and when they open the excel file it does the update so they see data from today.  The problem is that some of the people that get the excel sheet find it difficult to click that ENABLE MACRO's button which would that kick off the SQL update.  

I was wondering if there was a way for me to have the excel sheet connect to sql, update itself (as an XLSX file) and then email to an email group.
0
Colin Hart
Asked:
Colin Hart
  • 3
  • 2
1 Solution
 
MacroShadowCommented:
Yes you can connect an Excel workbook to a SQL database. See this MS article.
For several easy ways to prevent users from having to click the "Enable Macro" button see my article Overcome the Trust Center nuisance.
0
 
Colin HartAuthor Commented:
Thanks MacroShadow but I am not sure that will work.  My goal is to have the sheet run and update and then have it save as a static file that wont change.  Sorry, it looks like I didn't put that in the description but I would like it to run at 1pm and save the data to an XLSX file then shoot out an email with the file in it.  then no matter what time you open the attachment in the email the results wont change.  the snag is that if we open the file at 1pm it will be different then if we open it at 2pm so I need it to update itself at 1pm and then save as a final XLSX file and email out to everyone so no matter what time they open it, it will contain the same data.
sorry, my fault with the first explanation
any thoughts on how to do that?
0
 
MacroShadowCommented:
so I need it to update itself at 1pm and then save as a final XLSX file and email out to everyone so no matter what time they open it, it will contain the same data
No problem. Break the link before you save a copy of the workbook.
0
 
Colin HartAuthor Commented:
thanks MacroShadow
worked perfect
0
 
Colin HartAuthor Commented:
worked perfect
thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now