updating spreadsheet from sql

Posted on 2014-03-10
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 66

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

LVL 10

Accepted Solution

broro183 earned 2000 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 49

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

770 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