I am trying to automate an excel report so that it automatically posts a SQL query then saves the file every 24 hours. Currently I can do one or the other (post sql or save) but I cannot run both at the same time.
So far, I have an ODBC connection between my excel file and my MS SQL database and have the SQL queries refreshing every 1440 minutes.
I also have a VB timer set to run every 24 hours to save the workbook every morning. The timer starts when I click a button. Here is the script I use for that
Sub Save()
ActiveWorkbook. Save
timer1
End Sub
Sub SaveName()
ActiveWorkbook. SaveAs Filename:="C:\. .."
timer1
End Sub
Sub timer1()
Application.Wai t Now + TimeValue("24:0 0:00")
Save
End Sub
Private Sub CommandButton1_ Click()
timer1
End Sub
However, when I click the button to run the timer it slows everything down so that my MS SQL query timer does not count down.
My question is how to get both of these to work at the same time. Should I run the SQL query within VB as well so both functions use the same timer. If so, how would I do that. If I can figure this out, I will be able to run several reports that update, save and e-mail themselves out every day, week, month etc...
Onthefly
10 8488
I am trying to automate an excel report so that it automatically posts a SQL query then saves the file every 24 hours. Currently I can do one or the other (post sql or save) but I cannot run both at the same time.
So far, I have an ODBC connection between my excel file and my MS SQL database and have the SQL queries refreshing every 1440 minutes.
I also have a VB timer set to run every 24 hours to save the workbook every morning. The timer starts when I click a button. Here is the script I use for that
Sub Save()
ActiveWorkbook. Save
timer1
End Sub
Sub SaveName()
ActiveWorkbook. SaveAs Filename:="C:\. .."
timer1
End Sub
Sub timer1()
Application.Wai t Now + TimeValue("24:0 0:00")
Save
End Sub
Private Sub CommandButton1_ Click()
timer1
End Sub
However, when I click the button to run the timer it slows everything down so that my MS SQL query timer does not count down.
My question is how to get both of these to work at the same time. Should I run the SQL query within VB as well so both functions use the same timer. If so, how would I do that. If I can figure this out, I will be able to run several reports that update, save and e-mail themselves out every day, week, month etc...
Onthefly
Excel has an Autosave add-on that comes with it, why not use it. Should be listed under tools, if not you'll need the disk to add it.
That is what I originally tried, and it works but the autosave feature saves it as an .xar file and it saves it like a hidden file which prevents my re-occurring e-mail software program from recognizing it. Using the Autosave feature would be my first choice if I could figure out a way around this issue.
Ok, I re-read your post, and you have the Excel file Updating / inserting the Excel changes to the MS Sql Database every night, correct?
Why not just perform the updates when they're made. I created one at work that uses ADO and when you leave a cell, updates that records field
If I do not have a value in the 1st column then that indicates a new record so it does an insert to the table
Actually I have it the other way around. I have an excel spreadsheet that is pulling info from my MS SQL database everynight and posting the results in the spreadsheet (and it erases the results that were their the day before). So my question is, how do I then save the changes from the new query after the update took place so I can e-mail out the new figures.
Thanks for your help by the way....
Actually I have it the other way around. I have an excel spreadsheet that is pulling info from my MS SQL database everynight and posting the results in the spreadsheet (and it erases the results that were their the day before). So my question is, how do I then save the changes from the new query after the update took place so I can e-mail out the new figures.
Thanks for your help by the way....
We both learned on this one.
Create a module with this procedure in it -
Public Sub SaveData()
-
ThisWorkbook.Save
-
MsgBox "Saved", VBOkOnly, "Data Saved"
-
End Sub
-
and in the worksheet add this -
Private Sub Workbook_Open()
-
'This will run the code 1 minute after the worksheet is opened
-
Application.OnTime Now + TimeValue("00:01:00"), "SaveData", , True
-
-
'Or Use this to set a time, here it is 1 PM
-
' Application.OnTime TimeValue("13:00:00"), "SaveData", , True
-
End Sub
-
-
Hey, I think this will work! But I must be missing something because as it tries to save I get this error:
The Macro:"C:Docume nts and Settings\....'! SaveData' cannot be found.
Sorry, I am fairly new with VB so I am probably missing something simple.. any idea?
Hey, I think this will work! But I must be missing something because as it tries to save I get this error:
The Macro:"C:Docume nts and Settings\....'! SaveData' cannot be found.
Sorry, I am fairly new with VB so I am probably missing something simple.. any idea?
In the VBA Editor add a module, I call mine Utils since I usually add other functions and subs to it to do different things. Then place the "Public Sub SaveData()" I gave you there, not in the worksheet area. Then it should see it.
This is awesome, it works perfectly!
Thanks!
This is awesome, it works perfectly!
Thanks!
No problem, glad to help, and that was a method I was not aware of so I learned something as well.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Ryan |
last post by:
I came across a situation that I've been unable to explain and was
hoping somebody had an answer:
I had written an update query which was taking about 8 seconds to run
and considered it too slow. I copied the SQL statement from the query
and tried executing it from code which then ran in 1 second. To make
sure that I didn't miss anything, I copied the SQL statement back into
a query and tried running it again. It now also only took 1...
|
by: Carl |
last post by:
Please can anyone tell me how I can create a macro to save the results
of a query as an excel file? The query is called Student List and I
would like to save it to "My Documents".
We have had some problems with our database crashing. We would
therefore like another way to have access to basic student data and
thought we automatically save the results of this query when we close
Access.
Any suggestions on how we could do this would be...
|
by: Adam Louis |
last post by:
I would like help resolving this problem. I'm a novice who's been
hired to query a hospital database and extract useful information,
available to me only in a dynamically generated, downloadable .mdb.
The query below query runs correctly and without error, but any
attempt to save it causes Access to crash without a message, leaving
the .ldb file. Opening the DB reveals it saved a blank "query1".
I've upgraded to Jet SP 8, and I'm running...
|
by: Nicolae Fieraru |
last post by:
I have two tables, they contain:
Table1: ID1, Name1, Address1, Purchase1
Table2: ID2, Name2, Address2, Purchase2
I need a query which creates Table3 with content from Table1 and Table2.
The records in Table3 have to contain all distinct records from Table1 and
Table2 (records where Name2 and Address2 do not already exist in Table3) and
Any help appreciated. I need these queries as a reference, I consider they
|
by: Smartin |
last post by:
Using Access 97
I am trying to create a form that allows for user entry of search terms
and displays a table of results. I thought I could accomplish this using
a subform but it isn't quite doing what I want it to do.
What I have so far:
Fields to enter search terms
Subform bound to a query
| |
by: Macroman |
last post by:
MS Access XP, running on Win XP, Processor 2.4Ghz , 512Mb RAM, 40Gb Hard
drive
Table 1 has 167,000 records and contains the following fields
tblone_custID
tblone_easting
tblone_northing
Table 2 has 423,000 records and contains the following fields
tbltwo_custID
|
by: eighthman11 |
last post by:
Hello everyone. I have a table linked to an excel spreadsheet. I have
an append query based on this link table. I receive an error when I
run the append query "numeric field overflow". I have noticed that
when I open then save the append query and then run it I get no error.
I am assuming that the error is being caused by Access Optimizing. The
easy solution to my problem is if it is possible to run some VB code
that will save my...
|
by: Nathan Guill |
last post by:
I have an interface that works with an Access back-end. I would like to
store and/or load user defined query strings per each user (i.e. no user can
access another's queries). The idea I had was a "table" stored with the C#
front-end (not in the Access database), but don't know if this is even
possible. If it is, can someone let me know how? Otherwise, how do other
people handle storing and loading query strings for possible later use?
|
by: Wayne |
last post by:
I'm trying to automate the export of a query to a text file using
code. If I export the query manually I get the exact result that I
want i.e. If I select the query then choose File/Export from the menu
bar, choose "Text" as the save format, and then select "fixed width"
as the export format, I end up with a fixed width text file with no
column headers.
I've tried to automate the process using:
DoCmd.OutputTo acOutputQuery,...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |