Use VB6 Form OLE Excel File
Bob Askey
disabled so I was told this would be a good way to do it.
Created my project, add OLE to my form, browsed to the file, worked.
Now nothing fits correctly. I got the OLE to Autosize. The VB form
never really worked right and I could not see all of the sheet or
scroll down. It appears the form needs to resize according to the OLE
size but I have not been able to figure out how.
Can anyone give me some example code or suggestions that would help do
this or another method that might work better then adding an OLE to
the form.
Thanks,
Bob
Geoff
"Bob Askey" <ask...@hotmail.com> wrote in message
news:698e56c.03120...@posting.google.com...
Will the workbooks - BeforePrint event.
give you what you want?
Option Explicit
Private WithEvents oApp As Excel.Application
Private WithEvents oWrkBook As Workbook
Private Sub Form_Load()
Set oApp = New Excel.Application
With oApp
.Visible = True
End With
End Sub
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
Set oWrkBook = Nothing
Set oApp = Nothing
End Sub
Private Sub oApp_WindowActivate(ByVal Wb As Excel.Workbook, ByVal Wn As
Excel.Window)
Set oWrkBook = Wb
End Sub
Private Sub oApp_WorkbookDeactivate(ByVal Wb As Excel.Workbook)
Set oWrkBook = Nothing
End Sub
Private Sub oWrkBook_BeforePrint(Cancel As Boolean)
Cancel = True
Dim bln As Object
Set bln = oApp.Assistant.NewBalloon
With bln
.Heading = "Printing has been Disabled."
.Text = "Wasting ink usually results in a sacking."
.Show
End With
End Sub
Regards
Geoff
--
Bob Askey
Geoff,
I tried your code. It opens Excel but not the file. Plus I opened
the file and there where some errors when I tried to print. Excel
claimed it was waiting on another application to complete. The
balloon code failed so I put a msgbox in. After several error
messages I got the msgbox.
I just need the point to a file and open it. Be viewable Left Right
and down. User can not print the file while being viewed on the VB6
form using OLE or any other suggested method.
Thanks for your help,
Geoff
or any evasive action either in case excel did not have balloon support
as problem with using a messagebox is that it will display in your project
rather that excel but........
My main point I was I think you will need to use Excels BeforePrint
event either way even if you use an embedded doc in an OLE control,as once
the doc
is embedded and is active for edit its sizing handles can be right clicked
and the option
to print can be selected, the above event can be used to prevent the
printing.
As for sizing the embedded object to the OLE control I am not aware of how
that's
done or even if it can be, I never use the OLE control only the Excel OM.
But as I see it even in OLE you will need to capture this event to disable
any printing
HTH a little
Sorry I cant be of more help
Cheers
Geoff
Option Explicit
Dim WithEvents myob As Workbook
Private Sub Form_Activate()
With OLE1
.CreateEmbed "C:\Test.xls"
.AutoVerbMenu = False
.DoVerb vbOLEShow
Set myob = .object'Get a ref to the workbook
End With
End Sub
Private Sub myob_BeforePrint(Cancel As Boolean)
MsgBox "Disabled"
Cancel = True
End Sub
"Bob Askey" <ask...@hotmail.com> wrote in message
news:698e56c.03120...@posting.google.com...
Bob Askey
Thanks for the effort. It appears that the OLE option is a bit
lacking. In order for a fairly large file to display you would think
there would be a way to resize the form to fit the container. Also an
easy way to add scroll bars. From what I have read it is possible to
add scroll bars but a lot of work.
Thanks,
Bob
Snake
versitile and it can contain any Office document.
Snake
Steve Gerrard
Looks very interesting, though I haven't tried it yet. The poop on why
most containers don't work well as ActiveX document containers certainly
deflates all the hype about embedded objects.
"Snake" <drakeyk...@yahoo.co.uk> wrote in message
news:f1fd6df9.03120...@posting.google.com...
Snake
a resizeable form, it seems to want to keep showing the Web Components
Toolbar for some reason.
I've used it to run complex data entry spreadsheets where DAO code
runs directly from within the spreadsheet itself and it works very
quickly. I have had issues with what happens when a user already has
a copy of Excel open when using the control or when the user opens a
copy of Excel when the form is open but haven't been able to find out
what the problems were or why they happened as the control is not
supported (finding any sort of MS support is almost as difficult as
finding a virgin in Slough!!)
Snake
"Steve Gerrard" <notstev...@comcast.net> wrote in message news:<PfOdnX8in4S...@comcast.com>...
Bob Askey
found some code that made the scroll bars work better and added that.
I changed the Picture1 and Picture2 to OLE1 and OLE2. I had to change
a couple of lines of code and add a line for an Embeded file to open.
I also changed the following settings for OLE2.
AutoActivate = GetFocus (Default is DoubleClick)
AutoVerbMenu = False (I don't want the user to do anything to the
file.)
Start new project.
Form1 add the following using default names:
Add scroll bars Horizonal and Vertical
Add OLE1 to the form when prompted for a file cancel.
Add OLE2 inside of OLE1 when prompted for a file cancel.
Paste the following code to the form replacing Form_Load if already
there.
Remember to replace the filename with a filename on your computer. I
opened both Word and Excel files.
'Begin Code------------------------------------
Private Sub Form_Resize()
' When the form size is changed, the ole1 dimensions are changed
' to match.
OLE1.Height = Form1.Height
OLE1.Width = Form1.Width
' Re-Initializes picture postitions & scroll bars.
OLE1.Move 0, 0, ScaleWidth - VScroll1.Width, ScaleHeight -
HScroll1.Height
OLE2.Move 0, 0
HScroll1.Top = OLE1.Height
HScroll1.Left = 0
HScroll1.Width = OLE1.Width
VScroll1.Top = 0
VScroll1.Left = OLE1.Width
VScroll1.Height = OLE1.Height
HScroll1.Max = OLE2.Width - OLE1.Width
VScroll1.Max = OLE2.Height - OLE1.Height
' Checks to see if scroll bars are needed
VScroll1.Visible = (OLE1.Height < OLE2.Height)
HScroll1.Visible = (OLE1.Width < OLE2.Width)
End Sub
Private Sub Form_Load()
Const PIXEL = 3
'Add the following constant only in Visual Basic 1.0:
' Const TRUE = -1
Const NONE = 0
' Set design properties, included here for simplicity.
Form1.ScaleMode = PIXEL
OLE1.SizeMode = 2
' AutoSize is set to TRUE so that the boundaries of
' ole2 are expanded to the size of the actual file.
OLE2.SizeMode = 2
' Get rid of annoying borders.
OLE1.BorderStyle = NONE
OLE2.BorderStyle = NONE
With VScroll1
.LargeChange = 100 'set to what ever works for you
.SmallChange = 10
End With
With HScroll1
.LargeChange = 100
.SmallChange = 10
End With
'**********************************************************
'Change Filename you want to open.
' Load the file that you want to display.
Me.OLE2.CreateEmbed "C:\Documents and Settings\Bob\My
Documents\WorkFiles\NewSpareParts.xls"
'**********************************************************
' Initialize location of both OLEs.
OLE1.Move 0, 0, ScaleWidth - VScroll1.Width, ScaleHeight -
HScroll1.Height
OLE2.Move 0, 0
' Position the horizontal scroll bar.
HScroll1.Top = OLE1.Height
HScroll1.Left = 0
HScroll1.Width = OLE1.Width
' Position the vertical scroll bar.
VScroll1.Top = 0
VScroll1.Left = OLE1.Width
VScroll1.Height = OLE1.Height
' Set the Max value for the scroll bars.
HScroll1.Max = OLE2.Width - OLE1.Width
VScroll1.Max = OLE2.Height - OLE1.Height
' Determine if child picture will fill up screen.
' If so, then there is no need to use scroll bars.
VScroll1.Visible = (OLE1.Height < OLE2.Height)
HScroll1.Visible = (OLE1.Width < OLE2.Width)
End Sub
Private Sub HScroll1_Change()
OLE2.Left = Not HScroll1.Value
OLE2.SetFocus
End Sub
Private Sub HScroll1_Scroll()
OLE2.Left = Not HScroll1.Value
OLE2.SetFocus
End Sub
Private Sub VScroll1_Change()
OLE2.Top = Not VScroll1.Value
OLE2.SetFocus
End Sub
Private Sub VScroll1_Scroll()
OLE2.Top = Not VScroll1.Value
OLE2.SetFocus
End Sub
'End Code------------------------------------
Geoff
> found some code that made the scroll bars work better and added that.
Woops, I thought you needed to edit the doc.
If you just need to display it,
then your spot on with what you have done.
However
The only slight snag I see with your sample is the scrollers are a bit
mischievous.
Personally I would scrap one of the OLE's for a picturebox as the view port.
with the following code it should improve the scrollbars even more.
OLE1 inside Picture1
H&V Scrollbars on the form
Cheers
HTH
Geoff
---
Option Explicit
Dim SB_gWidth As Long
Private Sub Form_Activate()
Form_Resize
End Sub
Private Sub Form_Load()
'
SB_gWidth = Me.VScroll1.Width
Me.HScroll1.Width = SB_gWidth
With Me.OLE1
.Move 0, 0
.BorderStyle = vbBSNone
.AutoVerbMenu = False
'Your Document
.CreateEmbed "C:\test.xls"
.SizeMode = vbOLESizeAutoSize
End With
Picture1.BorderStyle = vbBSNone
End Sub
Private Sub Form_Resize()
Dim lHVar As Long
Dim lWVar As Long
'See if scrolls are needed
If ScaleWidth > OLE1.Width + SB_gWidth Then
HScroll1.Visible = False
lHVar = 0
Else
HScroll1.Visible = True
lHVar = SB_gWidth
End If
If ScaleHeight > OLE1.Height + SB_gWidth Then
VScroll1.Visible = False
lWVar = 0
Else
VScroll1.Visible = True
lWVar = SB_gWidth
End If
'Size scrolls to form
HScroll1.Move 0, ScaleHeight - lHVar, ScaleWidth - lWVar, SB_gWidth
VScroll1.Move ScaleWidth - lWVar, 0, SB_gWidth, ScaleHeight - lHVar
'Move View Port
Picture1.Move 0, 0, ScaleWidth - lWVar, ScaleHeight - lHVar
'Set a H scroll value
With HScroll1
If .Visible Then
.Max = (OLE1.Width - Picture1.Width)
'Uses IIf you may need to use if then else instead
'Not sure if pre VB6 has the IIf function
.SmallChange = IIf(.Max * 0.1 < 1, 1, .Max * 0.1)
.LargeChange = IIf(.Max * 0.5 < 1, 1, .Max * 0.5)
End If
End With
'Set a V Scroll value
With VScroll1
If .Visible Then
.Max = (OLE1.Height - Picture1.Height)
.SmallChange = IIf((.Max * 0.1) < 1, 1, .Max * 0.1)
.LargeChange = IIf(.Max * 0.5 < 1, 1, .Max * 0.5)
End If
End With
End Sub
Private Sub ScrollOLE()
OLE1.Move 0 - HScroll1.Value, 0 - VScroll1.Value
OLE1.SetFocus
End Sub
Private Sub HScroll1_Change()
ScrollOLE
End Sub
Private Sub HScroll1_Scroll()
ScrollOLE
End Sub
Private Sub VScroll1_Change()
ScrollOLE
End Sub
Private Sub VScroll1_Scroll()
ScrollOLE
End Sub
Bob Askey
Form's two OLE's with one Picture Box and an OLE inside of it. For
some reason I can't get back to the code to respond. I'm using Google
being that spammers will harvest all your addresses here.
Thanks Geoff that worked really well.
Bob
Bob
I am using your code but just realized that if I maximize and then go back
to normal the size between the Picutre1 and Ole1 increases each time. Looks
like it is increasing each time I change normal to maximize and back.
Any ideas on how to keep the screen sizes sync correctly?
Thanks,
Bob
Bob
containers to the scroll bars. This seems to increase with each resize.
"Bob" <notagoo...@home.com> wrote in message
news:rKMAb.459628$Fm2.453262@attbi_s04...
Geoff
> Should make a comment here I mean the form increases leaving a gap from
> containers to the scroll bars. This seems to increase with each resize.
>
>
>
The OLE re-sizes to the document
The Picture Box re-sizes to the form
The Picture Box allows room for scrolls
between it and the edge of the form if the
OLE (document) does not fit in the form.
The only thing I can think you on is
being able to see the edge of the OLE
(& Document) where it meat the Picturebox.
If that's the case you can set the backcolor
of the picture box to Windows Background.
or whatever it is then the same background will be
shown though.
Also I should have mentioned put
On Error resume next in the resize event
to let it skip out trying to resize things when the
form is that small that them things wont fit.
If its not the above then give it me again.
Regards
Geoff
---.
Bob
I changed Background of Picture1 to Windows Default and that did the trick.
Shouldn't the Picture1 and the Ole1 container stay the same size?
Thanks,
Bob
"Geoff" <n...@notspam.org> wrote in message
news:br0o4d$eia$1...@news8.svr.pol.co.uk...
Geoff
> Geoff,
>
> I changed Background of Picture1 to Windows Default and that did the
trick.
> Shouldn't the Picture1 and the Ole1 container stay the same size?
No,Its the OLE control & the document that auto to the same size.
The picture box is sized to the max viewing area of the form.
the OLE (doc) floats about inside this area.
If the OLE(doc) is bigger than Pic1 then
Pic1 is reduced slightly to accommodate for a scroll bar.
If the form is enlarged then pic1 will increase with the form as
the viewing area increases, it is therefore possible
to allow the OLE document to be scrolled out of view in all directions, it
just so happens that in this case you don't allow the OLE such
freedom.
When the doc is smaller than the form, then you will get a free area
(you could limit this on resize but a maximized form would need it)
If it was fully scrollable in all directions it would
probably be better as a default workspace colour.
As it is colour merged with the doc
should be fine:-)
Cheers
Geoff
--
Bob Askey
Sorry to keep this going. I have been working on this at home. Today
I changed my app to point at the folders I will be working with.
Excel files when maximized in the Ole window the verticle scroll bar
does not go down all the way to the last row of the workbook. Seems I
am getting something close to 8 1/2 by 11 and beyond that it cuts off.
The Excel Workbook is slightly long. The width fits fine.
Any ideas on this one. I think your comment below has something to do
with this.
Thanks,
Bob
"Geoff" <n...@notspam.org> wrote in message news:<br195q$itj$1...@newsg1.svr.pol.co.uk>...
Geoff
I think the problem will be that the OLE is not really designed for viewing
docs but embedding objects for active editing.When it displays an
image of the object, I guess its only to give the user an insight as to what
it is.
The problem you have is you are opening an ActiveX Document and that's
exactly what it wants to be active, so as in would be in excel you
will have a problems trying to make it totally inactive but keep alive
the worksheets scroll bars.
I know it seems a simple thing to want to do, but I guess
the problem is that the
controls that open them easily open them using active X.
(e.g. the web browser is another that would open it)
How you can change it to be inactive is unknown to me and I have
my doubts as to if its feasibly possible,I guess it would at least require
getting a reference to the objects and disabling stuff without disabling
the scrolls.
The simplest way I can think of displaying an excel file would be to
forget the active x stuff and get the data only.
set up an ODBC excel driver and DNS connection to the xls file in the ODBC
control panel
(or from an ADO control connection property)
Connect a hidden ADO data control and bind a (ODBC) Grid to it .
How flexible this needs to be will depend on :
Where the file is kept?
Can the sheet change name?,
Could the directory change?
Do other files need to be opened in the same grid?
Experience in ADO also........
All this will add to how it would be coded
and whether simple binding or not is enough.
It sounds a bit of a performance but I have
just successfully tested the above binding without using
a line of code.
Regards
Geoff
"Bob Askey" <ask...@hotmail.com> wrote in message
news:698e56c.03120...@posting.google.com...