Replies

Hi Shirley

You could try setting the Word Tools/Options/FileLocations/Autorecover to the new Temp directory.

The location for most temp files is determined by the environment variables Temp= and Tmp=. Environment variable are held in memory and only exist if something sets them. The something is almost almost always either a .Bat file or the NT equivalent a .Cmd file. These files can be edited with Notepad or anything else.

To see the environment variables you open the DOS box. (Start/Programs/CommandPrompt) and type in “SET” and press enter.

To set an environment variable you type (in the DOS box) SET VariableName=VariableValue. For example SET Temp=c:Temp.
Then press enter.

The line
SET Temp=c:Temp can be in a .bat or .cmd file.

The environment holds the latest value assigned to each variable.

The variable are held in memory and disappear when the computer is shut down or switched off.

Hope this grounding in DOS environment variable is useful to you in identifying your problem and may be useful to others.

Hi Phil

The /Q is for quiet. If you don’t have it you get a DOS window which will ask you if you really want to delete all the files in the temp directory. You have to type “Y” and Enter.

You don’t really want to see a DOSwindow do you?

A quite different approach would be to make the source document read only (using its attributes). Then you open it as normal, but there is no problem with a number of users opening it at the same time.

If you do not have a login script you will neet to put the lines in the Autoexec.bat file on each workstation.

The help file says specifically you can’t do it. However it might be possible to create code which would do it. not in the macro but perhaps at print time using the BeforePrint event.

One workaround is to make all the blank cells on the source spreadsheet contain a space. Then they will link correctly.

However this may have consequences for formulae which reference the “blank” cells.

Hi Sherry

You are quite right. Your sample file behaves decidedly oddly. It does some macro type things but there are no visible macros.

Is it possible you added comments in Excel, then opened the file in lotus and resaved it, then went back to Excel?

I am not going to write the VBA for you but I can give a few pointers.

I would suggest you set the first 12 cells in a column to the right of the list to equal the corresponding list number. Put a total of this column at the bottom and test if it = the target.

Then you need to work out a process for stepping through the list moving the spaces up into the list. Clearly the first step will be to make the 12th cell blank and the 13th cell = its corresponding figure. Then you make the 11 cell blank and make the 12 cell = its corresponding figure.

You would probably have a series of nested loops to do this but if you were very clever and wanted to minimise the size of the code you would use a recursive sub.

Back in the old days we used to do things like this for fun – see how much you can do with how little programming!

I don’t think you can do this. One workaround is to create all the controls you might want and make them invisible. I did this to make a gantt chart. I had a stack of invisible boxes I could move to wherever I wanted them by code.

You need to set up a directory C:temp on each PC. Then you need to set 2 environment variables in your login script by including the lines

Set TEMP=C:TEMP
Set TMP=C:TEMP

The temporary files will then go where they do no harm.

You may want to include the fiollowing line in your login as well

Del C:TEMp*.* /Q

This will clear out the directory each day.

Don’t bother! Right click and select Edit comment.

Could it be the second workgroup have their systems set to disable macros?

You may need to use GetSetting and SaveSetting in a macro. You would need to set a macro to run when a new sheet based on the template was created which would get the last invoice number from the registry, increment it, and set the value of the relevant cell to that value. It would then save the new value back in the registry. This assumes only one user is using the spreadsheet, otherwise they will all be using different invoice numbers.

I am not aware of any command for putting a value back in the template although it might be possible to do that.

Another possibility would be to write the number out to a file and read it back in.

I click where I want to start the selection, use the scroll bar until I can see the end point and then Shift-Click

I overcame this once by using SEQ (Insert/Field/Numbering/SEQ) This field has a reset switch.