Export Infoview Data using VB to Excel?

[Former Member]

I am attempting to access data (SI_Name, SI_ID values) in Infostore and export the data to an Excel file. Here is code I found on this site, I got the headings but can't get the data to pull from Infoview. Just started with this tool, my goal is to automate the data captured into a readable format(used Query Builder but ugly output). I'm tired of manually exporting data. What am I missing? :

strXlsFileName = "c:\BusinessObjects Users.xls"

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add()

Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

Dim i, strTotal, intTotal, strAliasNum, arrString

' This query will return all the users within the system marked as Named

' This query statement to be edited in order to pull the data from the system that your after

Set objMyUserInfo = iStore.Query("SELECT TOP 10 SI_ID, SI_NAME FROM CI_INFOOBJECTS")

' We will start writing user data from row 2 in MS Excel file

intRow = 2

' Write headings to the MS Excel file

objSheet.Cells(1, 1).Value = "SI_ID"

objSheet.Cells(1, 2).Value = "SI_NAME"

' Format the headings

For intColumn = 1 To 2

          objExcel.Cells(1, intColumn).Font.Bold = True

          objExcel.Cells(1, intColumn).Interior.ColorIndex = 40

Next

' For every object belonging to this uder perform the following

For Each UserProperties In objMyUserInfo

          strUserName = UserProperties.Properties.Item("SI_NAME").value
          strUserID = UserProperties.Properties.Item("SI_ID").value

'****Not sure what's going on here? Where/How is the data being sent into Excel?         
          For i = 1 To intTotal

                    'strAliasNum = CStr(i)

                    strMyname = UserProperties.Properties.Item("SI_Name").Properties.Item("SI_ID").value

                    objSheet.Cells(intRow, 1).Value = strUserName

                    Set objRange = objExcel.ActiveCell.EntireColumn

                    objRange.AutoFit()
                           

          Next
     

          ' Go to next row

          intRow = intRow + 1

Next

' Update the changes to DB

iStore.Commit objMyUserInfo

' To fit the columns

For intColumn = 1 to 5

          objSheet.columns(intColumn).AutoFit()

Next

' Save the Excel file

objWorkbook.SaveAs(strXlsFileName)

' Uncomment the below line to close the MS Excel File automatically

' objExcel.Quit

[DellSC · Accepted solution]

An easier way to do this might be to write a CSV file that can be opened in Excel instead of trying to write to Excel itself.  You would just open a text file and write to that, placing a comma between the SI_ID and SI_NAME values.  I don't know VB, but in C# I would use something like writeln to automatically put a carriage return/line feed at the end.

-Dell

[Former Member · Accepted solution]

I am really looking for a solution in VB.  This will help me with the admin tasks I do now manually in Query Builder.

[Former Member · Accepted solution]

This message was moderated.

[DellSC · Accepted solution]

So write it to a .csv file using VB.  I can't help you with the exact syntax as that's more of a VB-specific question.  I would use the same logic you're using now, just use a different method of getting the info into a file.

-Dell

[Adam_Stone]

Moved this thread to an SDK related space as you had posted this under Web Intelligence.

What have you done to debug the following loop:

For Each UserProperties In objMyUserInfo

If it is actually iterating through the objects, then your issue is purely how do I use the Excel API to write a value to a cell, in which case, it has nothing to do with BusinessObjects calls at all.