"The file Data.csv is already open. You cannot open two documents with 
the same name, even if the documents are in different folders. To open 
the second document, either close the one that's already open, or rename 
one of the documents."

Who knew Excel couldn't do this?

For that matter, who has any idea *why* it can't do this?! o_O

Post a reply to this message

> For that matter, who has any idea *why* it can't do this?! o_O

Because internally it uses some reference based on the filename of open 
documents?  Or it would get confused if you tried to reference another 
open document and there were two with the same filename?

Post a reply to this message

scott wrote:
>> For that matter, who has any idea *why* it can't do this?! o_O
> 
> Because internally it uses some reference based on the filename of open 
> documents?  Or it would get confused if you tried to reference another 
> open document and there were two with the same filename?

Or the UI didn't have a place to put the path name of the file in your face, 
and the Excel team decided it was easier to disallow that than to fix the UI 
to find a place to put the path?

-- 
Darren New, San Diego CA, USA (PST)
  "How did he die?"   "He got shot in the hand."
     "That was fatal?"
          "He was holding a live grenade at the time."

Post a reply to this message

Le 18/02/2011 15:18, Invisible a écrit :
> "The file Data.csv is already open. You cannot open two documents with
> the same name, even if the documents are in different folders. To open
> the second document, either close the one that's already open, or rename
> one of the documents."
> 
> Who knew Excel couldn't do this?

I did. And it's not limited to csv file: all files in excel must have a
unique name, and the path is irrelevant.

> 
> For that matter, who has any idea *why* it can't do this?! o_O

It might be due to its long history... and the display of filename in
the window submenu. (it might be for the "benefit" of the end-users, so
as to not confuse them.)

Excel is a sandbox: it make nice graphics (yet not all of them keep
being available across its versions) but is usually overused for database.
It's fine to use excel as a sketch, but any real production should stay
away from it. Alas, it's so eye-candy that it usually stays with the
uneducated people in charge (like CEO and other administrative/marketing
people).

Post a reply to this message

On 21/02/2011 08:19 AM, Le_Forgeron wrote:

> Excel is a sandbox: it make nice graphics (yet not all of them keep
> being available across its versions) but is usually overused for database.
> It's fine to use excel as a sketch, but any real production should stay
> away from it. Alas, it's so eye-candy that it usually stays with the
> uneducated people in charge (like CEO and other administrative/marketing
> people).

It's true enough that my company's IT department keeps *all* of their 
records in Excel spreadsheets. License keys, user account records, 
everything like that... all spreadsheets. Never mind that only one 
person can work on it at a time and that people can screw up each 
other's updates and all the rest of it. How hard would it be to put this 
stuff into a trivial Access database? Not very. But apparently our IT 
department is too stupid to understand why this would be a good idea.

For my part, the entire reason that I'm using Excel is that I *want* 
graphs. If it were somehow possible to make gnuplot draw graphs from CSV 
data, I'd use that. (It's scriptable, after all.) But since it isn't, 
I'm stuck with Excel.

Post a reply to this message

Le 21/02/2011 10:13, Invisible a écrit :

> For my part, the entire reason that I'm using Excel is that I *want*
> graphs. If it were somehow possible to make gnuplot draw graphs from CSV
> data, I'd use that. (It's scriptable, after all.) But since it isn't,
> I'm stuck with Excel.

Your friend on unix would be ploticus.
The script can read a separated data file in csv...

scripting in ploticus has a strange learning curve, best to copy & paste
from their gallery then play a bit.

For the lazy and/or always overbooked people:
http://ploticus.sourceforge.net/doc/welcome.html


-- 
Software is like dirt - it costs time and money to change it and move it
around.

Just because you can't see it, it doesn't weigh anything,
and you can't drill a hole in it and stick a rivet into it doesn't mean
it's free.

Post a reply to this message

>> It's fine to use excel as a sketch, but any real production should stay
>> away from it. Alas, it's so eye-candy that it usually stays with the
>> uneducated people in charge (like CEO and other administrative/marketing
>> people).
>
> It's true enough that my company's IT department keeps *all* of their
> records in Excel spreadsheets. License keys, user account records,
> everything like that... all spreadsheets. Never mind that only one
> person can work on it at a time and that people can screw up each
> other's updates and all the rest of it.

Excel is good for simple databases with infrequent updates for many 
reasons, the most important is that almost everyone has it installed on 
their machine and most people are familiar with it.  If you store the 
Excel file on a network drive it will get backed up regularly and ensure 
that only one person can edit it at a time.

There are also more advanced features to lock ranges of cell based on 
who is logged in if you want to limit certain parts of the database.  We 
use this on a couple of files where managers have to authorise certain 
items (it prevents non-managers from changing those cells).

