Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Unanswered: Export sql query to excel vba easier way

    Hi,

    I have a query ( Using sql) containing data that i need to export into excel.
    I am currently exporting it by cut and paste the data, it is
    not a very intuitive way of doing things, what i would prefer is if i
    could have a button on a form which when it was clicked, it
    automatically exported the query to a Excel file. Can anyone help me
    with the VBA that would be neccessary, so i can connect excel and ms sql.

  2. #2
    Join Date
    Aug 2003
    Location
    Melbourne, AUS
    Posts
    3

    Re: Export sql query to excel vba easier way

    Hi

    Try the following,
    1.
    Turn on the VBA Record Button from Excel

    Go to Data>Get External Data>New Database Query.

    Then Select a Database.

    Stop the Recorder.

    *Note that before you select a Database, ensure that you hve a DSN Setup.

    You Can setup a DSN by Going to Control Panel and Selecting a Database.


    2. Then yiou need to assign a button to the sheet via the Control Toolbox.

    -then call the macro from the button


    some sample code for you.

    This is the SQL/VB code inside a VBA Macro


    Sub Query_Download()

    Sheets("TNR").Select

    Cells.Select
    Cells.Clear


    With ActiveSheet.QueryTables.Add( _
    Connection:="ODBC;DRIVER=SQL Server;SERVER=;UID=;PWD=;APP=Microsoft Open Database Connectivity;" & _
    "DATABASE=TNR", Destination:=Range("A1"))

    ''Execute the SQL script to retrieve the date''



    .CommandText = "SELECT Name" & _
    "FROM Database"

    .Name = "List"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = True
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=True

    End With


    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Match"
    Range("c1").Select

    End Sub


    *Note that you would have to assign a value to Servwer, USerID and Password(Server is the IP Address)

    This code won't work on your PC, but it is a sample


    Originally posted by lh2001
    Hi,

    I have a query ( Using sql) containing data that i need to export into excel.
    I am currently exporting it by cut and paste the data, it is
    not a very intuitive way of doing things, what i would prefer is if i
    could have a button on a form which when it was clicked, it
    automatically exported the query to a Excel file. Can anyone help me
    with the VBA that would be neccessary, so i can connect excel and ms sql.

  3. #3
    Join Date
    Aug 2003
    Location
    Phoenix, AZ
    Posts
    12
    You can also use this one line VB script if the query has already been saved via MS-query (see my reply to your post in the MS SQL forum):

    Range("qABCD").QueryTable.Refresh

    The range name is created automatically when you name the query in Excel ( Right click on the any field in the query result in Excel and click on "Data Range Properties". Thats where you would assign or change the name of the query, which in turn defaults to the range name of "qABCD"). If you have set up the OBDC driver for excel to use correctly, the db password & login id will be saved automatically with the query & spreadsheet.

    If you are comfortable in Excel, MS Query can be a really powerful tool to export data to Excel and manipulate there.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •