Excel to Excel using ADO

[RaunLGoode]

I am usually pretty good at figuring things out by googling the problem, but I ahve tried for days to understand moving data back and forth between Excel files using ADO.

Lets sat I have a source file G:\Source _folder\sourcefile.xls whith a range of 4 colums x 20 rows named"SourceTable"
I also have a destination file G:\Destination _folder\Destinationfile.xls whith a range of 4 colums x 200 rows named"DestinationTable"

I want to transfer the informatiion in the SourceTable to the DestinationTable
Appending it to the end of DestinationTable

To help me understand how to use ADO could some kind soul give me a boiler pate example of how to do this ?

Just to complete the cycle, could someone show me how I would pull iinformation from the DestinationTable and append my SourceTable ?

This has been bugging me for days. most examples I have found are Excel to Acces or Access to Excel. which just complicates my de-bugging process..
Thanks in advance

 

[boblarson]

There is no reason I can think of to use ADO to do something which using COM can't do. COM is much simpler and straightforward and ADO is an un-natural way of doing this.

What is it you are really trying to do? For example, "I want to move data from Sheet3 in Workbook X to Sheet 4 in Workbook Y."

The Excel coding to do this is really straightforward and, in fact, you could get it by just recording a macro when doing it manually and then modifying it to suit your needs.

 

[boblarson]

But I just found this here which might give you what you want.

 

[RaunLGoode]

Thanks for the prompt reply.

While the scenario I gave is scaled down, I am really trying to move 20 col x 200 rows at a time. To make things worse, there are 12-15 people all trying to write to the "destinationtable" pretty much all at the same time.

I also have a manager that is convinced ADO is the only / proper solution.

Just to be sure, that is COM?

I have looked at resources like the link you provided, and many others. I even tried to buy a book on ADO to try to figure out what I am missing. ( it's on order)

 

[boblarson]

Well, I don't think you are going to be able to write to an Excel spreadsheet more than one person at a time. It just is not something that is possible. It is locked for the duration of one's updates and connections.

So, your manager will need to get a clue that the destination needs to be a DATABASE and not an Excel spreadsheet. And even writing to that from multiple people is going to be dicey unless you figure out how to keep them from colliding if they are dealing in the same data.

 

[RaunLGoode]

I agree a database would be a better fit. The thinking behind the ADO is that if the 12 users complie their data into one big table, then have the program check to see if the destination file is available before executing the ADO / SQL statement colllisions would be unlikely. also that the ADO is the quickest way to move the data. Again I am not sure what you mean by COM?

 

[boblarson]

COM is Common Object Model and that is the things like:

Dim appXL As Object

Set appXL = CreateObject ("Excel.Application")

etc.