Reply to: VBA and Excel

[BoredBloke]

I have recommended SharePoint, as it is quite easy to do most of what I want using the workflows built in.

From what I have been told, Sharepoint is good for simple stuff, but you can't build multi field primary keys, making it a bit rubbish for many tasks. Could be wrong though as I've not really played with it

[BoredBloke]

I used Access many years ago to keep software licence details for a fleet of systems.

When I tried to open the database a few years later, Access claimed it couldn't read that version.

I was not impressed. At all.

Even if I hadn't come across that little gem, the new PC that arrived a little later didn't have Access.

Fortunately I still had the scripts I'd written to grab that data again, and shove it into a proper database (Oracle).

Never come accross that one. I know when moving from 2 to v97 it would ask if you wanted to convert the data - but it would still open and use it. It's hardly the fault of the application if the file has been corrupted. As I said earlier, Access is not part of office std - but neither in Oracle! Also people who can work with access tend to be cheaper than 'proper' DBA's

[Netraider]

A spreadsheet. Sheesh.

Wrong tools.

I have recommended SharePoint, as it is quite easy to do most of what I want using the workflows built in.

[TheFaQQer]

A spreadsheet. Sheesh.

Wrong tools.

Everyone should be using Oracle instead.

LarryE

[MarillionFan]

A spreadsheet. Sheesh.

Wrong tools.

[Sysman]

Ofthe the reason is down to Access being included in Office Pro rather than Office Std and so does not tend to get deployed as often. Also there tends to be fewer people kicking about who can make a database than can throw together a spreadsheet. I've worked at loads of places where they have struggled to implement database functionality into a spreadsheet but simply will not accept a database! On the plus side it gives ample opportunity to keep on generating invoices! Grin and bank it!

it is possible to do and not difficult. I also would side with the Access solution as things can become a mess in Excel very easily!

I used Access many years ago to keep software licence details for a fleet of systems.

When I tried to open the database a few years later, Access claimed it couldn't read that version.

I was not impressed. At all.

Even if I hadn't come across that little gem, the new PC that arrived a little later didn't have Access.

Fortunately I still had the scripts I'd written to grab that data again, and shove it into a proper database (Oracle).

[BoredBloke]

At cargill their traders Daily PnL sheets were all based on Lotus 123 sheets. I found comments in the code that dated back 10 years and one where a chunk of VBA was to 'replace a 123 function that Excel doesn't have'

[eek]

There is a large section of GE which prior to be bought ran on a set of interlinked Lotus 123 spreadsheets.

I remember my colleagues having serious fun trying to audit the location of assets.

[BoredBloke]

Why do people insist on using the wrong tools for the job?


Spreadsheets are great and certainly versatile. However in this situation, an access database would be far better. In there you can do everything you want, lay out forms for each record, make a switchboard so editing and adding equipment is very easy, etc etc.

Access is a far better tool for this job and part of the MS office suite.

Ofthe the reason is down to Access being included in Office Pro rather than Office Std and so does not tend to get deployed as often. Also there tends to be fewer people kicking about who can make a database than can throw together a spreadsheet. I've worked at loads of places where they have struggled to implement database functionality into a spreadsheet but simply will not accept a database! On the plus side it gives ample opportunity to keep on generating invoices! Grin and bank it!

it is possible to do and not difficult. I also would side with the Access solution as things can become a mess in Excel very easily!

[ChrisPackit]

Yes assuming outlook is sat on the machine and you don't mind the email coming from the person updating the spreadsheet.

You can also use CDO instead of the MAPI object model & Outlook client. It's much more flexible than Extended MAPI and you can set the name of the Sender which doesn't have to be the person who's actually logged on. Can be a pain to set up though getting the schemas right.

[ChrisPackit]

Why do people insist on using the wrong tools for the job?

Well said! Drives me mad...

[wim121]

An organisation I give some general IT advice to has asked me a quite a complex question (complex to me)...

They have a spreadsheet that lists equipment they issue to clients. Each item of equipment has its own line in the spreadsheet. What they would like to happen is the following.

Why do people insist on using the wrong tools for the job?


Spreadsheets are great and certainly versatile. However in this situation, an access database would be far better. In there you can do everything you want, lay out forms for each record, make a switchboard so editing and adding equipment is very easy, etc etc.

Access is a far better tool for this job and part of the MS office suite.

[eek]

That is what I was hoping would happen... Is this something quite simple, or am I looking at squeezing big bucks out of this organisation?

A google search or two will give you most of the core code you need.

You still need to modify it, test it, deploy it and then test it again in situ. so its probably 3 days work especially as everything in office is version dependant.

[doodab]

It can certainly be done. You'll need to catch an event that will be generated when the cell in question is changed, munge the cells you need into some strings and invoke outlook to send the mail, which should be easy enough, I've used excel to drive other applications and called it "test automation" before.

Running VBA Code When Events Occur in Excel 2010 will get you started with events.

[Netraider]

Yes assuming outlook is sat on the machine and you don't mind the email coming from the person updating the spreadsheet.

That is what I was hoping would happen... Is this something quite simple, or am I looking at squeezing big bucks out of this organisation?