Solved

Question about .VBS script creation to export DB to excel format and paginate excel max records

Posted on 2008-10-21
27
478 Views
Last Modified: 2013-11-30
This one may be a bit complicated.  I have a stored procedure we will call sp_report on a SQL server (2000 enterprise/clustered).  I am looking to for a .VBS script (for automation purposes) that can execute that stored procedure and automatically export the results to an excel file. Also there are greater than 80,000 rows of results on the sql db, so I would need the script to make a new workbook for every 65000 or so records generated via the output of the stored procedure and continue exporting the results to that new workbook (in the same file) and perhaps continue doing this until all results have been exported and the procedure has completed its course.


Thank you for your help in advance.
0
Comment
Question by:smyers051972
  • 13
  • 10
  • 3
27 Comments
 
LVL 1

Author Comment

by:smyers051972
ID: 22785987
Anyone?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22788046
Bit nasty, but can be done in straight SQL if needed...

steps would be :

1) copy a "blank" spreadsheet as a template to load into
2) use the SP to load a temp table
3) open the excel spreadsheet
4) create some logic to split / identify which worksheet whill be loaded (e.g. count number of records, divide by two - half to the first, half to the second, or 60000 to the first the rest if any to the second etc)
5) insert to the spreadsheet
6) close  / mop up...

the only thing SQL cannot do is the create of a brand new spreadsheet...
0
 
LVL 1

Author Comment

by:smyers051972
ID: 22788818
Hmmm well I have some VB that does a lot of merging already but was thinking if VB can call the sql SP itself and output to a comma dliminated text file, then perhaps some VB could be used to import the data and auto paginate?

I think thats how I am peicing it into my head so far. What do you (or anyone) think about that?
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 22791940
Sure, can do it in VB....  I think that when it gets into these types of circumstances - it is sometimes easier to hit the code - VB or SQL or whatever is your favourite code base...
0
 
LVL 1

Author Comment

by:smyers051972
ID: 22792185
Great, I am looking for a VB Script that will execute the SP and output the results to an excel sheet but paginate the results automatically, say every 65000 records start a new work sheet.

Can someone help build it? I would like to stick with VB.

