Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
File name changes on opening - adds a number to end
I am receiving e mails each week which include a excel 97 spreadsheet. I then
open the spreadsheet and use it within excel. However on occassions the file name changes from say "fredbloggs" to "Fredbloggs1" and this seems to be because there is already a file ( albeit not open ) named "frebloggs" in the temp folder from the last time i used it. Is there a way to stop this number being added to the name or a way of deleting the .xls files in the temp folder automatically when windows starts ? Whatever the solution I need to keep it simple as others will be carrying out this task who have very little pc knowledge. Its working on Windows NT. And just in case it matters the reason i dont want the file name to change is that there is a macro which chooses the spreadsheet by name and this fails if the name is changed. I hope this makes sense, any help would be very much appreciated Darren |
#2
|
|||
|
|||
I think this isn't an excel question--it's more about what your email program
does. Personally, I'd put a VBS script on a network drive and put shortcuts to that VBS script on everyone's desktop. This VBS script would clean the Temp folder (much easier than doing it manually). This is the one I use. It was written by Michael Harris: http://groups.google.com/groups?thre...%40tkmsftngp02 If you're not on a network, you could put the script in a local folder and still put a shortcut to the script on the desktop. (I don't like to put the real file on the desktop--it's too easily deleted.) darrenrwood wrote: I am receiving e mails each week which include a excel 97 spreadsheet. I then open the spreadsheet and use it within excel. However on occassions the file name changes from say "fredbloggs" to "Fredbloggs1" and this seems to be because there is already a file ( albeit not open ) named "frebloggs" in the temp folder from the last time i used it. Is there a way to stop this number being added to the name or a way of deleting the .xls files in the temp folder automatically when windows starts ? Whatever the solution I need to keep it simple as others will be carrying out this task who have very little pc knowledge. Its working on Windows NT. And just in case it matters the reason i dont want the file name to change is that there is a macro which chooses the spreadsheet by name and this fails if the name is changed. I hope this makes sense, any help would be very much appreciated Darren -- Dave Peterson |
#3
|
|||
|
|||
You could even get the script to run automatically by making it a Scheduled
Task "Dave Peterson" wrote: I think this isn't an excel question--it's more about what your email program does. Personally, I'd put a VBS script on a network drive and put shortcuts to that VBS script on everyone's desktop. This VBS script would clean the Temp folder (much easier than doing it manually). This is the one I use. It was written by Michael Harris: http://groups.google.com/groups?thre...%40tkmsftngp02 If you're not on a network, you could put the script in a local folder and still put a shortcut to the script on the desktop. (I don't like to put the real file on the desktop--it's too easily deleted.) darrenrwood wrote: I am receiving e mails each week which include a excel 97 spreadsheet. I then open the spreadsheet and use it within excel. However on occassions the file name changes from say "fredbloggs" to "Fredbloggs1" and this seems to be because there is already a file ( albeit not open ) named "frebloggs" in the temp folder from the last time i used it. Is there a way to stop this number being added to the name or a way of deleting the .xls files in the temp folder automatically when windows starts ? Whatever the solution I need to keep it simple as others will be carrying out this task who have very little pc knowledge. Its working on Windows NT. And just in case it matters the reason i dont want the file name to change is that there is a macro which chooses the spreadsheet by name and this fails if the name is changed. I hope this makes sense, any help would be very much appreciated Darren -- Dave Peterson |
#4
|
|||
|
|||
Or just add the shortcut to the windows start|Programs|startup group.
bigwheel wrote: You could even get the script to run automatically by making it a Scheduled Task "Dave Peterson" wrote: I think this isn't an excel question--it's more about what your email program does. Personally, I'd put a VBS script on a network drive and put shortcuts to that VBS script on everyone's desktop. This VBS script would clean the Temp folder (much easier than doing it manually). This is the one I use. It was written by Michael Harris: http://groups.google.com/groups?thre...%40tkmsftngp02 If you're not on a network, you could put the script in a local folder and still put a shortcut to the script on the desktop. (I don't like to put the real file on the desktop--it's too easily deleted.) darrenrwood wrote: I am receiving e mails each week which include a excel 97 spreadsheet. I then open the spreadsheet and use it within excel. However on occassions the file name changes from say "fredbloggs" to "Fredbloggs1" and this seems to be because there is already a file ( albeit not open ) named "frebloggs" in the temp folder from the last time i used it. Is there a way to stop this number being added to the name or a way of deleting the .xls files in the temp folder automatically when windows starts ? Whatever the solution I need to keep it simple as others will be carrying out this task who have very little pc knowledge. Its working on Windows NT. And just in case it matters the reason i dont want the file name to change is that there is a macro which chooses the spreadsheet by name and this fails if the name is changed. I hope this makes sense, any help would be very much appreciated Darren -- Dave Peterson -- Dave Peterson |
#5
|
|||
|
|||
I have typed the vbs you refered to into wordpad and saved it to my desktop
for now. I am not on a network. I double clicked on the icon and get the error " There is no file extension in "c:\winnt\profiles\wooddk\desltop\*"" I am assuming as i have saved it straight into desktop that this error message is not refering to the actual opening of the script but something the script is trying to do? Do i need to amend the script to refer to where my tem folder is ? As far as i can see it is just in c drive . I am not clued up enough to know what may be causing the error Thanks "Dave Peterson" wrote: I think this isn't an excel question--it's more about what your email program does. Personally, I'd put a VBS script on a network drive and put shortcuts to that VBS script on everyone's desktop. This VBS script would clean the Temp folder (much easier than doing it manually). This is the one I use. It was written by Michael Harris: http://groups.google.com/groups?thre...%40tkmsftngp02 If you're not on a network, you could put the script in a local folder and still put a shortcut to the script on the desktop. (I don't like to put the real file on the desktop--it's too easily deleted.) darrenrwood wrote: I am receiving e mails each week which include a excel 97 spreadsheet. I then open the spreadsheet and use it within excel. However on occassions the file name changes from say "fredbloggs" to "Fredbloggs1" and this seems to be because there is already a file ( albeit not open ) named "frebloggs" in the temp folder from the last time i used it. Is there a way to stop this number being added to the name or a way of deleting the .xls files in the temp folder automatically when windows starts ? Whatever the solution I need to keep it simple as others will be carrying out this task who have very little pc knowledge. Its working on Windows NT. And just in case it matters the reason i dont want the file name to change is that there is a macro which chooses the spreadsheet by name and this fails if the name is changed. I hope this makes sense, any help would be very much appreciated Darren -- Dave Peterson |
#6
|
|||
|
|||
Sometimes google inserts extra characters.
Here's a copy of the script that I use: '===DeleteTempFiles.vbs=== 'http://groups.google.com/groups?threadm=%23bXVsIHnAHA.920%40tkmsftngp02 Const TemporaryFolder = 2 'for GetSpecialFolder const strTitle = "DeleteTempFiles.vbs" set fso = createobject("scripting.filesystemobject") 'init an empty array (ubound will be -1)... ' 'we use an array and store the file objects. 'this avoids any problems with altering the 'contents of the Files collections while they 'are being iterated. ' arFiles = array() count = -1 'get the path to the temp folder ' tempdir = fso.GetSpecialFolder(TemporaryFolder) 'load the (global scope) arFiles 'SelectFiles calls itself recursively 'for SubFolders ' SelectFiles tempdir msgbox count+1 & " files found",0,strtitle 'now do the actual deletes. the error trap 'is in case any are in-use... ' dcount = 0 for each file in arFiles on error resume next file.delete true if err.number = 0 then dcount = dcount + 1 err.clear on error goto 0 next 'now go back and delete empty folders 'below the temp folder DeleteEmptyFolders tempdir,false 'comment out for "silent" operation, 'or add support for a "/s" command-line switch. ' msgbox count+1 & " files found, " & dcount & " deleted.", 0,strtitle sub SelectFiles(sPath) 'select files to delete and add to array... ' set folder = fso.getfolder(sPath) set files = folder.files for each file in files count = count + 1 redim preserve arFiles(count) set arFiles(count) = file next for each fldr in folder.subfolders SelectFiles fldr.path next end sub sub DeleteEmptyFolders(sPath,bDeleteThisFolder) set folder = fso.getfolder(sPath) 'recurse first... ' for each fldr in folder.subfolders DeleteEmptyFolders fldr.path,true next 'if no files or folders then delete... ' 'bDeleteThisFolder is false for 'the root of the subtree, and true for 'sub-folders (unless you want to delete 'the entire subtree if it is empty). ' if (folder.files.count = 0) and _ (folder.subfolders.count) = 0 and _ bDeleteThisFolder then folder.delete exit sub end if end sub ' '===end-script=== darrenrwood wrote: I have typed the vbs you refered to into wordpad and saved it to my desktop for now. I am not on a network. I double clicked on the icon and get the error " There is no file extension in "c:\winnt\profiles\wooddk\desltop\*"" I am assuming as i have saved it straight into desktop that this error message is not refering to the actual opening of the script but something the script is trying to do? Do i need to amend the script to refer to where my tem folder is ? As far as i can see it is just in c drive . I am not clued up enough to know what may be causing the error Thanks "Dave Peterson" wrote: I think this isn't an excel question--it's more about what your email program does. Personally, I'd put a VBS script on a network drive and put shortcuts to that VBS script on everyone's desktop. This VBS script would clean the Temp folder (much easier than doing it manually). This is the one I use. It was written by Michael Harris: http://groups.google.com/groups?thre...%40tkmsftngp02 If you're not on a network, you could put the script in a local folder and still put a shortcut to the script on the desktop. (I don't like to put the real file on the desktop--it's too easily deleted.) darrenrwood wrote: I am receiving e mails each week which include a excel 97 spreadsheet. I then open the spreadsheet and use it within excel. However on occassions the file name changes from say "fredbloggs" to "Fredbloggs1" and this seems to be because there is already a file ( albeit not open ) named "frebloggs" in the temp folder from the last time i used it. Is there a way to stop this number being added to the name or a way of deleting the .xls files in the temp folder automatically when windows starts ? Whatever the solution I need to keep it simple as others will be carrying out this task who have very little pc knowledge. Its working on Windows NT. And just in case it matters the reason i dont want the file name to change is that there is a macro which chooses the spreadsheet by name and this fails if the name is changed. I hope this makes sense, any help would be very much appreciated Darren -- Dave Peterson -- Dave Peterson |
#7
|
|||
|
|||
I have amended the VBS as per your last message but I still get the error
message regarding no file extension as mentioned earlier. I think this is all a little beyond me, so I might give up now ! Thanks for your help though Darren "Dave Peterson" wrote: Sometimes google inserts extra characters. Here's a copy of the script that I use: '===DeleteTempFiles.vbs=== 'http://groups.google.com/groups?threadm=%23bXVsIHnAHA.920%40tkmsftngp02 Const TemporaryFolder = 2 'for GetSpecialFolder const strTitle = "DeleteTempFiles.vbs" set fso = createobject("scripting.filesystemobject") 'init an empty array (ubound will be -1)... ' 'we use an array and store the file objects. 'this avoids any problems with altering the 'contents of the Files collections while they 'are being iterated. ' arFiles = array() count = -1 'get the path to the temp folder ' tempdir = fso.GetSpecialFolder(TemporaryFolder) 'load the (global scope) arFiles 'SelectFiles calls itself recursively 'for SubFolders ' SelectFiles tempdir msgbox count+1 & " files found",0,strtitle 'now do the actual deletes. the error trap 'is in case any are in-use... ' dcount = 0 for each file in arFiles on error resume next file.delete true if err.number = 0 then dcount = dcount + 1 err.clear on error goto 0 next 'now go back and delete empty folders 'below the temp folder DeleteEmptyFolders tempdir,false 'comment out for "silent" operation, 'or add support for a "/s" command-line switch. ' msgbox count+1 & " files found, " & dcount & " deleted.", 0,strtitle sub SelectFiles(sPath) 'select files to delete and add to array... ' set folder = fso.getfolder(sPath) set files = folder.files for each file in files count = count + 1 redim preserve arFiles(count) set arFiles(count) = file next for each fldr in folder.subfolders SelectFiles fldr.path next end sub sub DeleteEmptyFolders(sPath,bDeleteThisFolder) set folder = fso.getfolder(sPath) 'recurse first... ' for each fldr in folder.subfolders DeleteEmptyFolders fldr.path,true next 'if no files or folders then delete... ' 'bDeleteThisFolder is false for 'the root of the subtree, and true for 'sub-folders (unless you want to delete 'the entire subtree if it is empty). ' if (folder.files.count = 0) and _ (folder.subfolders.count) = 0 and _ bDeleteThisFolder then folder.delete exit sub end if end sub ' '===end-script=== darrenrwood wrote: I have typed the vbs you refered to into wordpad and saved it to my desktop for now. I am not on a network. I double clicked on the icon and get the error " There is no file extension in "c:\winnt\profiles\wooddk\desltop\*"" I am assuming as i have saved it straight into desktop that this error message is not refering to the actual opening of the script but something the script is trying to do? Do i need to amend the script to refer to where my tem folder is ? As far as i can see it is just in c drive . I am not clued up enough to know what may be causing the error Thanks "Dave Peterson" wrote: I think this isn't an excel question--it's more about what your email program does. Personally, I'd put a VBS script on a network drive and put shortcuts to that VBS script on everyone's desktop. This VBS script would clean the Temp folder (much easier than doing it manually). This is the one I use. It was written by Michael Harris: http://groups.google.com/groups?thre...%40tkmsftngp02 If you're not on a network, you could put the script in a local folder and still put a shortcut to the script on the desktop. (I don't like to put the real file on the desktop--it's too easily deleted.) darrenrwood wrote: I am receiving e mails each week which include a excel 97 spreadsheet. I then open the spreadsheet and use it within excel. However on occassions the file name changes from say "fredbloggs" to "Fredbloggs1" and this seems to be because there is already a file ( albeit not open ) named "frebloggs" in the temp folder from the last time i used it. Is there a way to stop this number being added to the name or a way of deleting the .xls files in the temp folder automatically when windows starts ? Whatever the solution I need to keep it simple as others will be carrying out this task who have very little pc knowledge. Its working on Windows NT. And just in case it matters the reason i dont want the file name to change is that there is a macro which chooses the spreadsheet by name and this fails if the name is changed. I hope this makes sense, any help would be very much appreciated Darren -- Dave Peterson -- Dave Peterson |
#8
|
|||
|
|||
When you run the script, do you get an error message that indicates the line the
error occurred on. (I've used this for years and never seen an error message at all.) darrenrwood wrote: I have amended the VBS as per your last message but I still get the error message regarding no file extension as mentioned earlier. I think this is all a little beyond me, so I might give up now ! Thanks for your help though Darren "Dave Peterson" wrote: Sometimes google inserts extra characters. Here's a copy of the script that I use: '===DeleteTempFiles.vbs=== 'http://groups.google.com/groups?threadm=%23bXVsIHnAHA.920%40tkmsftngp02 Const TemporaryFolder = 2 'for GetSpecialFolder const strTitle = "DeleteTempFiles.vbs" set fso = createobject("scripting.filesystemobject") 'init an empty array (ubound will be -1)... ' 'we use an array and store the file objects. 'this avoids any problems with altering the 'contents of the Files collections while they 'are being iterated. ' arFiles = array() count = -1 'get the path to the temp folder ' tempdir = fso.GetSpecialFolder(TemporaryFolder) 'load the (global scope) arFiles 'SelectFiles calls itself recursively 'for SubFolders ' SelectFiles tempdir msgbox count+1 & " files found",0,strtitle 'now do the actual deletes. the error trap 'is in case any are in-use... ' dcount = 0 for each file in arFiles on error resume next file.delete true if err.number = 0 then dcount = dcount + 1 err.clear on error goto 0 next 'now go back and delete empty folders 'below the temp folder DeleteEmptyFolders tempdir,false 'comment out for "silent" operation, 'or add support for a "/s" command-line switch. ' msgbox count+1 & " files found, " & dcount & " deleted.", 0,strtitle sub SelectFiles(sPath) 'select files to delete and add to array... ' set folder = fso.getfolder(sPath) set files = folder.files for each file in files count = count + 1 redim preserve arFiles(count) set arFiles(count) = file next for each fldr in folder.subfolders SelectFiles fldr.path next end sub sub DeleteEmptyFolders(sPath,bDeleteThisFolder) set folder = fso.getfolder(sPath) 'recurse first... ' for each fldr in folder.subfolders DeleteEmptyFolders fldr.path,true next 'if no files or folders then delete... ' 'bDeleteThisFolder is false for 'the root of the subtree, and true for 'sub-folders (unless you want to delete 'the entire subtree if it is empty). ' if (folder.files.count = 0) and _ (folder.subfolders.count) = 0 and _ bDeleteThisFolder then folder.delete exit sub end if end sub ' '===end-script=== darrenrwood wrote: I have typed the vbs you refered to into wordpad and saved it to my desktop for now. I am not on a network. I double clicked on the icon and get the error " There is no file extension in "c:\winnt\profiles\wooddk\desltop\*"" I am assuming as i have saved it straight into desktop that this error message is not refering to the actual opening of the script but something the script is trying to do? Do i need to amend the script to refer to where my tem folder is ? As far as i can see it is just in c drive . I am not clued up enough to know what may be causing the error Thanks "Dave Peterson" wrote: I think this isn't an excel question--it's more about what your email program does. Personally, I'd put a VBS script on a network drive and put shortcuts to that VBS script on everyone's desktop. This VBS script would clean the Temp folder (much easier than doing it manually). This is the one I use. It was written by Michael Harris: http://groups.google.com/groups?thre...%40tkmsftngp02 If you're not on a network, you could put the script in a local folder and still put a shortcut to the script on the desktop. (I don't like to put the real file on the desktop--it's too easily deleted.) darrenrwood wrote: I am receiving e mails each week which include a excel 97 spreadsheet. I then open the spreadsheet and use it within excel. However on occassions the file name changes from say "fredbloggs" to "Fredbloggs1" and this seems to be because there is already a file ( albeit not open ) named "frebloggs" in the temp folder from the last time i used it. Is there a way to stop this number being added to the name or a way of deleting the .xls files in the temp folder automatically when windows starts ? Whatever the solution I need to keep it simple as others will be carrying out this task who have very little pc knowledge. Its working on Windows NT. And just in case it matters the reason i dont want the file name to change is that there is a macro which chooses the spreadsheet by name and this fails if the name is changed. I hope this makes sense, any help would be very much appreciated Darren -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening an Excel File | Excel Discussion (Misc queries) | |||
Opening a file with code without a set file name | Excel Discussion (Misc queries) | |||
Opening a file with a Macro | Excel Discussion (Misc queries) | |||
Problem Opening An Excel file. - by yk | Excel Discussion (Misc queries) | |||
restrict opening Excel file residing on a shared drive to one user | Excel Discussion (Misc queries) |