updating spreadsheet from sql

Posted on 2014-03-10
Last Modified: 2014-10-30
I have a spreadsheet that successfully queries an sql database to give us a set of numbers.  What I would like to do is have this query automatically fill in the next row in the spreadsheet each time it is run.  Currently we are copying the numbers from the sheet that is linked to the database to the next sheet. The auto-fill would help save a lot of time.

Question by:mjburgard
LVL 65

Expert Comment

by:Jim Horn
ID: 39918385
>What I would like to do is have this query automatically fill in the next row in the spreadsheet each time it is run.
Is this 'next row' something that the Stored Procedure can do, before it returns the set that is displayed in Excel?

Stored Procedures are executed read-only, meaning you can't edit it in Excel and expect the source data to update.    Tables and views yes, SP's no.
LVL 10

Expert Comment

ID: 39919947
>a spreadsheet that successfully queries an sql database

How is the spreadsheet set up to query the database?
For example does it use an external connection, is it populated via a vba macro/sub that uses a recordset which is dumped to the spreadsheet, or some other method?

Why is there a need to copy numbers the next sheet?
Can't the sheet be designed to refer directly to the first sheet?
Or is older data over-written each time?


Author Comment

ID: 39920685
The spreadsheet uses an external connection to query the database -
When the query runs, due to the nature of the data that we are accessing, it overwrites the old data every time.
declare @curr int
declare @cstatus int
declare @cnotin int
DECLARE @cepp int
DECLARE @cproduction INT

set @curr= (select min(jobn) from OpenJob where jobStatus in (0,2) and jobN > 0)
SET @cnotin = 0
SET @cepp = 0
SET @cproduction = 0
SET @cdone = 0

WHILE @curr <= (SELECT MAX(jobN) FROM openJob WHERE jobStatus IN (0,2))

	IF EXISTS(SELECT jobn FROM openjob WHERE jobn=@curr AND jobStatus IN (0,2))

			SELECT @cstatus=StatusCoden FROM statuscode where statuscoden = (select statuscode from JobStatus1 where jobn = @curr and transactionN = (select max(transactionN) from jobStatus1 where jobn=@curr))

			if @cstatus IS NULL set @cstatus = ''
			IF @cstatus BETWEEN 35100 AND 35108 SET @cnotin = @cnotin + 1
				IF @cstatus BETWEEN 35109 AND 35254 SET @cepp = @cepp + 1
					IF @cstatus BETWEEN 35255 AND 78100 SET @cproduction = @cproduction + 1
						IF @cstatus >= 78101 SET @cdone = @cdone + 1	

	set @curr = @curr + 1

SELECT @cnotin AS 'Not In', @cepp AS 'EPP', @cproduction AS 'Production', @cdone AS 'Done';

Open in new window

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

LVL 10

Accepted Solution

broro183 earned 500 total points
ID: 39930931

How do you trigger the query?
Are you willing to use VBA?

Please note that I haven't worked with Stored Procedures before so Jim or others may be able to provide a faster or easier solution. I'm learning & guessing as I go but, from the reading that I've just done*, I think that you can populate your sheet using vba. The vba code can identify the first blank row, create your query/external connection, define the output location as the first blank row, populate the table and then kill the query connection. Killing the connection prevents the old data being over-written the next time the data is updated.

The below code is a non-functional example & will need modification for your situation (ie the ConnTextStr constant needs to be changed), but it shows how the first blank row can be identified.
To correctly define the properties of the querytable it may be easiest to record a macro of your actions manually when setting up an external connection.

Option Explicit
Const Delim_Chr As String = "|"    '### this is probably not needed in your code
Const ConnTextStr As String = "blah blah"    '### change as required

Public glb_origCalculationMode As Long
Public glb_origStatusBar As String

Sub CutDownExample()
'12/03/2014, RB: sourced from: C:\Users\Robert\Documents\'UK Company'\Reports\Reporting Analysis v10.xlsb
Dim ws As Worksheet
Dim FBRw As Long    'FirstBlankRow

    Call ToggleRefreshXlApp(False)

    Set ws = ThisWorkbook.ActiveSheet
    With ws

        FBRw = .Range("a" & .Rows.Count).End(xlUp).Row + 1

        With .QueryTables.Add(Connection:= _
                              "TEXT;" & ConnTextStr _
                              , Destination:=.Range("$A" & "$" & FBRw))
            '### change all the following properties as required.
            .Name = "blah"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlOverwriteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = False
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 850
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            '.TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            '.TextFileColumnDataTypes = Array(1)
            .TextFileTrailingMinusNumbers = True
            '### is the next line needed in your code?
            '.TextFileOtherDelimiter = Delim_Chr
            .Refresh BackgroundQuery:=False
        End With
    End With
    Call ToggleRefreshXlApp(True)
msgbox "done"
End Sub

Sub RefreshXlApp()
'Obj: for use when testing, to quickly reset the excel application if code is stopped
'or crashes before "Call ToggleRefreshXlApp(True)" is run.
    With Application
        .EnableEvents = True
        On Error Resume Next
        .Calculation = xlCalculationAutomatic
        On Error GoTo 0
        .StatusBar = False
        .ScreenUpdating = True
        .DisplayFormulaBar = True
        .ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
    End With
End Sub

Sub ToggleRefreshXlApp(RefreshAppSettings As Boolean, Optional ByRef xlApp As Excel.Application)
'Obj: used as a wrapper to increase code execution speed.
    If xlApp Is Nothing Then
        Set xlApp = Excel.Application
    End If
    With xlApp
        If Not RefreshAppSettings Then
            glb_origCalculationMode = .Calculation
            glb_origStatusBar = .StatusBar
        End If
        .EnableEvents = RefreshAppSettings
        On Error Resume Next
        '        .Calculation = IIf(RefreshAppSettings, glb_origCalculationMode, xlCalculationManual)
        .Calculation = IIf(RefreshAppSettings, xlCalculationAutomatic, xlCalculationManual)
        On Error GoTo 0
        .StatusBar = IIf(RefreshAppSettings, vbNullString, CBool(glb_origStatusBar))
        .ScreenUpdating = RefreshAppSettings
    End With
    Set xlApp = Nothing
End Sub

Open in new window

*some links about stored procedures & excel:


Author Comment

ID: 39936871
I haven't been able to try this out yet, it is on the list for this week - hopefully very soon.
LVL 46

Expert Comment

by:Martin Liss
ID: 40413406
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.

Author Closing Comment

ID: 40413407
I modified this and fixed a couple of items that didn't work at first, but i works now.

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

778 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