Vbscript, sql & write/add record to existing excel file

[Ismailc]

Hi, Posted the question before - but could not get it going!

Can someone please Assist, as i need this to work it's part of my project!

VBscript, I'm executing a SQL command then with the result data append/add the row to an existing excel file.
Please anyone please Assist!

Scipt

Dim objXL 
Dim File_name
Option WScript

File_name="\\srv08-za063\source\test.xls" 
Set objXL = WScript.CreateObject("Excel.Application") 
Set wb = objXL.Workbooks.Add 
Set ws = objXL.Worksheets(1) 
r = 2 
objXL.Visible = False 


Set objConn = CreateObject( "ADODB.Connection")
Set resultSet = CreateObject ("ADODB.RecordSet")
objConn.Open fcCustom.Value(Process.Company, "Connections\FCDB")
strSQL = "select id,ProcessID from dbo.fcEventArchive where EventHistID = 2523"
resultSet.Open strSQL, objConn, 1, 2

On Error Resume Next 
resultSet.MoveFirst 

Do While Not resultSet.eof 
ws.Cells(r, 1).Value = (resultset(0)) 
ws.Cells(r, 2).Value = (resultset(1)) 
resultSet.MoveNext 
r = r + 1 
Loop 

resultSet.Close
objConn.Close
Set resultSet = Nothing
Set objConn = Nothing

[Shuja Ali]

This is VB.NET forum and I guess your question is related to VBScript so I am moving this thread to VB 6 forum.

[Ismailc]

Thank You

[Twodogs]

I'm not that familiar with vbscript, but without knowing exactly what your problem is, try this:

resultSet.Open strSQL, objConn, 1, 2

' Removed error resume, so you get a notification of the error (hopefully)
resultSet.MoveFirst 

Do While Not resultSet.eof 
ws.Cells(r, 1).Value = (resultset.fields(0).value) 
ws.Cells(r, 2).Value = (resultset.fields(1).value) 
resultSet.MoveNext 
r = r + 1 
Loop 

resultSet.Close
objConn.Close
Set resultSet = Nothing
Set objConn = Nothing

[dglienna]

Here's another:

Option Explicit

Public xlAppTemp As Excel.Application
Public xlWorkBook As Excel.Workbook
Public xlSheet As Excel.Worksheet
Dim strDate$

Public Sub GenerateReport()
  On Error GoTo ErrHandler
  
  ' Creating Object for Excel File.....
  Set xlAppTemp = New Excel.Application
  
  ' Making it Invisible and non-Interactive.....
  xlAppTemp.Visible = False
  xlAppTemp.DisplayAlerts = False
    ' Opening Template Excel File.....
  Set xlWorkBook = xlAppTemp.Workbooks.Open(App.Path & "\Book1.xls", , False)
  Set xlSheet = xlWorkBook.Sheets(1)

  ' Making Active to Worksheet 1.....
  xlSheet.Activate

  ' I am doing lot of things in it, but to provide you with example
  xlSheet.Cells(15, 1) = "This is my report 1"

  ' Formating Date to attach with new file name.....
  strDate = Format(Date, "yyyy-mm-dd")
  
  ' Saving excel file with new name on different folder.....
  xlWorkBook.SaveAs App.Path & "\Output" & strDate & ".xls"

Cleanup:
  ' Destroying Objects.....
  Set xlSheet = Nothing
  xlWorkBook.Close SaveChanges:=False
  Set xlWorkBook = Nothing
'The Visible and DisplayAlerts settings should be reset, as they can affect 'manual' use of Excel too.
  xlAppTemp.Visible = True
  xlAppTemp.DisplayAlerts = True
  xlAppTemp.Quit
  Set xlAppTemp = Nothing
  Exit Sub
ErrHandler:
'I presume this section comes after ErrHandler, in which case you will want to close the workbook without changes.
'(save happens just above if no error occurs)
  xlWorkBook.Close SaveChanges:=False
  Set xlWorkBook = Nothing

'The Visible and DisplayAlerts settings should be reset, as they can affect 'manual' use of Excel too.
  xlAppTemp.Visible = True
  xlAppTemp.DisplayAlerts = True

  xlAppTemp.Quit
  Set xlAppTemp = Nothing