Thanks!
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22808583
Hi, smyers051972, VB is not my cup of tea, so will let more abled experts attend to your requirements. I will keep a watch and if needed, post a SQL solution for you.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 22818709
I am always suspicious about splitting data into worksheets. Excel is a presentation tool - what human can make sense of more than 65,000 rows of data?
I guess you already know that if you export as CSV (text) you aren't faced with this limit but you probably have some user downstream in the process that just has to have it in Excel?
Anyway back to the question, it should be a simple matter to do some VBA in Excel to do this. Would you prefer a solution in VBA or VB Script? Basically I'll have a quick crack at it but if I can't guarantee that I won't get distracted.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 22818867
OK here's some 'brute force' VBA code that will do what you want.
Open Excel, press ALT-F11, paste it in and press F5 to test (its looking for an SP called p_LotsOfData which I can post if you want)
-I'm sure there are quicker ways to do it
-The fields are explicitly mapped in the code. I beleive you can use CopyFromRecordSet (http://support.microsoft.com/kb/246335) to do this more intelligently
-The sheets end up backwards
-There's probably a way to batch 65000 records into the recordset rather than stepping through at a row level.

Have fun

Private Sub Main()
 
Dim oCon As Object 'ADODB.Connection
Dim oCom As Object 'ADODB.Command
Dim oRst As Object 'ADODB.Recordset
Dim iReturnValue As Integer
Dim iCurrentRow As Long
Dim iCurrentWorkSheet As Integer
Dim dStartTime As Date
Dim dEndTime As Date
 
 
dStartTime = Time
 
'----------------------------------------------------------------------
' Create ADO objects
Set oCon = CreateObject("ADODB.Connection")
Set oCom = CreateObject("ADODB.Command")
Set oRst = CreateObject("ADODB.Recordset")
'----------------------------------------------------------------------
 
 
 
 
'----------------------------------------------------------------------
' Open a trusted database connection
oCon.Provider = "SQLOLEDB"
oCon.Open "Server=localhost;Database=scratch;Trusted_Connection=yes"
If oCon.State <> 1 Then
    Call MsgBox("Could not connect to the database")
    Exit Sub
End If
'----------------------------------------------------------------------
 
 
'----------------------------------------------------------------------
' Set up the stored procedure call
Set oCom.ActiveConnection = oCon
oCom.CommandText = "p_LotsOfData"
oCom.CommandType = 4            ' adCmdStoredProc
oCom.Parameters.Refresh         ' Load the SP parameters attributes into the object
'----------------------------------------------------------------------
 
 
 
 
 
 
'----------------------------------------------------------------------
' Run the stored procedure, retreive the result and close the connection
iCurrentWorkSheet = 1
iCurrentRow = 1
Set oRst = oCom.Execute
 
Do Until oRst.EOF
    If iCurrentRow = 64000 Then
        ActiveWorkbook.Worksheets.Add
        iCurrentWorkSheet = iCurrentWorkSheet + 1
        iCurrentRow = 1
    End If
    
    
    ActiveSheet.Cells(iCurrentRow, 1) = oRst(0)
    ActiveSheet.Cells(iCurrentRow, 2) = oRst(1)
    
    
    'ActiveWorkbook.Worksheets(iCurrentWorkSheet).Cells(iCurrentRow, 1) = oRst(0)
    'ActiveWorkbook.Worksheets(iCurrentWorkSheet).Cells(iCurrentRow, 2) = oRst(1)
    iCurrentRow = iCurrentRow + 1
    oRst.MoveNext
Loop
 
oCon.Close
'----------------------------------------------------------------------
 
 
 
 
dEndTime = Time
 
 
'----------------------------------------------------------------------
' Show a message depending on the result
 
If iReturnValue = 0 Then
    Call MsgBox("Stored procedure completed successfully" & vbCrLf & vbCrLf & CStr(dStartTime) & " - " & CStr(dEndTime), 64)
Else
    Call MsgBox("An error occured whilst calling the SP", 48)
End If
'----------------------------------------------------------------------
 
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:smyers051972
ID: 22822730
Hey thats an awesome try!

I did it the way you said and got an application error.

HOWEVER can this not just be done with a .VBS script? it would be much better that way for my situation.

Thanks!
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 22827096
Yeah sure you can do the whole thing in just a VBScript. It was actually adapted from a VBScript I had.
Its just a bit harder to debug in VBScript so I usually do it in the host application first, get it working, then convert to VBScript.
To convert it to VBScript, two basic things need to be done:
1. Take out all the 'As' parts - VBScript has no data types
2. Add some code to open an Excel file and manipulate it
Anyway what application error did you get, and which line did it stop on? I'm guessing the stored procedure called 'p_LotsOfData' doesn't exist.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 22827589
Here's the question which has some of that VBA in VBS format:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/DTS/Q_23852072.html
Its got syntax for calling an SP in VBScript. You basically need to add the pagination and Excel object bits to it. I can help if you want.
0
 
LVL 1

Author Comment

by:smyers051972
ID: 22833296
Ok,

I read over the article you mentioned and I think that I would need a couple changes.

1. Does it paginate every 65000 or so records creating a new tab each 65000 records?
2. How can I remove the date prompting from the script? I dont think I would need that because the SP auto parses dates needed for the mailing list.
3. Where does it save the excel sheet to?

I will attach the code from the article above for ease.

Thank you again!
' from: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/DTS/Q_23852072.html 
'==============================================================================
' RunSP
'
' This VBScript prompts for a value, calls a stored procedure with that value
' and shows a message based on the sp result.
' make sure that the SP has SET NOCOUNT ON
'==============================================================================
Dim oCon
Dim oCom
Dim iReturnValue
 
Call Main
 
Private Sub Main()
Dim sDate
 
'----------------------------------------------------------------------
' Create ADO objects
Set oCon = CreateObject("ADODB.Connection")
Set oCom = CreateObject("ADODB.Command")
'----------------------------------------------------------------------
 
'----------------------------------------------------------------------
' Open a trusted database connection
oCon.Provider = "SQLOLEDB"
oCon.Open "Server=localhost;Database=scratch;Trusted_Connection=yes"
If oCon.State <> 1 Then
	Call MsgBox("Could not connect to the database")
	Exit Sub
End If	
'----------------------------------------------------------------------
 
'----------------------------------------------------------------------
' Retrieve the parameter value
sDate = InputBox("Please Enter Date","Date Input",CStr(Date))
If Not IsDate(sDate) Then
	Call MsgBox("Thats not a valid date, please use the format DD/MM/YYYY",48)
	Exit Sub
End If
'----------------------------------------------------------------------
 
'----------------------------------------------------------------------
' Set up the stored procedure call
Set oCom.ActiveConnection = oCon
oCom.CommandText = "p_TestSPDate"
oCom.CommandType = 4			' adCmdStoredProc
oCom.Parameters.Refresh			' Load the SP parameters attributes into the object
oCom.Parameters(1) = sDate		' Replace this with the value of the input parameter
'----------------------------------------------------------------------
 
'----------------------------------------------------------------------
' Run the stored procedure, retreive the result and close the connection
Call oCom.Execute
iReturnValue = oCom.Parameters(0)
oCon.Close
'----------------------------------------------------------------------
 
'----------------------------------------------------------------------
' Show a message depending on the result
 
If iReturnValue = 0 Then
	Call MsgBox("Stored procedure completed successfully",64)
Else
	Call MsgBox("An error occured whilst calling the SP",48)
End If
'----------------------------------------------------------------------
 
End Sub

Open in new window

0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 22837454
It was really just an example of connecting to an SP in VBScript. It doesn't do any Excel functions at all and doesn't do any pagination.
If you tell me what error you got in the original VBA code I posted, we can fix that, then I can convert it to VBScript if you wish.
The fact that you are already using Excel means you may as well leave it as VBA though. You can't do any Excel manipulation in VBScript if you don't have Excel already installed, so you may as well leave it in Excel VBA.
0
 
LVL 1

Author Comment

by:smyers051972
ID: 22840701
OK, I got a runtime error when executing it, Operation not allowed when object closed at line:
Do Until oRst.EOF

Now what I suspect is, I ran this on our test bed because I cant execute it on the live environment with out testing scripts first, I am thinking that in this case there was just not 64000 records on the test side, or am I wrong?

I tried to change it to 100 records and still the same error, what I wonder is if it's an authentication issue? You set trusted connection to yes which I assume uses AD to log me into the DB server and my account has even admin rights to all our DB's.  Thanks and let me know if I did something wrong, I did however confirm the DB source info to make sure I entered the correct server.

As for converting it to .VBS that would be awesome! Thanks a lot!
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 22847709
Can you  run your stored procedure in Management Studio and ensure that it returns records, and also ensure that its first line is 'SET NOCOUNT ON'
0
 
LVL 1

Author Comment

by:smyers051972
ID: 22868175
Hi,

Sorry was away for a few days.  I have the SP and SET NOCOUNT is set to ON.

Thanks!
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 22873916
OK I've recreated the error here and its because your SP returns zero records. Thats OK, thats a valid state, so I've altered the code to do nothing of there are no records.
The attached code will work, but you'll see no records in your Excel document, because its returning no records.
You can test this by running the SP in Management Studio

Private Sub Main()
 
Dim oCon As Object 'ADODB.Connection
Dim oCom As Object 'ADODB.Command
Dim oRst As Object 'ADODB.Recordset
Dim iReturnValue As Integer
Dim iCurrentRow As Long
Dim iCurrentWorkSheet As Integer
Dim dStartTime As Date
Dim dEndTime As Date
 
 
dStartTime = Time
 
'----------------------------------------------------------------------
' Create ADO objects
Set oCon = CreateObject("ADODB.Connection")
Set oCom = CreateObject("ADODB.Command")
Set oRst = CreateObject("ADODB.Recordset")
'----------------------------------------------------------------------
 
 
 
 
'----------------------------------------------------------------------
' Open a trusted database connection
oCon.Provider = "SQLOLEDB"
oCon.Open "Server=localhost;Database=scratch;Trusted_Connection=yes"
If oCon.State <> 1 Then
    Call MsgBox("Could not connect to the database")
    Exit Sub
End If
'----------------------------------------------------------------------
 
 
'----------------------------------------------------------------------
' Set up the stored procedure call
Set oCom.ActiveConnection = oCon
oCom.CommandText = "p_LotsOfData"
oCom.CommandType = 4            ' adCmdStoredProc
oCom.Parameters.Refresh         ' Load the SP parameters attributes into the object
'----------------------------------------------------------------------
 
 
 
 
 
 
'----------------------------------------------------------------------
' Run the stored procedure, retreive the result and close the connection
iCurrentWorkSheet = 1
iCurrentRow = 1
Set oRst = oCom.Execute
 
If oRst.State = 1 Then
    Do Until oRst.EOF
        If iCurrentRow = 64000 Then
            ActiveWorkbook.Worksheets.Add
            iCurrentWorkSheet = iCurrentWorkSheet + 1
            iCurrentRow = 1
        End If
        
        
        ActiveSheet.Cells(iCurrentRow, 1) = oRst(0)
        ActiveSheet.Cells(iCurrentRow, 2) = oRst(1)
        
        
        'ActiveWorkbook.Worksheets(iCurrentWorkSheet).Cells(iCurrentRow, 1) = oRst(0)
        'ActiveWorkbook.Worksheets(iCurrentWorkSheet).Cells(iCurrentRow, 2) = oRst(1)
        iCurrentRow = iCurrentRow + 1
        oRst.MoveNext
    Loop
End If
oCon.Close
'----------------------------------------------------------------------
 
 
 
 
dEndTime = Time
 
 
'----------------------------------------------------------------------
' Show a message depending on the result
 
If iReturnValue = 0 Then
    Call MsgBox("Stored procedure completed successfully" & vbCrLf & vbCrLf & CStr(dStartTime) & " - " & CStr(dEndTime), 64)
Else
    Call MsgBox("An error occured whilst calling the SP", 48)
End If
'----------------------------------------------------------------------
 
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:smyers051972
ID: 22878465
Works great,  now can we get this into VBS some how? =)
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 500 total points
ID: 22882283
Here it is. Its incredibly slow. The VBA one takes about a second. I don't know how long the VBScript one takes cause I don't have the patience to wait.... I'm guessing half an hour for 70,000 records. There may be ways to speed it up a bit.
Do you really need it in VBScrtip? If you leave it in Excel you can put fancy dialogs and windows and stuff, and obviously its a lot faster.

