Excel Unbound Object Frame, Cell issue

[jb777]

This might not be a form related question, but that’s it origin.

First a few things about what and why:
- I am using Access 97, VBA and Excel 2000.
- I would have done all this in an easier way, with tables and queries. But the application I am creating does not have user write access to the harddrive. (So no writing to Access doing runtime)
- I need a dynamic table view in my interface so that’s why I have come to the point where I am now.
o Collum’s are fixed
o Rows do variate

First created a form, in this form I added an Unbound Object Frame and put a Excel worksheet inside that. Depending on the size (width/height) of the UOFrame I get x*y amount of cells.

The amount of x*y I can variate as long as I stay in design view and open the excel document and add data to it.

Once I close design view and save, reopen it in design view I loss the ability to add more cells. If I do the existing once smaller, the rest of the ‘space’ just gets white and the cells past the last x and y is not shown.

Which is my problem, how can I change the view of the UOFrame or “add” more cells in the Excel document? What property or what’s the VB code for this?

Cheers, John

 

[jb777]

Quick update on this

I'm struckling with a way to do this by creating a new worksheet and activate that. This works to some extend, it still only showes the x*y amount of cells in it. Code:

    Dim XL As Excel.Workbook
    Dim Sheet As Excel.Worksheet
    Set Sheet = OLEXLSheet.Worksheets.Add
    Sheet.StandardWidth = 3
    Sheet.Activate

But I found a curiously little thing (see attached picture) where I can drag the excel worksheet larger and thereby get the needed cells.

This is though not the wanted effect, I need the property to do this in code – Anyone know what that’s called? I have tried different things, without success.

The attached picture is from within the Access Application (VBA Form), the form is not locked, so thats the reason why I can drag the excel worksheet larger.

Suggestions?