Post reply

Extracting data using VBA

  • July 4, 2008 at 3:46 am

    #187797

    I'm trying to create a dashboard in excel that will enable me to press a button and it will run SQL and create a CSV file. Probably by running SQL in VBA to extract the data and enter each record into a CSV file including the header.

    I want to be able to store the location of my original SQL files(names and locations pre defined) and pull the txt into an excel sheet(or run SQL query through Excel as an automation process if we can send commands to SQL) and query the sheet in VBA to run it and create the record set in a CSV file (names and locations pre defined).

    My main problem is how to create the CSV file and define the name and location of where I want it stored.

    Pulling the SQL file into Excel and building up the SQL shouldn't be a problem, but it would be good if I can use automation on SQL query analyzer.

    The reason for this approach rather than using the tools in SQL is that I sit in finance and without dealing with IT and waiting several months we cant schedule any queries.

    Any help would be much appreciated.

    Thanks

  • July 4, 2008 at 3:57 am

    #838283

    Have you tried creating an ODBC connection to SQL and then linking to this via

    Data --> Import External Data --> New Database Query

    You can write your queries there and save them, also you can refresh them at the push of the refresh button.

  • July 4, 2008 at 4:02 am

    #838285

    I want the process to write to a csv file (the same way a log file is created) a record at a time until the end of the query results.

  • July 4, 2008 at 5:42 am

    #838318

    Hi Phil,

    If you need better response I guess you should also post this in a (VB) programming forum.

    I think you should create an ODBC within Excel-VBA to the SQL server. The code would be something like this:

    Dim objConnection

    Dim sServerName

    Dim sDatabaseName

    Dim sUserName

    Dim sPassword

    '--- Open a connection to SQL-server

    Set objConnection = CreateObject("ADODB.Connection")

    '--- apply values for Data Source, Initial Catalog, User ID and password

    objConnection.ConnectionString = "Provider=SQLOLEDB;" & _

    "Data Source=" & sServerName & ";" & _

    "Initial Catalog=" & sDatabaseName & ";" & _

    "User ID=" & sUserName & ";" & _

    "Password=" & sPassword

    objConnection.CommandTimeout = 60

    objConnection.Open

    The VB code to write to a file is this:

    Const ForWriting = 2

    Const ForAppending = 8

    Set fso = CreateObject("Scripting.FileSystemObject")

    Set WshShell = CreateObject("WScript.Shell")

    sFile = "C:\temp\test.txt"

    Set ts = fso.OpenTextFile(ServicesFile, ForAppending, False)

    ' place line below in a FOR...EACH loop to write multiple lines

    ts.WriteLine "Put text to write here"

    ts.Close

    One remark: The code I provided is written in VB-script. Although this is very similar to VBA :w00t: it is possible it is not working :ermm:

    Microsoft does have an excellent website for scripting with lots of samples in different area's.

    http://www.microsoft.com/technet/scriptcenter/scripts/default.mspx?mfr=true

    I hope this is helpfull

    Kind regards,

    Hans

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • July 4, 2008 at 5:47 am

    #838323

    Thanks very much.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply