Solved

VB Excel OLE

Posted on 2009-05-10
10
1,864 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
Comment Utility
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
Comment Utility
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
Comment Utility
If you change OLE1.height does it show more rows?  Make sure that size mode is set to Clip.
0
 

Author Comment

by:coderblues
Comment Utility
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
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:coderblues
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you for your help.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

772 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now