Solved

updating spreadsheet from sql

Posted on 2014-03-10
8
61 Views
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.

Thanks
0
Comment
Question by:mjburgard
8 Comments
 
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.
0
 
LVL 10

Expert Comment

by:broro183
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?

Rob
0
 
LVL 1

Author Comment

by:mjburgard
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.
Set NOCOUNT ON
declare @curr int
declare @cstatus int
declare @cnotin int
DECLARE @cepp int
DECLARE @cproduction INT
DECLARE @cdone 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))
BEGIN

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

			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
			ELSE 
				IF @cstatus BETWEEN 35109 AND 35254 SET @cepp = @cepp + 1
				ELSE
					IF @cstatus BETWEEN 35255 AND 78100 SET @cproduction = @cproduction + 1
					ELSE
						IF @cstatus >= 78101 SET @cdone = @cdone + 1	


		END	
	set @curr = @curr + 1
END

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

Open in new window

0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 10

Accepted Solution

by:
broro183 earned 500 total points
ID: 39930931
hi,

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
            .Delete
        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:
http://datapigtechnologies.com/blog/index.php/running-a-sql-stored-procedure-from-excel/
http://blogs.office.com/2010/06/07/running-a-sql-stored-procedure-from-excel-no-vba/
http://codebyjoshua.blogspot.co.nz/2012/01/get-data-from-sql-server-stored.html
http://www.sqlservercentral.com/articles/Stored+Procedures/63537/
http://dailydoseofexcel.com/archives/2004/12/13/parameters-in-excel-external-data-queries/

hth
Rob
0
 
LVL 1

Author Comment

by:mjburgard
ID: 39936871
I haven't been able to try this out yet, it is on the list for this week - hopefully very soon.
0
 
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.
0
 
LVL 1

Author Closing Comment

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

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

Suggested Solutions

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
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 demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

867 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now