Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Using Scheduled tasks to run VBA in excel to access SQL database

Posted on 2010-09-03
7
Medium Priority
?
1,613 Views
Last Modified: 2012-05-10
Hello,

I have some VBA in an excel file that completes a SQL query into a data warehouse and uses that data to create a report and email that report out.

I would like to set-up a windows scheduled task to open the excel workbook and run the code during non-office hours.

The only problem is that the SQL query requires a non-network user name and password and I can't figure out how to instruct the scheduled task to use two sets up passwords one to login into the system and another to entering in the prompt fields to complete the SQL query using MS Query.

Any ideas would be greatly appreciated.

Thanks
0
Comment
Question by:Tbyrd777
  • 3
  • 3
7 Comments
 
LVL 36

Accepted Solution

by:
ste5an earned 1500 total points
ID: 33602425
First of all extract the VBA from your Excel sheet to an external *.VBS file. Running a VB Script is much less error prone then running it inside an Excel macro. Use Excel Automation in the VB Script:

  http://www.suite101.com/content/automating-word-and-excel-with-vba-and-vbscript-a83289
  http://tips-testing.blogspot.com/2008/07/excel-automation-using-vbscript.html

What do you mean with "SQL query requires a non-network user name"? Do you need to authenticate yourself before running this query? Does "non-network" mean that you're using SQL Server Authentication? In this case you can simply add the credentials to the connection string. Standard Security at

  http://connectionstrings.com/sql-server-2008#p2


mfG
--> stefan <--

0
 
LVL 71

Expert Comment

by:Qlemo
ID: 33602755
There is no way to provide the SQL credentials from outside. As said already, you need to integrate that into the connection string.

I do not agree to extracting the VBA code to VBS in this case. VBS is more restricted to which keywords and features you can use, in particular data typing and error handling. If the code is there already in VBA, there is no need to pull it off the XLS file, which would require you to rework the script.
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 33603080
BTW, this is the VBS code to run the Macro "Auto_Open"  of an Excel workbook
Dim excel, wb
strFile = "c:\temp\ee\tst\autoopen.xls"

set excel=WScript.CreateObject ("Excel.Application")
excel.visible = false
set wb = excel.Workbooks.Open(strFile)
excel.Run("Auto_Open")

' Cleanup
wb.Close
set wb = Nothing
excel.Quit
Set excel = Nothing

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Tbyrd777
ID: 33604245
I've attached the VBA code that I'm using to query the database.  However, when I run the macro the system automatically prompts me for my database user name and password.  I did some more research and I'm pretty sure that I need to somehow incorporate the user name and password into the "Connection:=" string; however, I'm not sure how to do this.  Any ideas would be greatly appreciated.
Sub Import_Store_Regionstest()

Dim Regions As Worksheet
Set Regions = ThisWorkbook.Worksheets("Store Regions")

'Delete Prior Data
Regions.Select

lrow = LastCell(Regions).Row
lcol = LastCell(Regions).Column

Regions.Range(Cells(1, 1), Cells(lrow, lcol)).Select

Selection.Delete Shift:=xlUp

Dim SQL As String
SQL = "SELECT Store_Dim.Restaurant_Num, Store_Dim.Store_Name, Store_Dim.Owner2, Store_Dim.Store_Area FROM DW_PROD.dbo.Store_Dim Store_Dim WHERE (Store_Dim.Owner2='Corporate')ORDER BY Store_Dim.Restaurant_Num"

     With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=Smash Data;Description=Smashburger Datawarehouse;UID=sa;APP=Microsoft Office 2003;WSID=74P89F1;DATABASE=DW_PROD" _
        , Destination:=Range("A1"))
        .CommandText = SQL
        .Name = "Store_Region_Data_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = _
        "C:\Documents and Settings\hoodt\Application Data\Microsoft\Queries\Store_Region_Data.dqy"
        .Refresh BackgroundQuery:=False
    End With

End Sub

Open in new window

0
 

Author Comment

by:Tbyrd777
ID: 33604370
I stored my password into a string variable named "pass" and then added the following segment to the command string ;PWD=" & pass & ";....
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 33604518
It is ok to accept your own answer, since you found the missing part with some rework, but you were pointed into that direction by an Expert.  ste5an did tell you you need to change your connection string (and I confirmed that), and so this should be accepted as part of the solution (with grade "B" and some points).
0
 

Author Closing Comment

by:Tbyrd777
ID: 33606707
This answer pointed me in the right direction.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

972 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