Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

VB Excel OLE

Posted on 2009-05-10
10
1,879 Views
Last Modified: 2013-11-25
I am creating a project in VB 6 where I want to embed an Excel workbook.  I have it working correctly so far with the code below.  

The writeTable array is an array created in another procedure that I want to populate into the spreadsheet.  This array has a couple thousand rows of data.  However when the array is copied to the embedded OLE object only about 10 rows are visible.  How can I make the whole spreadsheet visible?

A link to advanced automation procedures would be appreciated (hide toolbars, printing, etc).

The reason I am using Excel vs another grid is that I like, and are more familiar with, the formatting functions of Excel sheets (vertical cell orientation, comments, etc).  If there is a **free** alternative please let me know.  
Private Sub cmdCreate_Click()
    
    Set oXL = OLE1.object
    Set oSheet = oXL.Sheets(1)
    
    With oSheet
        .Range(.Cells(2, 1), .Cells(NumDays + 1, 7)) = writeTable
    End With
    
    Set oBook = Nothing
    Set oSheet = Nothing
    
    oXL.Visible = True
    
End Sub
 
Private Sub Form_Load()
    OLE1.CreateEmbed vbNullString, "Excel.Sheet"
End Sub

Open in new window

0
Comment
Question by:coderblues
  • 6
  • 4
10 Comments
 
LVL 16

Expert Comment

by:JohnBPrice
ID: 24354925
You could set the OLE1.SizeMode property to 2 (Automatic), however thousands of rows are too big to fit on screen.  You can manually set the size by changing the OLE1 height and width properties to fit nicely in your form, and then add scroll bars as discussed here http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_22610942.html

0
 

Author Comment

by:coderblues
ID: 24356738
Sizemode does nothing to the spreadsheet that is created - still only have 10 rows of data.

If I right click on the OLE portion of the form and click edit (runtime) I can stretch the spreadsheet for more info.  I just want the spreadsheet to show more rows.  I can add the scrollbars later.
0
 
LVL 16

Expert Comment

by:JohnBPrice
ID: 24356790
If you change OLE1.height does it show more rows?  Make sure that size mode is set to Clip.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

Author Comment

by:coderblues
ID: 24358439
That doesn't help either.  Height is set to 11055.  There is a bunch of empty space from the bottom of the last row generated and the size of the OLE component on the form.

If you populate the writeTable array with:

for NumDays = 1 to 1000
  writetable(numdays) = numdays
next numdays

or something similar are you saying that what you specify above works?
0
 
LVL 16

Expert Comment

by:JohnBPrice
ID: 24359085
I loaded the OLE control with an existing spreadsheet, but yes, it works, if the control is small I get 10 rows, if I make it taller, I get more rows.  As many as will fit in the OLE control.
0
 

Author Comment

by:coderblues
ID: 24359474
I have the control as big as the form will hold and I still only get 10 rows.  Do you make the control bigger at runtime or in design mode?  What is the size of your control in designer?
0
 
LVL 16

Accepted Solution

by:
JohnBPrice earned 500 total points
ID: 24363531
I did it at run time, just a button that increases the size, here is the code and before and after pictures
Private Sub Form_Load()
    OLE1.SourceDoc = "C:\test\test.xls"
    OLE1.Action = 1
 
End Sub
 
Private Sub Command1_Click()
    OLE1.Height = 5000
 
End Sub

Open in new window

before.JPG
After.JPG
0
 
LVL 16

Expert Comment

by:JohnBPrice
ID: 24363548
Here is the entire form code including the properties for the OLE1 object.  Save this as a form and see it if works for you.

VERSION 5.00
Begin VB.Form Form1 
   Caption         =   "Form1"
   ClientHeight    =   5865
   ClientLeft      =   60
   ClientTop       =   450
   ClientWidth     =   7515
   LinkTopic       =   "Form1"
   ScaleHeight     =   5865
   ScaleWidth      =   7515
   StartUpPosition =   3  'Windows Default
   Begin VB.CommandButton Command1 
      Caption         =   "Command1"
      Height          =   495
      Left            =   5520
      TabIndex        =   1
      Top             =   240
      Width           =   1815
   End
   Begin VB.OLE OLE1 
      Height          =   2175
      Left            =   480
      TabIndex        =   0
      Top             =   240
      Width           =   4575
   End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
 
Private Sub Form_Load()
    OLE1.SourceDoc = "C:\test\test.xls"
    OLE1.Action = 1
 
End Sub
 
Private Sub Command1_Click()
    OLE1.Height = 5000
 
End Sub

Open in new window

0
 
LVL 16

Expert Comment

by:JohnBPrice
ID: 24363558
Here is my sample spreadsheet, in case that matters.  It is just a few columns of 200 rows.

Test.xls
0
 

Author Closing Comment

by:coderblues
ID: 31579969
Thank you for your help.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question