• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 93
  • Last Modified:

updating spreadsheet from sql

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.

1 Solution
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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.
>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?

mjburgardAuthor Commented:
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks


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:

mjburgardAuthor Commented:
I haven't been able to try this out yet, it is on the list for this week - hopefully very soon.
Martin LissRetired ProgrammerCommented:
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
mjburgardAuthor Commented:
I modified this and fixed a couple of items that didn't work at first, but i works now.

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now