Solved

updating spreadsheet from sql

Posted on 2014-03-10
8
60 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
Comment Utility
>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
Comment Utility
>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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 10

Accepted Solution

by:
broro183 earned 500 total points
Comment Utility
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
Comment Utility
I haven't been able to try this out yet, it is on the list for this week - hopefully very soon.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
I modified this and fixed a couple of items that didn't work at first, but i works now.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

771 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

15 Experts available now in Live!

Get 1:1 Help Now