Solved

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

Posted on 2010-09-03
7
1,522 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
[X]
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
  • 3
  • 3
7 Comments
 
LVL 33

Accepted Solution

by:
ste5an earned 500 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 69

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 69

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 69

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

726 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