运行SQL Query，然后保存 [英] Run SQL Query, then Save
问题描述
我正在尝试自动化excel报告，以便它自动发布SQL查询，然后每24小时保存一次文件。目前我可以做一个或另一个（发布sql或保存）但我不能同时运行两个。
到目前为止，我的excel文件和我的excel文件之间有一个ODBC连接MS SQL数据库，并且每1440分钟刷新一次SQL查询。
我还有一个VB计时器设置为每24小时运行一次，每天早上保存工作簿。单击按钮时计时器启动。这是我用来的脚本
Sub Save（）
ActiveWorkbook.Save
timer1
End Sub
Sub SaveName（）
ActiveWorkbook.SaveAs文件名：=" C：\ ..."
timer1
End Sub
子计时器1（）
Application.Wait Now + TimeValue（" 24:00） ：00）
保存
结束子
私有子CommandButton1_Click（）
timer1 < br $>
End Sub
然而，当我点击按钮运行计时器时，它会减慢所有内容，以便我的MS SQL查询计时器不倒计时。
我的问题是如何让这两个同时工作。我是否应该在VB中运行SQL查询，因此两个函数都使用相同的计时器。如果是这样，我该怎么做。如果我能搞清楚这一点，我将能够运行几个报告，每天，每周，每月等更新，保存和发送电子邮件......
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
推荐答案
我正在尝试自动化excel报告，以便它自动发布SQL查询，然后每24小时保存一次文件。目前我可以做一个或另一个（发布sql或保存）但我不能同时运行两个。
到目前为止，我的excel文件和我的excel文件之间有一个ODBC连接MS SQL数据库，并且每1440分钟刷新一次SQL查询。
我还有一个VB计时器设置为每24小时运行一次，每天早上保存工作簿。单击按钮时计时器启动。这是我用来的脚本
Sub Save（）
ActiveWorkbook.Save
timer1
End Sub
Sub SaveName（）
ActiveWorkbook.SaveAs文件名：=" C：\ ..."
timer1
End Sub
子计时器1（）
Application.Wait Now + TimeValue（" 24:00） ：00）
保存
结束子
私有子CommandButton1_Click（）
timer1 < br $>
End Sub
然而，当我点击按钮运行计时器时，它会减慢所有内容，以便我的MS SQL查询计时器不倒计时。
我的问题是如何让这两个同时工作。我是否应该在VB中运行SQL查询，因此两个函数都使用相同的计时器。如果是这样，我该怎么做。如果我能解决这个问题，我将能够运行几个报告，每天，每周，每月等更新，保存和发送电子邮件......
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
Excel附带一个自动保存附加组件，为什么不使用它。应该列在工具下面，如果不是你需要磁盘来添加它。
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.
这是我最初尝试的，它可以工作，但自动保存功能将其保存为.xar文件并将其保存为隐藏文件，以防止我重新发生的电子邮件软件程序识别它。如果我能找到解决这个问题的方法，使用自动保存功能将是我的第一选择。
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.
好的，我重新阅读你的帖子，你有Excel文件更新/插入Excel每晚都会更改为MS Sql数据库，对吗？
为什么不在它们制作完成后再执行更新。我在使用ADO的工作中创建了一个，当你离开单元格时，更新记录字段
如果我在第1列中没有值那么表示新记录，所以它插入表格
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
这篇关于运行SQL Query，然后保存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