> How hard would it be to put this
> stuff into a trivial Access database? Not very.

If you have no experience with Access it's not trivial to set it up 
properly, and if the only perceived benefit is that more than person can 
edit it at a time it's unlikely to be seen as worthwhile.

> But apparently our IT
> department is too stupid to understand why this would be a good idea.

Probably they just have other stuff to do they think is more urgent.

Post a reply to this message

>> It's true enough that my company's IT department keeps *all* of their
>> records in Excel spreadsheets.
>
> Excel is good for simple databases with infrequent updates for many
> reasons.

It's like the fact that all screenshots are always emailed as Word 
documents, even though it takes up far more space. It's just that nobody 
knows any better.

> There are also more advanced features to lock ranges of cell based on
> who is logged in if you want to limit certain parts of the database.

We don't use that feature at all.

(Unrelated, but... I was astonished when I discovered how hard Access 
security sucks. Excel is much better at this.)

>> How hard would it be to put this
>> stuff into a trivial Access database? Not very.
>
> If you have no experience with Access it's not trivial to set it up
> properly,

True. But we're talking about the IT department here. If we don't know 
how to work it, what hope is there?

> and if the only perceived benefit is that more than person can
> edit it at a time it's unlikely to be seen as worthwhile.

The other benefit of course is that you can trivially sort the data 
without screwing it up. (Excel makes it very easy to accidentally sort 
one column only, muddling up all the data.) It also makes inserting or 
deleting records vastly easier. (The same goes for adding or removing 
columns.) Don't even get me started on how difficult it is to 
cross-reference data in a spreadsheet. (Although admittedly it's 
unlikely you need to do that very often.)

>> But apparently our IT
>> department is too stupid to understand why this would be a good idea.
>
> Probably they just have other stuff to do they think is more urgent.

This is from the department that has set most of our admin passwords to 
"our1Tr0x!"...

Post a reply to this message

> The other benefit of course is that you can trivially sort the data
> without screwing it up. (Excel makes it very easy to accidentally sort
> one column only, muddling up all the data.)

Err, you did turn on filtering first on your table (select the header 
cells and click Data->Filter)?  This makes sorting and filtering a 
no-brainer and AFAIK prevents people from sorting the data incorrectly.

> Don't even get me started on how difficult it is to
> cross-reference data in a spreadsheet.

If you need to cross-reference beyond simple drop-down selection lists 
then you're probably beyond what Excel can easily offer as a simple 
database.

Post a reply to this message

On 22/02/2011 11:45 AM, scott wrote:
>> The other benefit of course is that you can trivially sort the data
>> without screwing it up. (Excel makes it very easy to accidentally sort
>> one column only, muddling up all the data.)
>
> Err, you did turn on filtering first on your table (select the header
> cells and click Data->Filter)? This makes sorting and filtering a
> no-brainer and AFAIK prevents people from sorting the data incorrectly.

Really? I had literally no idea that was possible. In 15 years of using 
Excel, I didn't know about this. (I wonder how many other people didn't.)

Rather than clicking on an unintuitive command name buried in a menu, if 
you click on the big "sort ascending" button right there on the toolbar, 
it can *totally* mess up your data. Fortunately there's an undo button. 
(I wonder how many people know that?)

>> Don't even get me started on how difficult it is to
>> cross-reference data in a spreadsheet.
>
> If you need to cross-reference beyond simple drop-down selection lists
> then you're probably beyond what Excel can easily offer as a simple
> database.

I meant stuff like if you have one spreadsheet with all the license 
keys, and another spreadsheet that tells you when each product was 
purchased, you're going to have fun trying to link one to the other. 
With a real database this would be a trivial table join.

(On the other hand, databases generally *need* to do joins more often 
because the data is usually normalised - something people don't bother 
with for Excel.)

Post a reply to this message

> Really? I had literally no idea that was possible. In 15 years of using
> Excel, I didn't know about this. (I wonder how many other people didn't.)

Odd, I've seen this used on pretty much every "database" style Excel 
workbook from several different companies.  Particularly for "action 
list" type databases where it's essential to be able to easily filter 
out certain rows.

> I meant stuff like if you have one spreadsheet with all the license
> keys, and another spreadsheet that tells you when each product was
> purchased, you're going to have fun trying to link one to the other.
> With a real database this would be a trivial table join.

Yes, it's quite tricky to get two tables linked in the same way a proper 
DB would do it whilst keeping the GUI friendly, but usually there is an 
easy enough hack using the lookup and data validation stuff that works.

Post a reply to this message

On 22/02/2011 02:51 PM, scott wrote:

> Yes, it's quite tricky to get two tables linked in the same way a proper
> DB would do it whilst keeping the GUI friendly, but usually there is an
> easy enough hack using the lookup and data validation stuff that works.

