Solved

Excel macro sheet auto run and save to xlsx

Posted on 2016-11-17
5
56 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 500 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

756 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