I have a file (quite large) that unfortunately has mixed formatted data in
one column; so i cannot use ADO to import it as it does not pick up all the
data, is there any other method apart from open the file and copy and past
values only

Any advices will be gratefully received
--
with kind regards

Spike

DAO

Many thanks will do
--
with kind regards

Spike


"Dave Miller" wrote:

DAO

Here is a sample function:

Regards,

David Miller

Function QuerySheet()
Dim db as DAO.Database, _
rst as DAO.Recordset, _
Source as string

Source = "C:\FileName.xls"
Set db = OpenDatabase(Source, _
dbDriverNoPrompt, _
False, _
"Excel 8.0")
Set rst = db.OpenRecordset("Named Range Here")

with rst
'Do Something
end with

Set rst = Nothing
Set db = Nothing
End Function

Thanks for your example code i am having trouble making it run. I am running
Excel 2003, i have set Tools/Refs to Microsoft DAO 3.51 Object Library and
get an error message "Run Time Error 429. ActiveX Component can't create
object"

I have adapted your code as below and it bombs out on the line "Set db=
OpenDatabase etc

Function QuerySheet()
Dim db As DAO.Database, rst As DAO.Recordset, Source As String

Source = "H:\Cash Recs\NetAssets.xls"

Set db = OpenDatabase(Source, dbDriverNoPrompt, False, "Excel 8.0")
Set rst = db.OpenRecordset("Assets")

With rst
'Do Something
End With

Set rst = Nothing
Set db = Nothing
End Function

However i word it using other code i get an error where the db is empty
--
with kind regards

Spike


"Dave Miller" wrote:

Here is a sample function:

Regards,

David Miller

Function QuerySheet()
Dim db as DAO.Database, _
rst as DAO.Recordset, _
Source as string

Source = "C:\FileName.xls"
Set db = OpenDatabase(Source, _
dbDriverNoPrompt, _
False, _
"Excel 8.0")
Set rst = db.OpenRecordset("Named Range Here")

with rst
'Do Something
end with

Set rst = Nothing
Set db = Nothing
End Function

ADO, DAO?
---


Hi Spike,

are you sure the error message "ActiveX Component can't create
object"

refers that THAT line? It looks like a DAO component (Dim db As
DAO.Database) not an activeX component

I suspect you have another line in your code where you are referencing a
recordset that was just Dimmed and not prefaced with DAO and since the
ADO library is higher in the order for your list of references than the
DAO library and both have recordset objects, Excel is try to use it as
an ActiveX object...

also, try using the Microsoft DAO 3.6 Object Library :)

what happens when you compile?

also, don't forget to
rst.close
db.close
(since you actually Opened the database, you have to close it unlike
setting db to CurrentDb)

~~~

or, could be...
I am also not sure you can access an Excel spreadsheet using DAO -- you
may have better luck with ADO...

why do you not want to use ADO?


Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Spike wrote:
Thanks for your example code i am having trouble making it run. I am running
Excel 2003, i have set Tools/Refs to Microsoft DAO 3.51 Object Library and
get an error message "Run Time Error 429. ActiveX Component can't create
object"

I have adapted your code as below and it bombs out on the line "Set db=
OpenDatabase etc

Function QuerySheet()
Dim db As DAO.Database, rst As DAO.Recordset, Source As String

Source = "H:\Cash Recs\NetAssets.xls"

Set db = OpenDatabase(Source, dbDriverNoPrompt, False, "Excel 8.0")
Set rst = db.OpenRecordset("Assets")

With rst
'Do Something
End With

Set rst = Nothing
Set db = Nothing
End Function

However i word it using other code i get an error where the db is empty