I guess fundamentally it just irks me that supposed IT "experts" are 
using the wrong tool for the job. I don't suppose an Access database 
would actually be any faster to access over an encrypted WAN link, 
that's for sure...

Post a reply to this message

scott wrote:
> Excel is good for simple databases with infrequent updates for many 
> reasons, the most important is that almost everyone has it installed on 
> their machine and most people are familiar with it.

I read a while ago that while LOTOS 1-2-3 and visicalc were putting in 
ever-more sophisticated math stuff, MS went and looked at how people were 
actually using the product and realized some surprisingly large number of 
spreadsheets had no formulas at all. So MS made it easier to keep lists of 
stuff in Excel, and that's part of why it won out over those others.

> that only one person can edit it at a time.

Actually, I think nowadays you can set it up for multiple edits and it'll 
coordinate. It seemed simple, but I never had two people actually try at 
once, so take it with a grain.

>> How hard would it be to put this
>> stuff into a trivial Access database? Not very.
> 
> If you have no experience with Access it's not trivial to set it up 
> properly, and if the only perceived benefit is that more than person can 
> edit it at a time it's unlikely to be seen as worthwhile.

Last I looked, Access was only one user at a time making changes too. The 
one guy would lean back in his chair and bellow "IS THERE ANYONE IN THE 
DATABASE?"  That was their locking protocol.

-- 
Darren New, San Diego CA, USA (PST)
  "How did he die?"   "He got shot in the hand."
     "That was fatal?"
          "He was holding a live grenade at the time."

Post a reply to this message

On 22/02/2011 04:49 PM, Darren New wrote:

> I read a while ago that while LOTOS 1-2-3 and visicalc were putting in
> ever-more sophisticated math stuff, MS went and looked at how people
> were actually using the product and realized some surprisingly large
> number of spreadsheets had no formulas at all. So MS made it easier to
> keep lists of stuff in Excel

That wouldn't surprise me.

> and that's part of why it won out over those others.

Perhaps. I can well imagine people being too stupid to work out how to 
use a proper database product...

>> that only one person can edit it at a time.
>
> Actually, I think nowadays you can set it up for multiple edits and
> it'll coordinate.

I've never seen that.

> Last I looked, Access was only one user at a time making changes too.
> The one guy would lean back in his chair and bellow "IS THERE ANYONE IN
> THE DATABASE?" That was their locking protocol.

I spent six months watching a group of three people in an office. They 
had an Access database, which everybody had constantly open all day. So 
clearly that's not the case.

On the other hand, I have seen "error - database in use", so I'm not 
sure what's up with that...

Post a reply to this message

Invisible wrote:
> Perhaps. I can well imagine people being too stupid to work out how to 
> use a proper database product...

Remember at the time that a "proper" database product cost roughly $100K per 
seat.

You don't need a "proper" database product to keep your to-do list or your 
org chart.

>>> that only one person can edit it at a time.
>>
>> Actually, I think nowadays you can set it up for multiple edits and
>> it'll coordinate.
> 
> I've never seen that.

It was really trivial in the menus. I think it's something around 
tools->Shared Workbook.  One of the things under Tools should do it.

> I spent six months watching a group of three people in an office. They 
> had an Access database, which everybody had constantly open all day. So 
> clearly that's not the case.

Well, this *was* years ago.  More than a decade, probably.

-- 
Darren New, San Diego CA, USA (PST)
  "How did he die?"   "He got shot in the hand."
     "That was fatal?"
          "He was holding a live grenade at the time."

Post a reply to this message

>> Actually, I think nowadays you can set it up for multiple edits and
>> it'll coordinate.
>
> I've never seen that.

It's under the Review ribbon, then "Share Workbook".  But it only lets 
you change basic stuff when shared (ie just cell contents, not the 
layout, charts, etc), and the changes are only updated when you save the 
file (or I think you can set it to automatically update at regular 
intervals).  If two people change the same cell, the first one to save 
will succeed, the second one will get to choose which value to keep.

>> Last I looked, Access was only one user at a time making changes too.
>> The one guy would lean back in his chair and bellow "IS THERE ANYONE IN
>> THE DATABASE?" That was their locking protocol.
>
> I spent six months watching a group of three people in an office. They
> had an Access database, which everybody had constantly open all day. So
> clearly that's not the case.

I only worked on Access about 8 years ago so it's probably out of date 
info now, but IIRC you could set up on a per-table basis how much of it 
was locked when opened (entire table, single row, nothing).  It also 
took a fair bit of work to get it to play nicely with multiple people 
opening it (ie catching all the errors that might come up, and 
preventing people doing stuff that would screw it up).

Post a reply to this message