Call Main()
 
 
Sub Main()
 
Dim oCon 		' ADODB.Connection
Dim oCom 		' ADODB.Command
Dim oRst 		' ADODB.Recordset
Dim iReturnValue 	' As Integer
Dim iCurrentRow 	' As Long
Dim iCurrentWorkSheet 	' As Integer
Dim oExcel		' As Excel.Application
Dim oExcelWorkbook	' As Excel.Workbook
'----------------------------------------------------------------------
' Create ADO objects
Set oCon = CreateObject("ADODB.Connection")
Set oCom = CreateObject("ADODB.Command")
Set oRst = CreateObject("ADODB.Recordset")
'----------------------------------------------------------------------
 
 
 
'----------------------------------------------------------------------
' Create Excel objects
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible=True
Set oExcelWorkbook = oExcel.Workbooks.Add
Call oExcelWorkbook.SaveAs("C:\ExcelOutput.XLS")
'----------------------------------------------------------------------
 
 
 
 
'----------------------------------------------------------------------
' Open a trusted database connection
oCon.Provider = "SQLOLEDB"
oCon.Open "Server=localhost;Database=scratch;Trusted_Connection=yes"
If oCon.State <> 1 Then
    Call MsgBox("Could not connect to the database")
    Exit Sub
End If
'----------------------------------------------------------------------
 
 
'----------------------------------------------------------------------
' Set up the stored procedure call
Set oCom.ActiveConnection = oCon
oCom.CommandText = "p_LotsOfData"
oCom.CommandType = 4            ' adCmdStoredProc
oCom.Parameters.Refresh         ' Load the SP parameters attributes into the object
'----------------------------------------------------------------------
 
 
 
 
 
 
'----------------------------------------------------------------------
' Run the stored procedure, retreive the result and close the connection
iCurrentWorkSheet = 1
iCurrentRow = 1
Set oRst = oCom.Execute
 
