6/19/2025 3:29:44 PM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss writing script in Architect plugins for SalesLogix & general SalesLogix customization topics (for Windows client only). View the code of conduct for posting guidelines.
|
|
|
|
Office Web Compnonents
Posted: 31 Jul 07 3:46 PM
|
I am trying to create an Excel Spreadsheet with some data from my SalesLogix database and then save it as a CSV file on my Sync Server. I plan on running this on the sync server as a sync agent. The script runs fine on my machine Excel 2002 and it fails on the server Excel 2003, but I'm thinking it could also be a permissions issue.
The error that I'm getting is:
"OLE Automation server cannot create object"
The error points to line 16 which is where I try to CreateObject("OWC.Spreadsheet")
Option Explicit
sub main
Dim strSQL as String
Dim hndInfo as Long
Dim objExcel as Object
Dim objExcel2 as object
Dim intRow as Integer
dim objWorkbook as object
dim objWorksheet as object
dim objcreatefile as object
Const xlCSV = 6
Set objExcel = CreateObject("OWC.Spreadsheet")
If LogixErrors Then
'msgBox LogixErrorText
DBCloseAll
Set objExcel = nothing
Exit Sub
End If
objExcel.Range("A1").value = "createdate"
objExcel.Range("B1").value = "Server_Name"
objExcel.Range("C1").value = "Application_Name"
objExcel.Range("D1").value = "User_ID"
objExcel.Range("E1").value = "EventID"
objExcel.Range("F1").value = "Type_of_Request"
strSQL = "SELECT REPLACE(convert(char(20),createdate,120),'-','') as createdate, " & _
"'STLAP458040' as Server_Name, 'SalesLogix_FA_Prod' as Application_Name, " & _
"substring(Usercode,1,15) as User_ID, EventID, substring(description,1,50) as Type_of_Request " & _
"FROM sysdba.C_LOGONAUDIT where datediff( day,createdate, getdate())< 1 order by createdate desc"
LogixClearError
hndInfo = DBOpenSQL(strSQL, true)
If LogixErrors Then
'msgBox LogixErrorText
DBCloseAll
Set objExcel = nothing
Exit Sub
End If
DBMoveTo hndInfo, "First"
intRow = 2
WHILE DBEoF(hndInfo) = False
objExcel.Range("A" & str(intRow)).value = DBGetValue(hndInfo, "createdate")
objExcel.Range("B" & str(intRow)).value = DBGetValue(hndInfo, "Server_Name")
objExcel.Range("C" & str(intRow)).value = DBGetValue(hndInfo, "Application_Name")
objExcel.Range("D" & str(intRow)).value = DBGetValue(hndInfo, "User_ID")
objExcel.Range("E" & str(intRow)).value = DBGetValue(hndInfo, "EventID")
objExcel.Range("F" & str(intRow)).value = DBGetValue(hndInfo, "Type_of_Request")
DBMoveTo hndInfo, "Next"
intRow = intRow + 1
WEND
DBCloseAll
objExcel.ActiveSheet.Export("C:\IBMTEMP\CUSTOMLOG\SalesLogix\SalesLogixFA.xls", 0)
If LogixErrors Then
'msgBox LogixErrorText
DBCloseAll
Set objExcel = nothing
Exit Sub
End If
Set objExcel = Nothing
Set objExcel2 = CreateObject("Excel.Application")
Set objWorkbook = objExcel2.Workbooks.Open("C:\IBMTEMP\CUSTOMLOG\SalesLogix\SalesLogixFA.xls")
Set objWorksheet = objWorkbook.Worksheets("Sheet1")
objExcel2.DisplayAlerts = FALSE
objWorksheet.SaveAs "C:\IBMTEMP\CUSTOMLOG\SalesLogix\SalesLogixFA.csv", xlCSV
objExcel2.Quit
end sub
Any ideas?
Thank you! |
|
|
|
Re: Office Web Compnonents
Posted: 31 Jul 07 11:56 PM
|
First of all, if all you need is to create a CSV file, don't go to all the trouble to use the OWC, espcially with all the extra overhead. BTW, your problem is likely that the OWC are not installed on the machine.
To create a CSV file, all you need to do is write out to a text file. Nothing fancy. This will be much better since you're removing all the overhead - also to keep in mind, Cypress basic completly sucks with memory management and does not properly dispose of objects, so over time this script will get very taxing on the server.
To create the CSV file, you'll do something like this (keep in mind, I've not written legacy basic code in years so this code may need some tweaking to work!)
Sub Main
Dim file as String
Dim hnd As Long
file = "C:\MyCSVFile.csv"
Open file For Output As #1
hnd = DBOpenSQL("select * from account", True)
While DBEof(hnd) = False
Write #1, Chr(34) & DBGetValue(hnd, "accountid") & Chr(34) & "," & Chr(34) & DBGetValue(hnd, "account") & Chr(34)
DBMoveTo hnd, "Next"
Wend
DBClose hnd
Close #1
End Sub
Anyway, writing that legacy code just made me shutter  . I've long since pushed those nightmares from my mind. But, something like that...
-Ryan |
|
|
|
Re: Office Web Compnonents
Posted: 01 Aug 07 12:25 PM
|
Thank you Ryan, that worked great!
We have everything in Active Script, except for these Sync agents. We still need to find a good way to run those in the Active Script.
|
|
|
|
Re: Office Web Compnonents
Posted: 01 Aug 07 12:32 PM
|
Hi Ian,
Even easier in ActiveScript. You'll use the FileSystemObject to write to the file. Something like this:
Dim fso
Dim file
Dim rs
Set fso = CreateObject("Scripting.FileSystemObject")
Set file = fso.CreateTextFile("C:\MyCSVFile.csv", True)
Set rs = Application.GetNewConnection.Execute("select * from account")
While Not rs.EOF
file.WriteLine Chr(34) & rs.Fields("accountid").Value & Chr(34) & "," & Chr(34) & .rs.Fields("account").Value & Chr(34)
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
file.Close
Set file = Nothing
Set fso = Nothing |
|
|
|
Re: Office Web Compnonents
Posted: 01 Aug 07 2:19 PM
|
Ryan,
The problem with that active script is I can't run it as a sync agent because the application object isn't open. Do you know any good ways to open the application automatically when the synce server cycles and then close it when it's done? |
|
|
|
Re: Office Web Compnonents
Posted: 01 Aug 07 2:58 PM
|
| Nothing really wrong with keeping it legacy. You could always just write it in VBScript, save it as a VBS file, then kick that off from the agent script. For connections and stuff just use a UDL file or put your connection string in the script. |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|