End Sub

Private Sub Command1_Click()
  Call GenerateReport
  Beep
End Sub

[Ismailc]

Hi, Thank You for the Help & kindness.
I have a small problem to take care of before i can continue.
I'm coding in flowcentric which uses vbscript - i thought the script ran locally on the PC but found out Flowcentric runs from the server & i do not have Excel installed on the server. Going to install Excel today - will get back soon with an update.

Thank You!

[dglienna]

I don't understand. You didn't try the code that you posted, which was VBA code.

You posted in a VB6 forum, so I converted to VB6 (although that is the correct sequence to use with slightly different commands for VBA)

You don't even HAVE Office? I've never heard of Flowtronics.

You should have been a little more specific.

[Ismailc]

Sorry for that, Here in SA there is a Procees Management Tool called Flowcentric which uses VBScipt. Flowcentric is server based and the part which i use to retireve the SQL: i use to do my lookups as examples from Flowcentric:
I used a few vbscript examples in my SSIS ETL package which opens excel & executes macro then save & delete the excel file. That worked as it executed the script locally and tried to do it in Flowcentric but Flow executes on the server which had no Excel.

Problem:

Installed Excel & then got the error: Permission Denied to Create Object then followed the fix from the url: http://p2p.wrox.com/topic.asp?TOPIC_ID=24857

Added the ASPNET but now i get a complete different error:
A scripting error has occurred in FG10120Save: Microsoft Excel cannot access the file 'C:\Documents and Settings\Default User\My Documents'. There are several possible reasons: • The file name or path name does not exist. • The file you're trying to open is being used by another program. Close the document in the other program, and try again. • The name of the workbook you're trying to save is the same as the name of another document that is read-only. Try saving the workbook with a different name.. For further details please check the event log.



Script:

Dim objXL
Dim File_name
'Option WScript
File_name="\srv08-za063.xls"
Set objXL = CreateObject("Excel.Application")
Set wb = objXL.Workbooks.Add
Set ws = objXL.Worksheets(1)
r = 2

objXL.Visible = False
Set objConn = CreateObject( "ADODB.Connection")
Set resultSet = CreateObject ("ADODB.RecordSet")
objConn.Open fcCustom.Value(Process.Company, "Connections")
strSQL = "select id,ProcessID from dbo.fcEventArchive where EventHistID = 2523"

resultSet.Open strSQL, objConn, 1, 2
resultSet.MoveFirst
Do While Not resultSet.eof
ws.Cells(r, 1).Value = (resultset.fields(0).value)
ws.Cells(r, 2).Value = (resultset.fields(1).value)
resultSet.MoveNext
r = r + 1
Loop
wb.Save
ws.Save
resultSet.Close
objConn.Close
Set resultSet = Nothing
Set objConn = Nothing

[Ismailc]

I found out something!
when i comment these two lines out then nothing at all happens no error but unsuccessful but when include them i get the following errors:

wb.Save
ws.Save

A scripting error has occurred in FG10120Save: Microsoft Excel cannot access the file 'C:\Documents and Settings\Default User\My Documents'. There are several possible reasons: • The file name or path name does not exist. • The file you're trying to open is being used by another program. Close the document in the other program, and try again. • The name of the workbook you're trying to save is the same as the name of another document that is read-only. Try saving the workbook with a different name.. For further details please check the event log.

Please Assist what can i do?

[Ismailc]

WS.SaveAs(File_Name) does not bomb out but writes nothing

[dglienna]

Must be a permission problem with Flowcentric. Talk to the admin. Try moving the wb into a shared folder to read and write from

[Ismailc]

Hi, Thank You All for all the help & Assistance.

My manager decided to cancel the request as it was outside the boundaries!

Thank You

[Twodogs]

Give it a chance to actually save, save the worksheet first, then the workbook:

ws.Save
sleep 1000
wb.Save

[dglienna]

now we'll never know

[Ismailc]

Hi, The user still would like it - so i'm doing it in the spare time!

I added the code but now i get:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified. For further details please check the event log.