Here is how I pull information from Enterprise 9 8

[BlurredVision]

To follow is how I pull information from CE 9. I can report on what reports I have, who gets them and when. Sorry for the less then great instructions, but I don't have a lot of time available these days.

Here we go.


Run the following query via the CSP Query Builder

SELECT
SI_NAME,
SI_PROCESSINFO.SI_FORMAT_INFO,
SI_ID,
SI_OWNER,
SI_PARENT_FOLDER,
SI_CREATION_TIME,
SI_DATE,
SI_DESCRIPTION,
SI_UPDATE_TS,
SI_DEST_SCHEDULEOPTIONS,
SI_LAST_RUN_TIME,
SI_NEXTRUNTIME,
SI_PATH,
SI_FILES,
SI_FORMAT_INFO,
SI_SCHEDULE_TYPE,
SI_SCHEDULE_INTERVAL_MINUTES,
SI_SCHEDULE_INTERVAL_HOURS,
SI_SCHEDULE_INTERVAL_MONTHS,
SI_TYPE,
SI_ENDTIME,
SI_PROGID_SCHEDULE,
SI_RETRIES_ALLOWED,
SI_RETRY_INTERVAL,
SI_RUN_ON_TEMPLATE,
SI_STARTTIME,
SI_DESTINATION,
SI_SCHEDULEINFO
FROM CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report'


From your web browser, save the results of the query to an html document.

Open up your html document in Excel.

