Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO versus VBA
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 . |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO versus VBA
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_da...Excel/427.html http://www.microsoft.com/technet/scr.../tips0607.mspx http://www.excelguru.ca/taxonomy/term/2 Mark Ivey "ytayta555" wrote in message ... 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO versus VBA
On 21 Mar, 14:23, "Mark Ivey" wrote:
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 ) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO versus VBA
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 "ytayta555" wrote in message ... On 21 Mar, 14:23, "Mark Ivey" wrote: 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 ) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO versus VBA
Sounds like it might be easier to just open the workbook -- Don Guillett Microsoft MVP Excel SalesAid Software "ytayta555" wrote in message ... On 21 Mar, 14:23, "Mark Ivey" wrote: 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 ) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO versus VBA
On 21 Mar, 15:25, "Don Guillett" wrote:
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 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO versus VBA
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "ytayta555" wrote in message ... On 21 Mar, 15:25, "Don Guillett" wrote: 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 . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO versus VBA
On 21 Mar, 16:09, "Jon Peltier"
wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO versus VBA
On Fri, 21 Mar 2008 04:27:20 -0700 (PDT), ytayta555
wrote: 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. -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO versus VBA
On 21 Mar, 17:41, Dick Kusleika wrote:
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO versus VBA
On Fri, 21 Mar 2008 09:00:52 -0700 (PDT), ytayta555
wrote: 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. -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO versus VBA
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 "ytayta555" wrote in message ... On 21 Mar, 17:41, Dick Kusleika wrote: 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO versus VBA
SQL isn't a worksheet editor, either, but SQL + VBA might have the same
chance of working as ADO + VBA. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "ytayta555" wrote in message ... On 21 Mar, 16:09, "Jon Peltier" wrote: 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO versus VBA
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 _______ "ytayta555" wrote in message ... On 21 Mar, 17:41, Dick Kusleika wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
$ versus None | Excel Discussion (Misc queries) | |||
Excel: PC versus Mac | Excel Discussion (Misc queries) | |||
R1C1 versus A1 | New Users to Excel | |||
XLA versus Reference versus Nothing | Excel Programming | |||
vb versus cf | Excel Programming |