VB6 open XL in OLE

[Dave]

I can't seem to get this right. Using VB6, I have a form with an OLE control (MS ws) and I would like to open/display/use an XL wb. If this is possible, can someone provide me with some syntax or a link to some info? Thanks. Dave


[VBA]
Sub OpenXL()
Dim objExcel As Object, objWorkBook As Object, objWorksheet As Object
'display XL wb in form1 OLE1(MS XL Ws)
Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkBook = objExcel.Workbooks.Open("c:\tmpfile.xls")
Set objWorksheet = objWorkBook.Worksheets("Sheet1")
'syntax for opening Wb in OLE1????

'etc
End Sub
[/VBA]

[Norie]

http://www.vbexplorer.com/VBExplorer/ole1.asp

[Dave]

Thanks Norie again. That was almost perfect. I just want to open the file in the container.. I think. Seems easy enough but I can't seem to get the syntax? Dave
[VBA] Dim objExcel As Object, objWorkBook As Object

Set objExcel = CreateObject("EXCEL.APPLICATION")
objExcel.Workbooks.Open ("c:\tmpfile.xls")
'this doesn't work???
objOLEContainer.CreateLink objExcel.FileName
[/VBA]

[Norie]

I just want to open the file in the container.. I think.

Dave

That's what I thought but I thought I'd post the link anyway.

I tried various properties eg SourceItem but couldn't get the OLE container to work with an existing workbook.

I could open it with a 'blank' workbook though.

There must be a way I suppose, I'll look into it further.

By the way I found that link via a google search, so maybe you could try that._

[Tommy]

This should just show the file on the OLE you don't have to fire up Excel, that is the OleContainer's job
[VBA]
objOLEContainer.CreateLink FileName
[/VBA]

If you need to edit or show a particular sheet let me know.

[Norie]

Tommy

That code does open up Excel.

Scratch that.

It does load the file into the container but to edit it you seem to need to right click and select Edit/Open.

[Dave]

Thanks Tommy and Norie. I think that opens my file but I'm not sure how to use it. If you have a couple of minutes I would very much like to learn how to edit or show a particular sheet. Dave
now using this code (my ole is named objOLEContainer)
[VBA] objOLEContainer.CreateLink "c:\tmpfile.xls"
[/VBA]

[Tommy]

LOL but this is not required to open excel, the ole container does it for you, you just pass the name of the workbook. The worksheet that will be displayed is the worksheet that was active at the last closing of that workbook.
[VBA]
Set objExcel = CreateObject("EXCEL.APPLICATION")
objExcel.Workbooks.Open ("c:\tmpfile.xls")
[/VBA]
Now to get to the data and change it, change sheet etc you will need to fire up you own copy of excel, at least I haven't figured out how with the ole container. LOL that doesn't mean it can't be done, I just haven't done it.

[Norie]

If you double click the control it will open the workbook in Excel.

In code I've been trying to use Verb to open the workbook with no luck.

eg
[vba]
objOLEContainer.Verb = -1
[/vba]

[Dave]

What a tease. I'm starting to get p'd off. It's right there if you right click or double left click on the OLE. Abit of syntax and I think a stand alone exe of XL isn't that far away. I'll repost if I hit a solution. Dave

[Tommy]

Norie
I finally got it to open with
[VBA]
objOLEContainer.DoVerb(vbOLEUIActivate)
[/VBA]

I am able to modify the active/sheet but I can't seem to get it to save without complaining. Still looking.

[Tommy]

Dave,

The below should show how to do most of what you want in code.

I have a form with one command button called command1. On this form I have 1 OLE on the form called OEL1. The code is in the form1.