From Excel, do an alt-F9 to create a new macro.
From the macro menu, type in a new macro name, then click ‘create’ (The macro editor will now open.

Select all text within the editor and copy and paste the following information within the editor:

' Start Copy

Sub Remove_Insert()
'
' Remove_Insert Macro
' Macro recorded 11/25/2003 by Brian Kuipers
'

'
Rows("1:13&quot.Select
Selection.Delete Shift:=xlUp
ActiveSheet.Shapes("Picture 5&quot.Select
Selection.Delete
Sheets.Add
Sheets("Sheet1&quot.Select
Sheets("Sheet1&quot.Name = "Sheet2"
Sheets("export&quot.Select
Sheets("export&quot.Move Before:=Sheets(1)
End Sub
Sub copyEmail()
Dim nFound As Boolean
counter = 1: nFound = False
For a = 1 To Range("E65535&quot.End(xlUp).Row
Cells(a, 1).Activate

If ActiveCell.Value = "SI_ID" Then
nFound = True: Cells(a, 2).Copy Sheets(2).Cells(counter, 1)
End If

If ActiveCell.Value = "SI_OWNER" Then
nFound = True: Cells(a, 2).Copy Sheets(2).Cells(counter, 2)
End If

If ActiveCell.Value = "SI_PARENT_FOLDER" Then
nFound = True: Cells(a, 2).Copy Sheets(2).Cells(counter, 3)
End If

If ActiveCell.Value = "SI_UPDATE_TS" Then
nFound = True: Cells(a, 2).Copy Sheets(2).Cells(counter, 4)
End If

If ActiveCell.Value = "SI_CREATION_TIME" Then
nFound = True: Cells(a, 2).Copy Sheets(2).Cells(counter, 5)
End If

If ActiveCell.Value = "SI_LAST_RUN_TIME" Then
nFound = True: Cells(a, 2).Copy Sheets(2).Cells(counter, 6)
End If

If ActiveCell.Value = "SI_DESCRIPTION" Then
nFound = True: Cells(a, 2).Copy Sheets(2).Cells(counter, 7)
End If

If InStr(1, Cells(a, 3).Value, &quot;frs://&quot <> 0 And nFound = True Then
Cells(a, 3).Copy Sheets(2).Cells(counter, 8)
End If

If InStr(1, Cells(a, 3).Value, &quot;.rpt&quot <> 0 And nFound = True Then
Cells(a, 3).Copy Sheets(2).Cells(counter, 9)
End If

If InStr(1, Cells(a, 3).Value, &quot;crx&quot <> 0 And nFound = True Then
Cells(a, 3).Copy Sheets(2).Cells(counter, 10)
End If

If ActiveCell.Value = &quot;SI_SCHEDULE_TYPE&quot; Then
nFound = True: Cells(a, 2).Copy Sheets(2).Cells(counter, 11)
End If

If ActiveCell.Value = &quot;SI_NAME&quot; Then
nFound = True: Cells(a, 2).Copy Sheets(2).Cells(counter, 12)
End If

If ActiveCell.Value = &quot;SI_TYPE&quot; Then
nFound = True: Cells(a, 2).Copy Sheets(2).Cells(counter, 13)
End If

If ActiveCell.Value = &quot;SI_ENDTIME&quot; Then
nFound = True: Cells(a, 2).Copy Sheets(2).Cells(counter, 14)
End If

If ActiveCell.Value = &quot;SI_PROGID_SCHEDULE&quot; Then
nFound = True: Cells(a, 2).Copy Sheets(2).Cells(counter, 15)
End If

If ActiveCell.Value = &quot;SI_RETRIES_ALLOWED&quot; Then
nFound = True: Cells(a, 2).Copy Sheets(2).Cells(counter, 16)
End If

If ActiveCell.Value = &quot;SI_RETRY_INTERVAL&quot; Then
nFound = True: Cells(a, 2).Copy Sheets(2).Cells(counter, 17)
End If

If ActiveCell.Value = &quot;SI_STARTTIME&quot; Then
nFound = True: Cells(a, 2).Copy Sheets(2).Cells(counter, 18)
End If

If InStr(1, Cells(a, 5).Value, &quot;@&quot <> 0 And nFound = True Then
Cells(a, 5).Copy Sheets(2).Cells(counter, 19): counter = counter + 1
End If

If ActiveCell.Value = &quot;Properties&quot; Then nFound = False: counter = counter + 1
Next a
End Sub

Sub InsertHeader()

With ThisWorkbook.Worksheets(&quot;Sheet2&quot
.Rows(1).Insert
.Range(&quot;A1&quot.Value = &quot;SI_ID&quot;
.Range(&quot;B1&quot.Value = &quot;SI_OWNER&quot;
.Range(&quot;C1&quot.Value = &quot;SI_PARENT_FOLDER&quot;
.Range(&quot;D1&quot.Value = &quot;SI_UPDATE_TS&quot;
.Range(&quot;E1&quot.Value = &quot;SI_CREATION_TIME&quot;
.Range(&quot;F1&quot.Value = &quot;SI_LAST_RUN_TIME&quot;
.Range(&quot;G1&quot.Value = &quot;SI_DESCRIPTION&quot;
.Range(&quot;H1&quot.Value = &quot;SI_FILE_PATH&quot;
.Range(&quot;I1&quot.Value = &quot;SI_FILE_NAME&quot;
.Range(&quot;J1&quot.Value = &quot;SI_EXPORT_FORMAT&quot;
.Range(&quot;K1&quot.Value = &quot;SI_SCHEDULE_TYPE&quot;
.Range(&quot;L1&quot.Value = &quot;SI_NAME&quot;
.Range(&quot;M1&quot.Value = &quot;SI_TYPE&quot;
.Range(&quot;N1&quot.Value = &quot;SI_ENDTIME&quot;
.Range(&quot;O1&quot.Value = &quot;SI_PROGID_SCHEDULE&quot;
.Range(&quot;P1&quot.Value = &quot;SI_RETRIES_ALLOWED&quot;
.Range(&quot;Q1&quot.Value = &quot;SI_RETRY_INTERVAL&quot;
.Range(&quot;R1&quot.Value = &quot;SI_STARTTIME&quot;
.Range(&quot;S1&quot.Value = &quot;SI_REPORT_RECIPIENTS&quot;


'add header


End With



End Sub

Sub FillSI_ID()
For Each c In Range(&quot;A1:A748&quot
If c = &quot;&quot; Then c.Value = c.Offset(-1, 0).Value
Next
End Sub

Sub clearBorders()
Dim ws As Worksheet

Set ws = ActiveSheet

' Clear exiting borders
Cells.Borders.LineStyle = xlLineStyleNone

End Sub

Sub DeleteEmptyRows()
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
Next r
End Sub

' End Copy


Save and exit.

The following macro’s have been created:
clearBorders
copyEmail
DeleteEmptyRows
FillSI_ID
InsertHeader
Remove_insert

Press Alt-F9 to bring up the macro list. Highlight a macro in the following order and click run

Remove_Insert
clearBorders
copyEmail
DeleteEmptyRows (Run From Sheet 2)
FillSI_ID (You must edit this macro with the total amount of rows in your spread sheet) (Run From Sheet 2)
InsertHeader (Run From Sheet 2)

Now save your spread sheet in excel format. Now you can use Crystal Reports to report off of your newly created spread sheet.

If you have any questions.. Please post them. I'll help out if I can.

Brian

 

[gashandler]

Thanks for sharing this, I'll try it out.

 

[McChops]

Cheers Blurred Vission!

Saved me a lot of time. Not sure if it's possible to extract the actual folder name in the original query, but otherwise it's all there for me.

Thanks again.

 

[SuzanneColson]

Excel is not my forte. Especially when working with macros, vba, etc. I am using Excel and running into macro errors. Anyone have the same problem. I have looked vba code and the errors I am getting and doesn't seem like it should be a problem with the latest version of Excel. Will try on an older version, but wonder if anyone running into the same problem on Excel 2003?

Suzanne

 

[McChops]

I just successfully used this in Excel 2000. The first time I ran it I encountered a problem in the "Remove Insert" macro.

On closser inspection I found it relies upon the sheet to be called "Export". Renamed it this and did not encounter any more problems.

Regards,

MC

 

[Bisquite]

This sounds useful, does anybody know if it can be used with CE8.5

 

[synapsevampire]

It works with 8.5.

-k

 

[BlurredVision]

Hello all... Sorry, I've been away... I've been on the always fun job hunt! Very happy to see that others are finding this useful. I am getting ready to install CE10. Hopefully the information is a bit more accessible, if not I'll post an update to this process if needed.

Brian

 

[fredp1]

I've had some fun and games with Excel 97 when running the macro Remove_Insert. Excel crashed when it tried to delete rows.
Do a Select All, Copy and Paste Special, values only into a new sheet, then run the macros and it worked okay.
I also modified the macro CopyEmail as it wasn't getting all the rows. It stopped at the last row with a email recipient. Not the last report.
The changes are, use Column B not Column E.
Sub copyEmail()
Dim nFound As Boolean
counter = 1: nFound = False
'For a = 1 To Range("E65535").End(xlUp).Row
For a = 1 To Range("B65535").End(xlUp).Row
the remaining part of the macro is fine.

Also to avoid editing FillSI_ID macro for the last cell try this..

Sub FillSI_ID()
'For Each c In Range("A1:A748")
For Each c In Range("A1", Range("A65535").End(xlUp).Address)
If c = "" Then c.Value = c.Offset(-1, 0).Value
Next
End Sub
It also worked okay for CE8.5
Fred

 

[johnstrang]

Using Excel 2000, with CE 8.5, I have problems with the FilSI_ID macro - error is
"Run-time error '1004'"
Application-defined or object-defined error.

The debugger shows error in the line
If c = "" Then c.Value = c.Offset(-1, 0).Value

The only thing I can think is that when I ran the original query in the CSP it timed out after 539 of 1000 records, so in the spreadsheet there are rows like 1/1000, 2/1000 up to 539/1000.

All the other macros ran without any problems.

Any help gratefully received!

John

 

[BlurredVision]

make sure the range is correct. If you only havd 539 rows, the range in the macro should read For ("A1:A539")


Hope this helps

 

[johnstrang]

Thanks for the answer, but I found my problem with the FilSI_ID macro.
In fact the problem was in the Remove_Insert macro where it begins :-
Sub Remove_Insert()
'
' Remove_Insert Macro
' Macro recorded 11/25/2003 by Brian Kuipers
'

'
Rows("1:13").Select

My problem was that there were only 10 lines of header to be removed - modifiying the line to read
'
Rows("1:10").Select

solved it.

And thanks to fredp1 for his advice on the other macros.

John

PS still getting errors about Script.ServerTimeout :-(

 

[synapsevampire]

I replaced the bad copyemail code line with:

ActiveCell.SpecialCells(xlLastCell).Select
For A = 1 To ActiveCell.Row 'Range("E65535").End(xlUp).Row

-k

 

[khartman]

Hello everyone,

Has anyone used this against CE 8.5/CR 8.5? I am experiencing a problem when I have more than two tables joined together and am trying to pull data from all of the tables. I get the following error:

"ODBC Error:[MERANT][ODBC XML driver][XML][Closed Integrator Base]Syntax error at token '(', line 1 offset 123"

I saw an article on the BO support site that suggested inseting a registry entry relating to the crxml18.dll; however, this driver is only found on the CR 9 install not on CR 8.5. Is there a solution out there? If you are using 8.5 and have run across this let me know what you did to fix it.

--Ken

Kenneth Hartman
Manager, EIS
Hughes Network Systems

http://www.hns.com

 

[weikfan]

Hi BlurredVision,

The query works great!

We just installed CE 10. Could you please let me know if CE10 makes the information provided by your query more accessable?

Thanks.
Fan