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

I will try that. Normally use ADO but at there is a column with mixed data
in it , the column in question has mostly dates in it but there is some text
(which i need) the text is not brought over using ADO
--
with kind regards

Spike


"strive4peace" wrote:

> 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
>

Hi Spike,

there is a column with mixed data
in it

make a calcualted column like this:

=TEXT(A1,"##,##0.00")

if the column contains text, it will show even though it doesn't conform
to the format -- numbers will be formatted as specified


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



Spike wrote:
> I will try that. Normally use ADO but at there is a column with mixed data
> in it , the column in question has mostly dates in it but there is some text
> (which i need) the text is not brought over using ADO

Changed the reference to 3.6 and works a dream many thanks much appreciated
--
with kind regards

Spike


"Spike" wrote:

> I will try that. Normally use ADO but at there is a column with mixed data
> in it , the column in question has mostly dates in it but there is some text
> (which i need) the text is not brought over using ADO
> --
> with kind regards
>
> Spike
>
>
> "strive4peace" wrote:
>
> > 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
> >

Sorry I should have mentioned the reference needed to be set.