Access 2003 trouble - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Access 2003 trouble

Author

Topic

chobo
Starting Member

11 Posts

Posted - 2004-02-27 : 22:20:12

Hi, I am new to the forums and to programming in access. What i am trying to do is open a connection to excel through access, and gain access to the values in a worksheet.

Or is it possible to take all the data from an excel worksheep and put it in a ado recordset in access? This is all I have at the moment, and it gives me a runtime error 429 - active x component can't create object.

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim ExcelSheet As Object

Set ExcelSheet = CreateObject(Excel.Application)
ExcelSheet.Application.Visible = True

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-02-28 : 02:01:56

When you are in your Visual Basic edit window, go to Tools, References and browse the list to make sure you have Microsoft Excel Objects checked to make the object available to your code.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-02-28 : 09:52:17

if you use the reference, you can use early binding:

dim xl as new excel.application
dim wb as excel.workbook
dim ws as excel.worksheet

set wb = xl.workbooks.add
set ws = wb.worksheets(1)

xl.visible = true
ws.cells(1,1) = "Hi there"

If you don't use a reference to the Excel object library, you must use LATE binding, which is kind of what you were doing before but you had some syntax wrong, and you were trying to store an application object into a variable called "ExcelSheet" which may be confusing.

this is a late binding version:

dim xl as object
dim wb as object
dim ws as object

set xl = createobject("Excel.Application") ' Note the quotes !!! must be a string
set wb = xl.workbooks.add
set ws = wb.worksheets(1)

xl.visible = true
ws.cells(1,1) = "Hi there"

Pros and cons of each:

Late binding - more flexible; can work with most any version of Excel stored on PC; slower performance; no intellisense when designing; useful if the Excel features of your app are optional and maybe not all users will have Excel

Early binding -- must reference a specific library (i.e., Excel 8.0 vs. 9.0) ; much better performance; intellisense is available when designing

- Jeff

chobo
Starting Member

11 Posts

Posted - 2004-02-28 : 16:09:05

Thanks for the help guys! I get kinda lost when using vb, and vba because each version seems to change things around, and you need references for this and that.

chobo
Starting Member

11 Posts

Posted - 2004-02-28 : 16:50:35

I am trying to take specific cell data from an existing excel worksheet, and have that data display in a msgbox in access.
Ex. Display "john" from cell C10 in an access msgbox. This is what I have so far. Don't mind the sloppy code, this stuff is still new to me.

Dim dbs As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim x1 As Object
Dim wb As Object
Dim ws As Object

Set dbs = CurrentProject.Connection
FollowHyperlink "C:\temp\exceldata.xls", _
"Sheet1" & "!C10"
rst.Open "select firstname from tblinstructors order by firstname", dbs, adOpenDynamic, adLockOptimistic
Do While rst.EOF = True
!firstname = "!C10"
Loop
Set dbs = Nothing
Set rst = Nothing

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-02-29 : 14:15:36

no offense, but I cannot make any sense out of the code you've provided ... why are you using ADO recordsets? what is "!firstname" ?? that won't compile ... where is the msgbox ? why are you following hyperlinks ?

sorry, I'm totally lost ...

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources