Ms Excel 11.0 Object Library

[ktwk]

hi all experts and grandmasters,

i have bind an Ms excel 11.0 object to my form, for those who have downloaded my file (topic: subscript out of range) should have seen it, now i face another problem :), i have a textbox and a command button in "Form A", when i enter an Ms Excel file name to the textbox box and click the command button , "Form B" will appear, now, my ms excel 11.0 object( let's name it Spreadsheet0) is in my "Form B". I need whatever i have in my the Excel file to show in the ms excel 11.0 object.

here is the code


Dim excelWorkbook As Excel.Workbook

If IsNull(Me.Text0) Then
MsgBox "Please key in the exact file name, thank you", vbOKOnly
Else
Set excelWorkbook = GetObject("C:\" & Me.Text0 & ".xls")
excelWorkbook.Application.Visible = True excelWorkbook.Application.Windows("" & Me.Text0 & ".xls").Visible = True
stDocName = "CodeList"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If


notice that i didn't close or save the automation object, i will leave it to another button to do it, my situation right now is i am able to execute the file i want, but it does not appear in my object(in the form), the code open the file for me, that all. i know the code above is mean to open the file i want,what i need some hints what to do next, thanks a lot

KT

[ktwk]

sorry, my code somehow de-form


Dim excelWorkbook As Excel.Workbook

If IsNull(Me.Text0) Then
MsgBox "Please key in the exact file name, thank you", vbOKOnly
Else
Set excelWorkbook = GetObject("C:\" & Me.Text0 & ".xls")
excelWorkbook.Application.Visible = False
excelWorkbook.Application.Windows("" & Me.Text0 & ".xls").Visible = True
stDocName = "CodeList"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

[XLGibbs]

Dim oApp as Object,oWB as Workbook
Set oApp = CreateObject(Excel.Application)

oApp.Visible = False


If IsNull(Me.Text0) Then
MsgBox "Please key in the exact file name, thank you", vbOKOnly
Else
Set oWB = oAPP.Open("" & Me.Text0 & ".xls")
stDocName = "CodeList"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If


You are better of using Late Binding, and avoid needing to set a reference to the object.

[Ken Puls]

You are better of using Late Binding, and avoid needing to set a reference to the object.

That sounds like the gateway to controversy... ;)

[XLGibbs]

That sounds like the gateway to controversy... ;)

Quite, but in this case, don't you agree? What if the user doesn't "have" Excel 11.0?

I swing through the gate both ways depending on the particular solution..

[Ken Puls]

It depends on the user base. If they all have Excel 11.0, then the performance of the Early bind would be better. Having said that, I work in an environment that is mixed Excel 97 and Excel 2003. Since Excel 2003 has a bad habit of "upsizing" to the latest library, it kills the app for the next 97 user. For that very reason, everything I do is released with a late bind.

I believe that it says it on Bob Phillips site (sorry, Bob, if it doesn't): "Develop early, release late."

:)

[XLGibbs]

Well yes, it is dependent on the user base entirely. Early bind is better if they are all on the same version.