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
Solved

Excel macro sheet auto run and save to xlsx

Posted on 2016-11-17
5
42 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 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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

809 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