[VBA]
Private Sub RunShipper()
Dim jobsheet As String
Dim JobExcelSheet As Worksheet
jobsheet = "C:\12345.xls"
Set JobExcelSheet = OLE1.object.Sheets("Sheet1")
JobExcelSheet.Activate
JobExcelSheet.Cells(1, 2).Value = "Great Scott Batman"
JobExcelSheet.Cells(1, 2).Font.Name = "Times New Roman"
JobExcelSheet.Cells(1, 2).Font.Size = 14
JobExcelSheet.Cells(1, 2).Font.Bold = True
JobExcelSheet.Cells(1, 2).Font.Color = vbRed
JobExcelSheet.Columns.AutoFit
OLE1.object.SaveAs jobsheet
OLE1.CreateLink jobsheet
End Sub

Private Sub Command1_Click()
RunShipper
End Sub
[/VBA]

Have a great day!

[Dave]

See this link here for how to add an XL wb as a resource file...
http://cuinl.tripod.com/tutorials/res-11.htm
Now with the following code that file opens as an XL wb in the OLE control. You can edit/save as per usual. I'm thinking that resource files are included when the VB6 project is made an .exe and ergo the XL file becomes an .exe ... Haven't checked this though. Will post if I come up with more. Also re. linking the resource file, if you want to change the XL file added to the resource file, you need to change the file and rename the link (in the following code this needs to be changed ("c:\tmpfile2.xls"). Not sure why? Dave
form code....
[VBA]
Private Sub Form_Load()
On Error Resume Next

'centre the form
'Me refers to this form
'Move is more efficient than setting left and top properties
'we use the \ operator to make integer divisions,
'accuracy is not important but speed is.

Me.Move (Screen.Width - Me.ScaleWidth) \ 2, _
(Screen.Height - Me.ScaleHeight) \ 2
'move the OLE container to cover the rest of the form
objOLEContainer.Move Me.ScaleLeft, _
Me.ScaleTop, _
Me.ScaleWidth, _
Me.ScaleHeight - cbOpenFile.Height

'we want the contents of the container to be automatically
'and proportionally resized
objOLEContainer.SizeMode = vbOLESizeZoom
End Sub
Private Sub cbOpenFile_Click()
Call Loadit
Dim objExcel As Object, objWorkBook As Object
Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkBook = objExcel.Workbooks.Open("C:\tmpfile2.xls")
objOLEContainer.CreateLink "c:\tmpfile2.xls"
objOLEContainer.DoVerb (vbOLEUIActivate)
Set objWorkBook = Nothing
Set objExcel = Nothing
End Sub
Public Sub LoadDataIntoFile(DataName As Integer, FileName As String)
Dim myArray() As Byte
Dim myFile As Long
If Dir(FileName) = "" Then
myArray = LoadResData(DataName, "CUSTOM")
myFile = FreeFile
Open FileName For Binary Access Write As #myFile
Put #myFile, , myArray
Close #myFile
End If
End Sub
Public Sub Loadit()
LoadDataIntoFile 101, "c:\tmpfile2.xls"
End Sub
[/VBA]

[Dave]

I started with a test.xls with a msgbox "Hi Dave", I loaded this file as a resource file then used the above code to create an EXE in VB6. I then used the package and deployment wizard to create a distributable file. It came with an installment wizard and a zip file. The file self extracted and I now have a new program installed which you can click and the form pops up with a command button (that I forgot to mention before) when clicked says "Hi Dave" and the WB is active ...you can save/edit as per usual I suspect. The next step is to see if this will transfer to my laptop. Dave

[Dave]

My laptop now has a new program installed that says "Hi Dave" That is most excellent. It auto installed. I'm very glad. Should have mentioned that my VB6 project had a reference set to the XL object library and I would imagine that's important to do before you save and make exe. This seems to make XL so much better. Dave
edit: should also mention that the 101 number below is the name of the resource file. It doesn't seem to be like text or changing more than once.
[VBA]
LoadDataIntoFile 101, "c:\tmpfile2.xls"
[/VBA]

[Dave]

Thanks Norie and Tommy for the great help. I'm going to mark this thread as solved allthough I do have a followup question probably better suited for a new thread if I'm unable to resolve this... How to transfer the contents of "c:\tmpfile2.xls" back to the resource file then Kill it? Maybe it's not possible. Again, thanks for your help on this post. Dave
edit: hmmm No "Solved" thread tool?