coderblues
asked on
VB Excel OLE
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.
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
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 https://www.experts-exchange.com/questions/22610942/How-to-add-scrollbars-on-an-VB6-OLE-box.html
ASKER
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.
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.
If you change OLE1.height does it show more rows? Make sure that size mode is set to Clip.
ASKER
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?
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?
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.
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
Thank you for your help.