Solved

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

Posted on 2010-09-03
7
1,507 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 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

Suggested Solutions

Title # Comments Views Activity
Insert with SET how to handle join 6 80
Pivot not using aggregate yield error 3 61
Coldusion - DATA insert syntax problem 12 49
Access 2016 Query with Proper Case logic 2 32
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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…

856 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