On 23/02/2011 08:44 AM, scott wrote:
>>> Actually, I think nowadays you can set it up for multiple edits and
>>> it'll coordinate.
>>
>> I've never seen that.
>
> It's under the Review ribbon, then "Share Workbook".

Ribbon?

As in, Office 2007 or later?

Well, that'll be why I haven't seen it then. ;-)

> But it only lets
> you change basic stuff when shared (ie just cell contents, not the
> layout, charts, etc), and the changes are only updated when you save the
> file (or I think you can set it to automatically update at regular
> intervals). If two people change the same cell, the first one to save
> will succeed, the second one will get to choose which value to keep.

I bet that's fun if you try to add a new row to the bottom. (This would 
of course be a complete non-issue if you used... a database.)

>> I spent six months watching a group of three people in an office. They
>> had an Access database, which everybody had constantly open all day. So
>> clearly that's not the case.
>
> I only worked on Access about 8 years ago so it's probably out of date
> info now, but IIRC you could set up on a per-table basis how much of it
> was locked when opened (entire table, single row, nothing). It also took
> a fair bit of work to get it to play nicely with multiple people opening
> it (ie catching all the errors that might come up, and preventing people
> doing stuff that would screw it up).

It's not something I have a lot of expertise with, but I saw all this 
set up 10 years ago, running under Windows 98. It can't be that hard.

Post a reply to this message

On 22/02/2011 05:11 PM, Darren New wrote:
> You don't need a "proper" database product to keep your to-do list or
> your org chart.

Not really, no. Access may be hopeless as a "real" database, but it's 
pretty much ideal for quickly setting up small databases for 
non-critical stuff.

Now, if only it wasn't so absurdly difficult to secure it...

Post a reply to this message

> I bet that's fun if you try to add a new row to the bottom. (This would
> of course be a complete non-issue if you used... a database.)

Actually it works fine.  If two users both add a row to the bottom in 
the same place it does what you would expect (ie add the 2nd row below 
the 1st new row, and not ask if you want to overwrite it or not).

> It's not something I have a lot of expertise with, but I saw all this
> set up 10 years ago, running under Windows 98. It can't be that hard.

The problem with Access is that it's not included in the cheap Office 
packs.  Everyone here needs to ask IT to get a new license if they need 
Access (which of course costs more).  That is a big reason why people 
use Excel for this kind of stuff.

Post a reply to this message

On 23/02/2011 10:24 AM, scott wrote:
>> I bet that's fun if you try to add a new row to the bottom. (This would
>> of course be a complete non-issue if you used... a database.)
>
> Actually it works fine.

MS really have worked hard on this, eh? ;-)

> The problem with Access is that it's not included in the cheap Office
> packs. Everyone here needs to ask IT to get a new license if they need
> Access (which of course costs more). That is a big reason why people use
> Excel for this kind of stuff.

Oh. Really?

I can't say about other sites, but here in the UK, every employee has 
Word, Excel, Access, Power Point and Outlook installed by default. We 
have volume licenses for all of them.

Which is just as well, since several vital items of operational data 
*are* Access databases. (All things developed in the UK, not by the 
central IT department. I'm sure they would have used Excel instead...)

Post a reply to this message

> I can't say about other sites, but here in the UK, every employee has
> Word, Excel, Access, Power Point and Outlook installed by default. We
> have volume licenses for all of them.

IME not many organisations buy Access for every employee.

Post a reply to this message

On 23/02/2011 10:36 AM, scott wrote:
>> I can't say about other sites, but here in the UK, every employee has
>> Word, Excel, Access, Power Point and Outlook installed by default. We
>> have volume licenses for all of them.
>
> IME not many organisations buy Access for every employee.

OK, fair enough.

Still doesn't explain why *our* IT department uses spreadsheets. ;-)

Post a reply to this message

Invisible wrote:
> Not really, no. Access may be hopeless as a "real" database, but it's 
> pretty much ideal for quickly setting up small databases for 
> non-critical stuff.

It's the microsoft equivalent of sqlite except back from when DBase was 
considered heavy duty for a personal computer. You shouldn't really expect 
it to be "real".

-- 
Darren New, San Diego CA, USA (PST)
  "How did he die?"   "He got shot in the hand."
     "That was fatal?"
          "He was holding a live grenade at the time."

Post a reply to this message

Invisible wrote:
> Well, that'll be why I haven't seen it then. ;-)

Under 2003, it's under Tools, and there's a variety of "share" things to 
play with.  Or you could, you know, look in the help file, now that you know 
Microsoft calls it "share".

-- 
Darren New, San Diego CA, USA (PST)
  "How did he die?"   "He got shot in the hand."
     "That was fatal?"
          "He was holding a live grenade at the time."

Post a reply to this message