Solved

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

Posted on 2008-10-21
27
475 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

705 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

17 Experts available now in Live!

Get 1:1 Help Now