Read Only Spreadsheet / Save Copy As Option

[ksp]

I have setup up several templates for some users that are used to import
data from a third party software application. As the users only have
read only access to the directory where the templates are saved, they
can only save a copy of the file. This in itself is what I want as I do
not want them corrupting the templates, however when they "save copy as"
the file that they then have open remains as the Bookxx spreadsheet
rather than the new file they have just saved/created.

Does anyone know a way around this as I am concernd that the users will
not realise and may loose changes that they make to the file after they
have done the "save copy as"

Thanks

Karen

 

[Dave Peterson]

This is not usual behavior. (I've never seen it.)

Are you sure that it's happening?

If you double check and you're sure, you may want to post the version of excel
that you're using and how the users actually do this process.

Do they open a *.xlt file (a real template) or do they create a new workbook
based on that template (file|new)?

Or do they open a .xls file that you use as a template?

 

[ksp]

Hi Dave

Well at least I know its not normal, so hopefully there is a solution
out there. The users are exporting data from Lotus Notes (V6.5) using a
Notes script that dumps some data into excel (we have Excel 2003). As
the users do this a dialog box appears in Notes asking them to browse
the network to select an excel file. I have set up xlt files (but have
also tested with xls files) on a directory that is read only for the
general population - however this appears to be a red herring as I have
just tested this again with my access (and I have full read-write access
to this directory) and I have the same problem. The spreadsheet has some
macro's that run in the back ground when the user selects the template
but they only do formatting changes. If they then go to save the file
by going File -> Save from the menu the only option available is "Save
Copy As". The file does save a copy but the file name that is still
open on the PC is not the file name that was just saved.

Any help anyone can give would be appreciated

Thanks

KP

 

[Dave Peterson]

Just a test.

After you do the save, put this in an empty cell:

=cell("filename",a1)

What do you see?

Since your workbook has macros, maybe it's a problem with a macro that changes
the caption on the title bar and doesn't change it correctly.

 

[ksp]

The result from that formula is just a blank cell, which I gatehr means
that the spreadsheet thinks it has not been saved.

Any other suggestions?

Thanks

 

[Dave Peterson]

It sounds to me like the file isn't being saved.

You can check under File|properties|general tab.

But I don't have another guess.

Sorry.

 

[ksp]

The book... version of the file is definitely not being save but it does
at least create a copy for you. I am begining to think it is more
related to how Lotus Notes Opens Excel rather than an excel problem as
such. So I'm now going to try some Notes forums

Thanks for your help

Much Appreciated

KP

 

[Beege]

ksp,

First, where are they trying to save the file? Are there rights issues
there?
Second, if they are opening File/New/from Template, they shouldn't need to
File/SaveCopyAs. They should be able to just "save" and the template should
still be protected.
I think "save copy as" saves the work done so far to a file to another file,
and leaves the workbook currently open yet unnamed.

Beege

 

[Dave Peterson]

If you create a new workbook based on a template (not just editing the template
itself), then the first time you do File|save, you'll see the File|SaveAs
dialog. Excel is pretty smart and knows that the workbook hasn't been saved.

And File|SaveAs should leave the user editing the workbook under its new name.

There is a VBA command .savecopyas that will save the workbook as a different
name and the user will still see the original name. But this function/feature
isn't available to the user through the user interface.

 

[Dave Peterson]

One more thought...

Are you saving the workbook as a normal excel workbook (*.xls)? If you're
saving as an addin (*.xla), that could be the problem.

I don't use Lotus Notes, but I don't quite seeing it being a Lotus Notes
problem. Once you have the file open in excel, I'd bet Excel doesn't know/care
where it came from.

 

[Beege]

If you create a new workbook based on a template (not just editing the
template
itself), then the first time you do File|save, you'll see the File|SaveAs
dialog. Excel is pretty smart and knows that the workbook hasn't been
saved.

And File|SaveAs should leave the user editing the workbook under its new
name.

There is a VBA command .savecopyas that will save the workbook as a
different
name and the user will still see the original name. But this
function/feature
isn't available to the user through the user interface.

So this File/Save give the default file name "TemplateFileName#" where # is
the number of times a file has been created frm this template during the
current session, yes? So why the worry of corrupting the template file? The
OP should be using the Save command under the File menu if I understand
correctly. "Save Copy as" seems to be behaving just as designed, no?

Beege

 

[Dave Peterson]

That is my guess, too.

But the OP is having different results.

 

[ksp]

Hi Guys

Thanks for your posts.

I have been chatting to a Notes programmer that I know and what is
happening is that Notes is opening the excel file as though it is an
OLE, excel is then treating it as such, hence the Save Copy As and
Update functionality, however it is not an OLE. Apparently this is a
known Notes issue for which there is no Notes solution.

However, a bit of lateral thinking, and stepping away from the issue
over the weekend and I have thought of a work around. If I copy the
data to a new workbook, excel then just behaves as normal. I wanted to
build this into the macro to happen automatically however where I am
getting stuck is that the last thing I need the macro to do is close
the excel file that has been created ie that it is running in. Since
the file name can vary from Book1 thru to Bookx is there a command that
I can use to close the spreadsheet that the macro is running in without
a filename? (Hope that made some sense)

Thanks

Karen

 

[Dave Peterson]

You can refer to the workbook holding the code as ThisWorkbook.

So maybe something like this would work:

Dim Wks as worksheet
set wks = workbooks.add(1).worksheets(1)

thisworkbook.worksheets(1).cells.copy _
destination:=wks.range("a1")

But remember that the last thing you'll want to do is to close that workbook
with the macro. As soon as you close the workbook, the macro will stop.

You can use a line like this:

Thisworkbook.close savechanges:=false

 

[ksp]

Hi Dave

Thanks for you help on this I have managed to get the copy/paste and
then close the ThisWorkbook all working

Thanks heaps

Karen

 

[Dave Peterson]

Glad you got it working.

Hi Dave

Thanks for you help on this I have managed to get the copy/paste and
then close the ThisWorkbook all working

Thanks heaps

Karen