I have some VBA code attached to a

[illini]

I have some VBA code attached to a form which opens an Excel file, and extracts specific data within a spreadsheet. This data is then placed within a listbox. When executed, the code works great!

The problem I'm faced with is that I want to set this up within a report. However, when I try to place this code within the report's 'On Activate' event, I receive a Run-time error '2191' stating that I can't set the Row Source property after printing has started.

Here's the code in question. How can I assign the Row Source of a listbox (via VBA) prior to printing?


Dim ExApp As Excel.Application

With ExApp
.Workbooks.Open FileName:=strInputFileName
.Sheets("Info&quot.Select
End With

zz = "Num;Rate;St;F4;F5;F6;F7;F8;F9;Notes"
For Each X In ExApp.Range("A21", ExApp.Range("A20&quot.End(xlDown).Offset(1, 0))
X.Select
For Each y In ExApp.Range(ExApp.ActiveCell, ExApp.ActiveCell.Offset(0, 9))
If y <> ExApp.ActiveCell Then z = z & &quot;;&quot; & y Else z = y
Next y
If zz <> Empty Then zz = zz & &quot;;&quot; & z Else zz = z
z = Empty
Next X

LB_Utilization.RowSource = zz
zz = Empty -illini

 

[vbajock]

Usually you would code a line in your For loop that appends a record to a table, then use the table as the record source for your report objects.

 

[illini]

vbajock,

Thanks for the input. I was hoping to avoid having to create or append a table for this.

In this case, I'm guessing that it would be better to create a table. I only need the information briefly. If I appended a table, then I would have to delete the new data afterwards.

What's the best way of going about this? -illini

 

[vbajock]

It depends on what version of access you are using, and if you prefer DAO or ADO. Using DAO, first, create a table that has the correct structure for your list box. Then


Add this to declarations:

Dim mytable as new DAO.recordset
dim mydb as DAO.database

Add this after the declarations

set mydb=Currentdb()
set mytable=openrecordset(&quot;TheNameOfTheActualTable&quot

Then something like this goes in your looping structure to add a record, and then assign a value to the new record's field each time you make a pass through your loop

mytable.addnew
mytable!myfield_1=x
mytable!myfield_2=y
mytable!myfield_1=z
mytable.update