Run SQL Query, then Save

[Onthefly]

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.Wait Now + TimeValue("24:00: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

[CaptainD]

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.Wait Now + TimeValue("24:00: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.

[Onthefly]

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.

[CaptainD]

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

[Onthefly]

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....

[CaptainD]

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
 
 

[Onthefly]

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:Documents and Settings\....'!SaveData' cannot be found.

Sorry, I am fairly new with VB so I am probably missing something simple.. any idea?

[CaptainD]

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:Documents 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.

[Onthefly]

This is awesome, it works perfectly!

Thanks!

[CaptainD]

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.

[Onthefly]

Quick question, will this loop the time so it saves it every day at the same time without me having to re-open this?

I'm glad you learned as well. The cool thing about this set up is that I now have about 25 excel reports that automatically update themselves every day/week/month and e-mail themselves to the business owners at a specified time, so I don't have to touch them.