Excel property needed

**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