Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel macro sheet auto run and save to xlsx

Posted on 2016-11-17
5
Medium Priority
?
97 Views
Last Modified: 2016-11-17
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
Comment
Question by:Colin Hart
  • 3
  • 2
5 Comments
 
LVL 28

Expert Comment

by:MacroShadow
ID: 41891497
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
 

Author Comment

by:Colin Hart
ID: 41891528
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
 
LVL 28

Accepted Solution

by:
MacroShadow earned 2000 total points
ID: 41891860
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
 

Author Comment

by:Colin Hart
ID: 41892186
thanks MacroShadow
worked perfect
0
 

Author Closing Comment

by:Colin Hart
ID: 41892189
worked perfect
thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question