Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2010-09-03
7
Medium Priority
?
1,601 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 35

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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
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 Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

722 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