Using VBA to pull SQL data into Excel?

  • rbarryyoung (6/16/2008)


    I would be very interested if you could find a way to make that happen. I haven't tried it in Excel 2007, but in every other version that I have tried, the automatic data updates did overwrites, not inserts.

    Try right-clicking in the data range, and looking through "data range properties". I've got Excel 2002 right-now. I get options to either Insert Cells/delete unused, Insert/delete entire rows, or Overwrite. It will also allow me to define line totals which will get copies up or down assuming they're adjacent to the data range.

    Note that this deals with rows, NOT columns. Variable columns means SOL I think.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • rbarryyoung (6/16/2008)


    Jeff Moden (6/16/2008)


    rbarryyoung (6/16/2008)


    Jeff Moden (6/15/2008)


    rbarryyoung (6/15/2008)


    On the other hand, if your sheets need to be resized based on the number of rows returned, then automated "Get External Data" refreshes can be pretty hard to get working right. I think have VBA code (somewhere) for doing this if you still need it.

    Nah... sheets resize auto-magically.

    But pointers to them (like data ranges) do not.

    Actually, I think data-ranges can be made to be self expanding... it's been a very long while since I've had to do something like this so I'll have to try it... not 100% sure about it, yet...

    I would be very interested if you could find a way to make that happen. I haven't tried it in Excel 2007, but in every other version that I have tried, the automatic data updates did overwrites, not inserts.

    I just tried it using Excel 2003... if you right click on the external data range and select "Data Range Properties", the "Name" that appears is actually a range name and it expands and contracts correctly and automatically when you refresh the external data range. It's selectable from the range pull down and does all of the things you would expect any named range to do as well as being self sized based on the refresh.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Crud... Matt beat me to it... 🙂

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • montgomery johnson (6/16/2008)


    I would be interested in anything you've got. There will be future changes on additional spreadsheets.

    OK, I checked and unfortunately I do not have much useful stuff that I can share at the moment (customer ownership and confidentialty precludes the specific stuff). Anyway, this is just some common utility functions, it is to Access, instead of SQL, but all you should have to do is change the connection string to fit. And by the way, this being VBA and COM, etc., it uses ADO, not ADO.net.

    'APP_DataBase (vba)

    '

    ' This is the module for the APP application's

    'Database functions, routines and constants.

    '

    '2007-06-08 B.Young Created

    '

    Option Explicit

    Public appDB As New ADODB.Connection

    Public Function IsOpenDB() As Boolean

    ' function to track the DB status

    IsOpenDB = (appDB.State <> 0) ' 0=closed

    End Function

    Public Sub CloseDB()

    'Close the database if it is open.

    If IsOpenDB Then appDB.Close

    End Sub

    Public Function OpenDB() As Boolean

    'Open the Database and indicate if sucessful

    Static sPrev As String

    Dim sFile As String

    If IsOpenDB Then

    OpenDB = True 'we are already open

    Exit Function

    End If

    If sPrev = "" Then sPrev = GetSetting(APP_AppName, "History", "DBName")

    With Application.FileDialog(msoFileDialogFilePicker)

    'specify the file open dialog

    .AllowMultiSelect = False

    .Filters.Clear

    .Filters.Add "Access Workbooks", "*.mdb"

    .Filters.Add "All Files", "*.*"

    .InitialFileName = sPrev

    .Title = "Open TIP Database"

    .Show

    If .SelectedItems.Count > 0 Then

    sFile = .SelectedItems(1)

    Else 'user canceled ...

    OpenDB = False

    Exit Function

    End If

    End With

    'appDB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\byoung\APP\Main.mdb"

    appDB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFile

    On Error Resume Next

    appDB.Open

    If Err.Number <> 0 Then

    MsgBox "Error(" & Err.Number & "): " & Err.Description, vbOKOnly + vbCritical, "Error in OpenDB"

    OpenDB = False

    Exit Function

    End If

    'Opened ok, so finsh-up and exit

    OpenDB = True

    sPrev = sFile

    SaveSetting APP_AppName, "History", "DBName", sPrev

    End Function

    Public Function DBLookup(SQLCommand As String, Optional DefaultVal As Variant = "") As Variant

    'Execute a SQL statement and return a single value.

    Dim rs As Recordset

    On Error GoTo ErrHandler 'Handle any/all DB errors

    If Not IsOpenDB Then OpenDB

    ' execute the command

    Set rs = appDB.Execute(SQLCommand)

    ' extract the first field of the first record returned

    rs.MoveFirst

    DBLookup = rs.Fields(0).Value

    Exit Function

    ErrHandler:

    ' whatever the error is, just assign the default value and return

    DBLookup = DefaultVal

    Exit Function

    End Function

    Public Sub DBExecute(SQLCommand As String, Optional RecordsAffected As Long = 0)

    'Execute a SQL statement that returns no values.

    If Not IsOpenDB Then OpenDB

    appDB.Execute SQLCommand, RecordsAffected

    End Sub

    '===================

    (edit: added vb formatting tag. Nice!)

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Cool. I stand corrected. 🙂

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Once you have your basic query in place with Import External Data and an ODBC connection, you can use the following VBA code instead, which has one advantage, that of allowing you to change your query on the fly, since it's only a text string ("MyQuery$"):

    With Worksheets(1).QueryTables(1)

    .Destination = Worksheets(1).Range("A1")

    .CommandText = MyQuery$

    .Refresh (False)

    End With

  • Also allows it to possibly be slower because it will recompile every time it's used.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks guys. You've given me something verging on excitement.

  • Matt, Jeff:

    FYI, I've already used this trick today to put up a "quick & dirty" Service Broker monitor. sweet...

    Thanks again,

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Now there's a left turn across the field....I've used Excel for a lot of things, but that's a new one for me...:cool:

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 10 posts - 16 through 24 (of 24 total)

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