Excel property needed

**jb777** · 08-25-2008, 04:43 PM

Hi all,

I hope someone here is able to help me.

First a little background on the problem. I am doing an applikation in Access forms, with VBA and in one of my forms I embedded an Excel document. Now this Excel (2000) document is causing me some problems, since I cant get it to show me a dynamic amount of cells in the Access form.
But, I almost think I solved it. This means if I can find this last property to manipulate in VBA. All I know about it is, that its called "Name Box" if you hover over it in Excel.

It gets activated when you select a bunch of cells and says something like 4R x 3C (4 rows times 3 collums).
What I would like to is programmicly set this property to give me YR x 20C (Y is dynamic) and selected from A1 cell.

See the attached picture, to see where to find the "Name Box" - I hope someone is able to help me or know of something that can 'select' the rows and collums.

Thank you, John

**MartinShort** · 08-25-2008, 06:52 PM

How about something like this?

Sub defineRange()
    Dim rng As Range
    Dim Y As Integer
    Y = 12 'Or whatever you want it to be.
    Set rng = Range("A1").Resize(Y, 3)

    rng.Select 'You should delete this line - I put it in so 
               'you could see what's happening to your range.
End Sub

HTH

**jb777** · 08-25-2008, 07:37 PM

Hi MartinShort,

I tryed something like that earlyer, but got an error so thought I did something wrong. I just tryed your code and it gives me the following error

Run-time error '1004':
Method 'Range' of object'_Global' failed

at

Set rng = Range("A1").Resize(Y, 3)

I tryed with diffrent Y's cause it might be outofbound.

What I tryed earlyer was something like worksheet.range("a1:f4").resize or so

Thank you, John

**MartinShort** · 08-26-2008, 03:23 AM

Hi John

A bit confused. I tried the code out (just to check if I'd done something stupid). It does work on my PC.

Can you post an example worksheet please and I'll see if I can help.

Martin

**jb777** · 08-26-2008, 04:19 PM

Hi Martin,

I just open'd a new Excel sheet and tryed out your code - Yes it works!!

But sadly its not working in the application im using it in. I am doing an application in Access forms, where I put an Excel document in a Unbound OLE object. And if I put the code there, and let Access control the properties of Excel it created the above error - I control everything else from Access and thereby prevent the 'macro warning' to pop up if I put code in Excel documents.

See the attached picture for more explaination - I am trying to resize the size of the Excel view, I can do it manualy with the mouse but havent found code for it. I thought it would be linked to the "Name Box" cause thats where it shows the RxC size.

Cheers for the helping, John

**MartinShort** · 08-27-2008, 01:43 PM

Hi John

Sorry to take so long to get back - the day job got in the way.

I set up a module in Access and tried this - it does work honest!

Sub controlExcel()
      Dim xlapp As Object
      Dim xlwb As Object
      Dim xlws As Object
      Dim rng As Range
      Dim Y As Integer
      
      ' Create an instance of Excel and add a workbook
      Set xlapp = CreateObject("Excel.Application")
      Set xlwb = xlapp.Workbooks.Add
      Set xlws = xlwb.Worksheets("Sheet1")

'Give the user control of Excel.
      xlapp.UserControl = True

'Select Range According to John's requirements - i.e. the bit we did last time!
      Y = 12 'Or whatever you want it to be.
      Set rng = xlws.Range("A1").Resize(Y, 3)
      rng.Select 'You should delete this line - I put it in so
               'you could see what's happening to your range.

'Make the application visible to the user.      
      xlapp.Visible = True

' For needness I set the 3 Excel objects to nothing at the end of the routine.
' I'm told that this avoids memory leaks.

End Sub

I don't know how you set up your control, but I use the xlapp, xlwb and xlws sections as standard. You can then control Excel by prefixing it with the relevant object and treat it as if your code is native to the Excel application.

Hopefully this helps. It's always possible that someone much brighter than me knows better ways of doing this, but that's how I do it anyway!

Martin

**jb777** · 08-27-2008, 04:46 PM

Hi Martin,

Np at all - im just glad for the help, and finding a solution to this.

I tryed your code, and yes again it works

- I diddent doubt it.

The diffrence now between our two "applications" is that I want this to happen in a form, inside a '(U)Bound Object Frame' - so that the excel sheet is actualy showing inside that Object Frame. Keeping the interface of my form the same, without having an excel window pop up.

So, create a form, inside that form create an Unbound Object Frame and select Excel Worksheet. It is here the problem is, everything else you've show works like a charm.

Not sure how else to explain it, sorry.

Cheers, John

**jb777** · 08-27-2008, 04:51 PM

Ohh btw,

I would like to find the solution to this problem, but ive made a work around:

Workaround: Create a default sized Excel dokument 10R x 20C - what I now do is insted of resizing the internal Excel dokument, I desided to create a 'tab' order. So if more items then 10R reside in my Excel dokument, I tab over to a "new" page, which is actualy just the same 10R x 20C, just with rewritten data in it from my vector.

- insted of tabs you could also write a scrollbar that follows a sertain index.

But just to know how excel works inside a form object frame would be nice, since it lessens the amount of code to be written.

John