Need help - Ms excel 11.0 Object Library - stuck for days

[kennytwk via AccessMonster.com]

hi all experts and grandmasters,

i have bind an Ms excel 11.0 object to my Access form, 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(CodeList)". I need
whatever i have in my the Excel file to show in the ms excel 11.0 object
(Spreadsheet0). is it possible?

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
' Please note i know what the code below does, i dont want to open the
file, i just want to retrive
' the data in the file and display it in my ms excel 11.0 Object which i
have embeded in my form.
' many many many many thanks, been stuck for dayssssssssssss, gonna start
banging wall soon

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 object, i will leave it to another
button to do it, my situation right now is i need to sort of display the data
in my excel file to the object(Spreadsheet0) in my Form B(CodeList), is it
possible? or do i need to use import ??

another question, if i use import, correct me if i am wrong, we have to use
xml ?, does anyone have any idea of a good website for xml or ms excel 11.0
object library?

KT

 

['69 Camaro]

Hi, Kenny.

is it
possible? or do i need to use import ??

You don't need to embed the Excel Object in your form. If you just need to
display the data for the user, you can link to the spreadsheet as a linked
table, then create a query to order the data in the spreadsheet in the way you
want, and then create a form with the query as the Recordsource to display the
data for the user.

If you need to edit the data, then it depends upon whether or not you've
installed Microsoft Office 2003 SP-2. If you haven't, then you can edit the
linked spreadsheet. If you have, then you won't be able to edit the data in the
linked table, so importing would be recommended.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

['69 Camaro]

Hi, Kenny.

another question, if i use import, correct me if i am wrong, we have to use
xml ?

It's easiest to use the Import Text Wizard. First convert your XLS format file
to CSV format, then open the Access database file and select File -> Get
External Data -> Import..., then navigate to the file, import it and follow the
prompts in the Import Text Wizard.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

['69 Camaro]

Hi, Kenny.

You may be tempted to use the Import Spreadsheet Wizard to import the
spreadsheet as an XLS file, but it's more error prone and much slower than
importing from a text file with the Import Text Wizard. For more information,
please see the following Web page for a link to the article, "Spreadsheet Import
Wizard Error":

http://www.Access.QBuilt.com/html/articles.html

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

Hi, Kenny.

another question, if i use import, correct me if i am wrong, we have to use
xml ?

It's easiest to use the Import Text Wizard. First convert your XLS format
file to CSV format, then open the Access database file and select File -> Get
External Data -> Import..., then navigate to the file, import it and follow
the prompts in the Import Text Wizard.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.