Solved

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

Posted on 2010-09-03
7
1,567 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 34

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 70

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 70

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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 

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 70

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

Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

Question has a verified solution.

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

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…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
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…

626 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