More Outlook Resource Sites

Microsoft Developer Network (MSDN)

FAQs and other general resources

forum 18-Dec-2018 14:16

Looking for help with Outlook programming projects — VSTO, add-ins, VBA, custom Outlook forms, etc.? You′ve come to the right place!

NEW! >> Subscribe to this site via RSS. For more RSS options, see the complete list of feeds on our main news page.

    Page [ 1 2 3 4 Next >> ]  
 Outlook Form Design Printer Friendly Version
Designing custom Outlook forms and form regions and writing code for them
Topic
Loading a combobox...is there a faster way?
Hi,

I have a custom form (in outlook 2003) with a combobox that loads the data from columns a-z and rows 1 - 114 in an excel spreadsheet. I am currently using the additem to load the data. If anyone knows a faster method I would greatly appreciate it.
I need the data to be consistent with the columns because they are mapped to custom fields in the form.
Thanks.
 

  03-Jul-2008  08:10
  03-Jul-2008  08:28   
Building an array and then using it to set the combo box's List property will probably be faster. See http://www.visualbasic.happycodings.com/API_and_Miscellaneous/code32.html for an example of loading a range of cell values into an array.
  03-Jul-2008  09:32   
how can I use this in vbscript since I can not write VBA code in outlook....

Function RangeToArray(rngInput As Object, avValues As Variant) As Boolean
 
  03-Jul-2008  09:37   
Remove the As clauses from all declarations.
  03-Jul-2008  10:17   
Im doing something wrong because it's not loading...

Here's how I'm calling the information:

Function RangeToArray(rngInput, avValues) Boolean
    On Error GoTo ErrFailed
    avValues = Empty
    avValues = rngInput.Value
    RangeToArray = True
    
    Exit Function

ErrFailed:
    'Failed
    Debug.Print "Error in RangeToArray: " & Err.Description
    Debug.Assert False
    RangeToArray = False
    On Error GoTo 0
End Function

Getting the data from excel:

On Error Resume Next
Set objXL=getobject(,"excel.application")
Err.clear
if objXL is nothing then
Set objXL=createobject("excel.application")
end if

set Mybook=objXL.workbooks.open(strFilNam)

Call RangeToArray(Worksheets(1).Range("B1:AD117"), avValues)

Nothing is working...any guidance will be great.
 
  03-Jul-2008  10:39   
This procedure declaration is not valid:

    Function RangeToArray(rngInput, avValues) Boolean

It needs to be:

    Function RangeToArray(rngInput, avValues)

Also, you cannot use construction like On Error GoTo ErrFailed in VBScript. Instead, you can use On Error Resume Next to allow code execution to advance to the next statement in the event of an error. If you feel a need to, you can add an If Err <> 0 block to check for errors.
  03-Jul-2008  10:53   
There were a couple of other subtleties that I missed the first time around. Here's the test version that worked for me:

Function Item_Open()
    Call TestR2A
End Function

Sub TestR2A()
    Dim ex ' As Excel.Application
    Dim ws ' As Excel.Worksheet
    Dim rg ' As Excel.Range
    Dim arr
    
    Set ex = GetObject(, "Excel.Application")
    Set ws = ex.Workbooks(1).Worksheets("Sheet1")
    Set rg = ws.Range("A3", "C9")
    Call RangeToArray(rg, arr)
    Set ListBox1 = Item.GetInspector.ModifiedFormPages("P.2").Controls("ListBox1")
    ListBox1.List = arr
End Sub

Function RangeToArray(ByVal rngInput, ByRef avValues)
    On Error Resume Next
    avValues = rngInput.Value
    If Err = 0 Then
        RangeToArray = avValues
    End If
End Function
 
  03-Jul-2008  11:34   
how would you open a saved spreadsheet? (for example: c/mydocs/data.xls) Shouldn't the range be ("A3:C9") - i realize you're the expert just wanted to understand better.
  03-Jul-2008  11:46   
I used your code structure and implemented a line to open my file....however the odd thing is I am only getting the first column populated in my sheet

Sub TestR2A()
Dim ex 'As Excel.Application
Dim ws 'As Excel.Worksheet
Dim rg 'As Excel.Range
Dim arr

Set ex = GetObject(, "Excel.Application")
set Mybook=ex.workbooks.open(strFilePath)
Set rg = ex.worksheets(1).Range("B3:AD117")
Call RangeToArray(rg, arr)
Set ListBox1 = Item.GetInspector.ModifiedFormPages("P.2").Controls("ListBox1")
ListBox1.List = arr
End Sub

Function RangeToArray(ByVal rngInput, ByRef avValues)
On Error Resume Next
avValues = rngInput.Value
If Err = 0 Then
RangeToArray = avValues
End If
End Function
 
  03-Jul-2008  11:47   
I meant the first column of my spreadsheet loaded into Listbox1.
 
  03-Jul-2008  17:08   
Did you set the list box's ColumnCount and ColumnWidths properties to match the data?
    Page [ 1 2 3 4 Next >> ]