Copy data without opening file and without using ADO

[Guest]

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

 

[Dave Miller]

DAO

 

[Guest]

Many thanks will do

 

[Dave Miller]

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

 

[Guest]

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

 

[strive4peace]

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
*

 

[Guest]

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

 

[strive4peace]

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
*

 

[Guest]

Changed the reference to 3.6 and works a dream many thanks much appreciated

 

[Dave Miller]

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

 

[strive4peace]

Hi Spike,

you're welcome glad it worked out for you!

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