Is this possible ?

[TheBionicOrange]

Is it possible to grab data from an external db, dump it in Excel, modify it, then throw it back ?

I know its easy enough to read from a db and populate a sheet, but I'm not sure if "throwing it back at the db" is possible or not ?!

[si_the_geek]

"Throwing it back" is much more awkward than CopyFromRecordset, but is certainly possible.

What you need to do is read the data from the sheet, and add the records to the database.. one way (and possibly the best) is to do one row of data at a time, and use an Insert or Update SQL statement - but you need to know which rows have changed, and which are new.

[TheBionicOrange]

OK thanks Si. you should see the SIZE of the s/sheet I am going to have to work with !!!
Why is it no matter WHAT software you give people, they ALWAYS end up exporting it to Excel and working with it there !
Now I have to come up with a SQL database back end and an Excel front end to handle the volumes and changes, and also to try and resolve all the file locking issues they have !

[si_the_geek]

They do it because you let them.

We had that going on for a while, so I explained to the users how much damage they could do to the data (as it wasn't possible to track their changes accurately), and created a 'nicer' interface for them in VB.

From then on I only allowed Excel for new data (with checks for validity & no duplication), and only from "template" files I gave them.


As you seem to be comfortable with Excel (or restricted to?), I'm sure you can work out a solution for it!

[TheBionicOrange]

Unfortunately I don't have the clout to tell these people to move away from Excel.
The best I think I can do is give them VBA forms for their data input, so at least I can validate things. This is going to be a great big mess ... I can see it already and I haven't even started it yet !

[Al42]

Unfortunately I don't have the clout to tell these people to move away from Excel.

Do you have the "clout" to keep a record of how much of your time is being wasted by inconsistent data, and not being paid for by the customer, and present it to your boss?

[heinz1218]

Do you have the "clout" to keep a record of how much of your time is being wasted by inconsistent data, and not being paid for by the customer, and present it to your boss?

goooood call

[Al42]

Nah, I just have to do it about 3 times a week.

[Bruce Fox]

Is it possible to grab data from an external db, dump it in Excel, modify it, then throw it back.

Here you go: http://www.vbforums.com/showthread.p...ighlight=excel This example uses FULL automation, adapt to suit

Excerpt from my post:

I needed a way to automatically import a user selected Excel spreadsheet into a new (dynamic) Access table.

This was what I came up with. It is fast, very fast, compared to other methods of looping past each Excel cell etc.

It also creates and formats the new Table dynamically – no matter how many columns exist in the Excel sheet.

[TheBionicOrange]

Thanks a lot Bruce. Thats going to help me out no end

[Bruce Fox]

Hi TheBionicOrange,

You could play with it a bit to try and customise the Tables Fields Data Type as the are created as 'varchar' in this example. I did try, but became too messy.

You could possibly use a pre-defined Table to load the Excel data into aswell if required.

[TheBionicOrange]

Well I have a pretty large project to do. What I'll do at the end of it is maybe put a post up here with the attached code, and explain exactly how it all panned out.