If oRst.State = 1 Then
    Do Until oRst.EOF
        If iCurrentRow = 64000 Then
            oExcelWorkbook.Worksheets.Add
            iCurrentWorkSheet = iCurrentWorkSheet + 1
            iCurrentRow = 1
        End If
        
        oExcelWorkbook.ActiveSheet.Cells(iCurrentRow, 1) = oRst(0)
        oExcelWorkbook.ActiveSheet.Cells(iCurrentRow, 2) = oRst(1)
        
        iCurrentRow = iCurrentRow + 1
        oRst.MoveNext
    Loop
End If
oCon.Close
'----------------------------------------------------------------------
 
 
'----------------------------------------------------------------------
' Save workbook and clean up
oExcelWorkbook.Save
oExcelWorkbook.Close
Set oExcelWorkbook = Nothing
oExcel.Quit
Set oExcel = Nothing
'----------------------------------------------------------------------
 
 
'----------------------------------------------------------------------
' Show a message depending on the result
 
If iReturnValue = 0 Then
    Call MsgBox("Complete", 64)
Else
    Call MsgBox("An error occured whilst calling the SP", 48)
End If
'----------------------------------------------------------------------
 
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:smyers051972
ID: 22885750
I do need it to be in VB actually because I am running all this from batch scripting... OR option B would be to manually change ODBC datasources like 9 times and manually pull the reports =)
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 22891145
You can run VBScript from batch scripting. This command line runs a VBScript:
CScript "D:\YourScript.VBS"
But it does beg the question: why are you using ODBC datasources? The existing script that I have posted does not use a DSN. It connects directly to the SQL Server.
 
