Skip to Content
0
Former Member
Jun 12, 2013 at 04:06 PM

Export Infoview Data using VB to Excel?

61 Views

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