Consulting

Results 1 to 2 of 2

Thread: VBA code to export SQL extract result and formatting

  1. #1
    VBAX Newbie
    Joined
    Nov 2011
    Posts
    1
    Location

    VBA code to export SQL extract result and formatting

    Hi Guys,

    I need to take an extract from SQL Server table and populate the data in a no of excel sheets. I generally copy paste it and do some manually formatting. The formatting is same for all the sheets. Can somebody let me know the steps involved if I want to automate the process with the help of macro?

    A detail description would be appreciated.

    Thanks in advance
    Prasan

  2. #2
    VBAX Tutor mohanvijay's Avatar
    Joined
    Aug 2010
    Location
    MADURAI
    Posts
    268
    Location
    here is the simple code for get data from SQl

    [vba]

    Dim R_Con As Object
    Dim R_Rec As Object
    Dim Qqury As String

    Const DB_Con As String = "Provider=SQLNCLI;Server=YourServerNAme;Database=YourDatabaseName;Uid=UserN ame;Pwd=Password;"

    Set R_Con = CreateObject("ADODB.Connection")
    Set R_Rec = CreateObject("ADODB.Recordset")

    Qqury = "" ' You SQL Query here

    R_Con.Open DB_Con
    R_Rec.Open Qqury, R_Con

    Range("a1").CopyFromRecordset R_Rec 'SQL Data into Excel

    R_Rec.Close
    Set R_Rec = Nothing
    R_Con.Close
    Set R_Con = Nothing

    [/vba]

Posting Permissions

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