If you must use DSN's then you can alter the VBS to accept a parameter (a DSN name) and use that.
0
 
LVL 1

Author Comment

by:smyers051972
ID: 22895698
Its mainly other applications that use ODBC, I did notice this does make a direct connection and that leads me to wonder if I could convert most of my scripting to VBS instead of dos batch.   I tried posting a question about it but they are like "Why do that?" Hard question to answer?
0
 
LVL 1

Author Closing Comment

by:smyers051972
ID: 31508588
Thank you very much. Good script!
0
 
LVL 1

Author Comment

by:smyers051972
ID: 22895761
I hope you dont mind, I closed it prematurely, looks like there is only 1 issue with it. After about 5 minutes I got an error stating it could not access ExcelOutput.XLS.

Everything else was perfect.
0
 
LVL 1

Author Comment

by:smyers051972
ID: 22895835
I figured out that issue, the file was open already HOWEVER now it times out :)
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 22900863
now it times out
Whats the exact error? Is it a database timeout or a COM timeout?
You may need to clean up your process space:
1. Close everything Excel related
2. Start task manager
3. Kill any processes called Excel
try again.
 
Its mainly other applications that use ODBC, I did notice this does make a direct connection and that leads me to wonder if I could convert most of my scripting to VBS instead of dos batch.   I tried posting a question about it but they are like "Why do that?" Hard question to answer?
Do you want to post a link and I'll take a look. There are many things that you can do in VBS that you can't do in batch scripting, but batch scripting is a bit simpler.
I don't see any reason to use an ODBC connection to SQL nowadays unless there is some legacy application issue.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

820 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