ADO versus VBA

[ytayta555]

HI ALL

Can somebody please to learn me if ADO
can do an autofill method in a closed workbook ? ...
.... to do in a closed workbook the same action
like in VBA :
Range("B91:B7000").Select
Selection.AutoFill Destination:=Range("B91:BB7000"),
Type:=xlFillDefault

???This is my first information that I need strongly !

2.The second thing is to know if ADO can do ClearContents in a closed
workbook like in VBA code :
Sheets("1").Range("C91:BB22005").ClearContents,
but with the workbook not open .

____________________________________
I'm beginner in ADO , I don't know to
write querry in ADO to corespund to this 2
actions in VBA : Range("B91:B65536").Select
Selection.AutoFill Destination:=Range("B91:BB65536"),
Type:=xlFillDefault , and Sheets("1").Range("C91:BB65536").
ClearContents ;
This are only 2 things I need (and I don't know if
ADO do this actions with workbook not open ). Can
somebody give me any sugestions and help ?


Thank you very much ,any sugestion is very
helpfull for me .

 

[Mark Ivey]

ADO is a method used to connect to a data source and retrieve information.
There are a variety of uses for ADO (even in VBA).

I would strongly recommend searching the net to see what ADO can do for you.

Here are few links to get you started:

http://support.microsoft.com/kb/257819

http://www.exceltip.com/st/Import_d...l_(ADO)_using_VBA_in_Microsoft_Excel/427.html

http://www.microsoft.com/technet/scriptcenter/resources/officetips/jun05/tips0607.mspx

http://www.excelguru.ca/taxonomy/term/2



Mark Ivey

 

[ytayta555]

ADO is a method used to connect to a data source and retrieve information.
There are a variety of uses for ADO (even in VBA).

Thank very much for links , Mark ; I understand
that ADO can extract data from a few source of
database , I want to know if it (ADO) can do
and some actions in this database ;(and , I said,
I need autofill and delete a range )

 

[Mark Ivey]

I have not had much experience with ADO and Excel...

From my understanding, you can only retrieve information from a data source
with ADO using queries. I do not think it is possible to alter a data
source, but I could be mistaken.

If anyone else has better information for this topic, please post your
thoughts, ideas...

Mark

 

[Don Guillett]

Sounds like it might be easier to just open the workbook

 

[ytayta555]

Sounds like it might be easier to just open the workbook

Thanks again

I have now 231 workbooks and the number can became
bigger ; I find that in ADO you can work with SQL querrys;
maybe SQL can do this actions (autofill and delete) in my
database ;I am looking for (...normaly..) speed .I just read in
an Mark's link that ADO can to write data in a database , too ;
maybe I'll find how to do and this 2 actions (if it is posible ),
autofill and delete .

 

[Jon Peltier]

I don't see how ADO could do autofill; ADO is not a worksheet modification
tool. ADO can definitely extract data from a closed workbook, and though I
have little experience, it is my impression that it can also store data in a
closed workbook. You might be able to use VBA in conjunction with ADO to (a)
calculate the values that need to be filled in and modify the related
records, or (b) delete the appropriate records.

- Jon

 

[ytayta555]

I don't see how ADO could do autofill; ADO is not a worksheet modification
tool. ADO can definitely extract data from a closed workbook, and

Many thanks for informations , I'll work .
Or maybe SQL can do this , have anybody any ideea ?

Respectfully

 

[Dick Kusleika]

Can somebody please to learn me if ADO
can do an autofill method in a closed workbook ? ...
... to do in a closed workbook the same action
like in VBA :
Range("B91:B7000").Select
Selection.AutoFill Destination:=Range("B91:BB7000"),
Type:=xlFillDefault

What kind of data is in B91? Is it a number? A date? ADO can't do an
AutoFill, but you may be able to do something similar. Generally when you
use ADO to read or write to a closed workbook, the worksheet to which you
are writing is set up like a database table - that is, each row is like a
record and each column is a field.

2.The second thing is to know if ADO can do ClearContents in a closed
workbook like in VBA code :
Sheets("1").Range("C91:BB22005").ClearContents,
but with the workbook not open .

I think you can simply write a bunch of empty strings. It wouldn't be the
same as ClearContents, but it may suit your needs. The same concern about
setting up your worksheet as a table applies.

Having said all that, I agree with everyone else who said this sounds like a
bad idea.

 

[ytayta555]

What kind of data is in B91?  Is it a number?  A date?  ADO can't doan
AutoFill, but you may be able to do something similar.  Generally when you
use ADO to read or write to a closed workbook, the worksheet to which you
are writing is set up like a database table - that is, each row is like a
record and each column is a field.

In Range (B91:B65536) are 65446 Count function !!
can ADO do something similar AutoFill
Destination:=Range("B91:BB65536")?

Having said all that, I agree with everyone else who said this sounds likea
bad idea.> Dick Kusleika
Microsoft MVP-Excelhttp://www.dailydoseofexcel.com

I just try , and many thaks for help and sugestions

 

[Dick Kusleika]

In Range (B91:B65536) are 65446 Count function !!
can ADO do something similar AutoFill
Destination:=Range("B91:BB65536")?

Functions? I don't think it will work. You'll have to open Excel to do
what you want.

 

[RB Smissaert]

It will depend on what those Count functions are doing,
but maybe you could replace them with SQL, so it can run via ADO. If you
have 65446 Count functions then
I think you might have the wrong approach.

RBS

What kind of data is in B91? Is it a number? A date? ADO can't do an
AutoFill, but you may be able to do something similar. Generally when you
use ADO to read or write to a closed workbook, the worksheet to which you
are writing is set up like a database table - that is, each row is like a
record and each column is a field.

In Range (B91:B65536) are 65446 Count function !!
can ADO do something similar AutoFill
Destination:=Range("B91:BB65536")?

Having said all that, I agree with everyone else who said this sounds like
a
bad idea.> Dick Kusleika
Microsoft MVP-Excelhttp://www.dailydoseofexcel.com

I just try , and many thaks for help and sugestions

 

[Jon Peltier]

SQL isn't a worksheet editor, either, but SQL + VBA might have the same
chance of working as ADO + VBA.

- Jon

 

[Jon Peltier]

You mean you want to fill a function into the range of cells? I think you
will have to open the file in Excel.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

What kind of data is in B91? Is it a number? A date? ADO can't do an
AutoFill, but you may be able to do something similar. Generally when you
use ADO to read or write to a closed workbook, the worksheet to which you
are writing is set up like a database table - that is, each row is like a
record and each column is a field.

In Range (B91:B65536) are 65446 Count function !!
can ADO do something similar AutoFill
Destination:=Range("B91:BB65536")?

Having said all that, I agree with everyone else who said this sounds like
a
bad idea.> Dick Kusleika
Microsoft MVP-Excelhttp://www.dailydoseofexcel.com

I just try , and many thaks for help and sugestions