Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel macro sheet auto run and save to xlsx

Posted on 2016-11-17
5
Medium Priority
?
87 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 27

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 27

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

Industry Leaders: 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!

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

715 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