Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
FSO.MoveFile
Morning all.
I'm trying to modify an existing macro to move a file from its source to a secondary folder if it finds an error, for later resolution. I.e., I'm using an existing macro grouping to update a series of workbooks to make for a common format for all our files, then if it finds an error in any of the files, we want to move it from the source location to a "errorfolder" destination for later processing/fixing. The goal is to set aside the files that have errors so we can just focus on "healthy" files, so we're not constantly having to start/stop and then retrace our steps. We've been trying the FSO.Move And we keep getting either 438 or 450 errors. 1- What would we need to modify out of the code below to resolve this? We're using Excel 2007. 2- can we move an open file or do we need to close the file first, and then move it? (I know, stupid question, but this is our first use of this method) ----------------------------------------------------- Sub ASaveErrorToSubFolder() 'same routine as AsaveNewFormat but when any errors found will save to the '\ErrorSaveFiles subfolder With Application .DisplayAlerts = True 'these can be changed to either true or false, depending 'on if you want the alerts or not. .ScreenUpdating = True 'Prevents from having to watch updating of files. End With Folder = ActiveWorkbook.Path 'this sets the folder of the source file FName = ActiveWorkbook.Name 'this looks at the existing file's name 'remove extension FName = Left(FName, InStr(FName, ".") - 1) ' this appears to remove the existing 'file's extension SaveName = Folder & "\ErrorSaveFiles\" & FName & ".xlsx" ' this tells where to 'save it, but one directory deeper. ' if you wish to have it save to another directory, you must specify that directory. xx On Error Resume Next 'This bypasses an error to keep the routine moving. ActiveWorkbook.SaveAs Filename:=SaveName, FileFormat:=xlOpenXMLWorkbook With ActiveWorkbook 'this command grouping is to save workbook after processing 'is completed, .Save 'do a READ ONLY SaveAS, and close the workbook. .ChangeFileAccess Mode:=xlOpenXMLWorkbook 'xlReadOnly xx '.Close 'comment for now, other macro closes wkbk. End With End Sub -------------------------------------------------------------------------------------- Thank you. Best, SteveB. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
FSO.MoveFile
Steve,
I don't have Excel 2007 so won't offer a complete solution, just a couple of comments. Be sure you're using FSO.MoveFile and not FSO.Move. You can move open files with the filesystemobject's MoveFile method although I've never tested from a Workbook when the code was running from within the workbook. You probably want to check that the "\ErrorSaveFiles" subfolder exists before you attempt to move the file there. If it doesn't exist, you can use the file system object to create it and then move your file there. In the line where you remove the extension from FName, you will run into problems if the file name contains any periods in addition to the one setting off the file extension. Unless you're certain that won't happen, I'd use the 'Split' function with the period as the delimiter and then reconstruct the array without the last member. The "Scripting.FileSystemObject" is borrowed from scripting, so some of the best help and examples will come from places that are dedicated to scripting. For some decent examples that can easily be modified to use within VBA, check out http://www.microsoft.com/technet/scr...r/default.mspx and take the link named 'Hey Scripting Guy'. Steve Yandl "SteveDB1" wrote in message ... Morning all. I'm trying to modify an existing macro to move a file from its source to a secondary folder if it finds an error, for later resolution. I.e., I'm using an existing macro grouping to update a series of workbooks to make for a common format for all our files, then if it finds an error in any of the files, we want to move it from the source location to a "errorfolder" destination for later processing/fixing. The goal is to set aside the files that have errors so we can just focus on "healthy" files, so we're not constantly having to start/stop and then retrace our steps. We've been trying the FSO.Move And we keep getting either 438 or 450 errors. 1- What would we need to modify out of the code below to resolve this? We're using Excel 2007. 2- can we move an open file or do we need to close the file first, and then move it? (I know, stupid question, but this is our first use of this method) ----------------------------------------------------- Sub ASaveErrorToSubFolder() 'same routine as AsaveNewFormat but when any errors found will save to the '\ErrorSaveFiles subfolder With Application .DisplayAlerts = True 'these can be changed to either true or false, depending 'on if you want the alerts or not. .ScreenUpdating = True 'Prevents from having to watch updating of files. End With Folder = ActiveWorkbook.Path 'this sets the folder of the source file FName = ActiveWorkbook.Name 'this looks at the existing file's name 'remove extension FName = Left(FName, InStr(FName, ".") - 1) ' this appears to remove the existing 'file's extension SaveName = Folder & "\ErrorSaveFiles\" & FName & ".xlsx" ' this tells where to 'save it, but one directory deeper. ' if you wish to have it save to another directory, you must specify that directory. xx On Error Resume Next 'This bypasses an error to keep the routine moving. ActiveWorkbook.SaveAs Filename:=SaveName, FileFormat:=xlOpenXMLWorkbook With ActiveWorkbook 'this command grouping is to save workbook after processing 'is completed, .Save 'do a READ ONLY SaveAS, and close the workbook. .ChangeFileAccess Mode:=xlOpenXMLWorkbook 'xlReadOnly xx '.Close 'comment for now, other macro closes wkbk. End With End Sub -------------------------------------------------------------------------------------- Thank you. Best, SteveB. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
FSO.MoveFile
Steve
You can't move an open file via Windows Explorer, so I doubt you can with FSO. is the .xlsx the right extension? it should be xlsm if it has macros. Cheers Simon Blog: www.smurfonspreadsheets.net SteveDB1 wrote: Morning all. I'm trying to modify an existing macro to move a file from its source to a secondary folder if it finds an error, for later resolution. I.e., I'm using an existing macro grouping to update a series of workbooks to make for a common format for all our files, then if it finds an error in any of the files, we want to move it from the source location to a "errorfolder" destination for later processing/fixing. The goal is to set aside the files that have errors so we can just focus on "healthy" files, so we're not constantly having to start/stop and then retrace our steps. We've been trying the FSO.Move And we keep getting either 438 or 450 errors. 1- What would we need to modify out of the code below to resolve this? We're using Excel 2007. 2- can we move an open file or do we need to close the file first, and then move it? (I know, stupid question, but this is our first use of this method) ----------------------------------------------------- Sub ASaveErrorToSubFolder() 'same routine as AsaveNewFormat but when any errors found will save to the '\ErrorSaveFiles subfolder With Application .DisplayAlerts = True 'these can be changed to either true or false, depending 'on if you want the alerts or not. .ScreenUpdating = True 'Prevents from having to watch updating of files. End With Folder = ActiveWorkbook.Path 'this sets the folder of the source file FName = ActiveWorkbook.Name 'this looks at the existing file's name 'remove extension FName = Left(FName, InStr(FName, ".") - 1) ' this appears to remove the existing 'file's extension SaveName = Folder & "\ErrorSaveFiles\" & FName & ".xlsx" ' this tells where to 'save it, but one directory deeper. ' if you wish to have it save to another directory, you must specify that directory. xx On Error Resume Next 'This bypasses an error to keep the routine moving. ActiveWorkbook.SaveAs Filename:=SaveName, FileFormat:=xlOpenXMLWorkbook With ActiveWorkbook 'this command grouping is to save workbook after processing 'is completed, .Save 'do a READ ONLY SaveAS, and close the workbook. .ChangeFileAccess Mode:=xlOpenXMLWorkbook 'xlReadOnly xx '.Close 'comment for now, other macro closes wkbk. End With End Sub -------------------------------------------------------------------------------------- Thank you. Best, SteveB. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
FSO.MoveFile
Hi Steve,
I'll look into the scripting guy's page-- thank you. I've already created the errorfiles' folder, so that's set. I will however ensure that I add that to our code. Thank you for explaining the difference on .move, and .movefile. I was looking at both and the difference wasn't explicitly stated. And we'll look more closely at the split function. Thank you for your helps. Best, SteveB. "Steve Yandl" wrote: Steve, I don't have Excel 2007 so won't offer a complete solution, just a couple of comments. Be sure you're using FSO.MoveFile and not FSO.Move. You can move open files with the filesystemobject's MoveFile method although I've never tested from a Workbook when the code was running from within the workbook. You probably want to check that the "\ErrorSaveFiles" subfolder exists before you attempt to move the file there. If it doesn't exist, you can use the file system object to create it and then move your file there. In the line where you remove the extension from FName, you will run into problems if the file name contains any periods in addition to the one setting off the file extension. Unless you're certain that won't happen, I'd use the 'Split' function with the period as the delimiter and then reconstruct the array without the last member. The "Scripting.FileSystemObject" is borrowed from scripting, so some of the best help and examples will come from places that are dedicated to scripting. For some decent examples that can easily be modified to use within VBA, check out http://www.microsoft.com/technet/scr...r/default.mspx and take the link named 'Hey Scripting Guy'. Steve Yandl "SteveDB1" wrote in message ... Morning all. I'm trying to modify an existing macro to move a file from its source to a secondary folder if it finds an error, for later resolution. I.e., I'm using an existing macro grouping to update a series of workbooks to make for a common format for all our files, then if it finds an error in any of the files, we want to move it from the source location to a "errorfolder" destination for later processing/fixing. The goal is to set aside the files that have errors so we can just focus on "healthy" files, so we're not constantly having to start/stop and then retrace our steps. We've been trying the FSO.Move And we keep getting either 438 or 450 errors. 1- What would we need to modify out of the code below to resolve this? We're using Excel 2007. 2- can we move an open file or do we need to close the file first, and then move it? (I know, stupid question, but this is our first use of this method) ----------------------------------------------------- Sub ASaveErrorToSubFolder() 'same routine as AsaveNewFormat but when any errors found will save to the '\ErrorSaveFiles subfolder With Application .DisplayAlerts = True 'these can be changed to either true or false, depending 'on if you want the alerts or not. .ScreenUpdating = True 'Prevents from having to watch updating of files. End With Folder = ActiveWorkbook.Path 'this sets the folder of the source file FName = ActiveWorkbook.Name 'this looks at the existing file's name 'remove extension FName = Left(FName, InStr(FName, ".") - 1) ' this appears to remove the existing 'file's extension SaveName = Folder & "\ErrorSaveFiles\" & FName & ".xlsx" ' this tells where to 'save it, but one directory deeper. ' if you wish to have it save to another directory, you must specify that directory. xx On Error Resume Next 'This bypasses an error to keep the routine moving. ActiveWorkbook.SaveAs Filename:=SaveName, FileFormat:=xlOpenXMLWorkbook With ActiveWorkbook 'this command grouping is to save workbook after processing 'is completed, .Save 'do a READ ONLY SaveAS, and close the workbook. .ChangeFileAccess Mode:=xlOpenXMLWorkbook 'xlReadOnly xx '.Close 'comment for now, other macro closes wkbk. End With End Sub -------------------------------------------------------------------------------------- Thank you. Best, SteveB. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
FSO.MoveFile
Simon,
Thank you for the clarification. We don't place macros in our workbooks. We use everything from either our xlsb, or xlam files. Too many non-macro users modfiy these files, and with as tight as the security is on 2007 now, we don't want to give them any reason to get confused. While we're in testing stages we only place the testing macros in the xlsb files. After all of our testing is complete we disseminate them through our xlam files. "Simon Murphy" wrote: Steve You can't move an open file via Windows Explorer, so I doubt you can with FSO. is the .xlsx the right extension? it should be xlsm if it has macros. Cheers Simon Blog: www.smurfonspreadsheets.net SteveDB1 wrote: Morning all. I'm trying to modify an existing macro to move a file from its source to a secondary folder if it finds an error, for later resolution. I.e., I'm using an existing macro grouping to update a series of workbooks to make for a common format for all our files, then if it finds an error in any of the files, we want to move it from the source location to a "errorfolder" destination for later processing/fixing. The goal is to set aside the files that have errors so we can just focus on "healthy" files, so we're not constantly having to start/stop and then retrace our steps. We've been trying the FSO.Move And we keep getting either 438 or 450 errors. 1- What would we need to modify out of the code below to resolve this? We're using Excel 2007. 2- can we move an open file or do we need to close the file first, and then move it? (I know, stupid question, but this is our first use of this method) ----------------------------------------------------- Sub ASaveErrorToSubFolder() 'same routine as AsaveNewFormat but when any errors found will save to the '\ErrorSaveFiles subfolder With Application .DisplayAlerts = True 'these can be changed to either true or false, depending 'on if you want the alerts or not. .ScreenUpdating = True 'Prevents from having to watch updating of files. End With Folder = ActiveWorkbook.Path 'this sets the folder of the source file FName = ActiveWorkbook.Name 'this looks at the existing file's name 'remove extension FName = Left(FName, InStr(FName, ".") - 1) ' this appears to remove the existing 'file's extension SaveName = Folder & "\ErrorSaveFiles\" & FName & ".xlsx" ' this tells where to 'save it, but one directory deeper. ' if you wish to have it save to another directory, you must specify that directory. xx On Error Resume Next 'This bypasses an error to keep the routine moving. ActiveWorkbook.SaveAs Filename:=SaveName, FileFormat:=xlOpenXMLWorkbook With ActiveWorkbook 'this command grouping is to save workbook after processing 'is completed, .Save 'do a READ ONLY SaveAS, and close the workbook. .ChangeFileAccess Mode:=xlOpenXMLWorkbook 'xlReadOnly xx '.Close 'comment for now, other macro closes wkbk. End With End Sub -------------------------------------------------------------------------------------- Thank you. Best, SteveB. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
FSO.MoveFile
Steve, you're welcome. On that scripting page, there are a couple of help
files available for download. I find that Script56.CHM is a bit more help than the VBA help files when using FSO. I use it all the time. It may make life simpler down the road if you download it now. Steve Yandl "SteveDB1" wrote in message ... Hi Steve, I'll look into the scripting guy's page-- thank you. I've already created the errorfiles' folder, so that's set. I will however ensure that I add that to our code. Thank you for explaining the difference on .move, and .movefile. I was looking at both and the difference wasn't explicitly stated. And we'll look more closely at the split function. Thank you for your helps. Best, SteveB. "Steve Yandl" wrote: Steve, I don't have Excel 2007 so won't offer a complete solution, just a couple of comments. Be sure you're using FSO.MoveFile and not FSO.Move. You can move open files with the filesystemobject's MoveFile method although I've never tested from a Workbook when the code was running from within the workbook. You probably want to check that the "\ErrorSaveFiles" subfolder exists before you attempt to move the file there. If it doesn't exist, you can use the file system object to create it and then move your file there. In the line where you remove the extension from FName, you will run into problems if the file name contains any periods in addition to the one setting off the file extension. Unless you're certain that won't happen, I'd use the 'Split' function with the period as the delimiter and then reconstruct the array without the last member. The "Scripting.FileSystemObject" is borrowed from scripting, so some of the best help and examples will come from places that are dedicated to scripting. For some decent examples that can easily be modified to use within VBA, check out http://www.microsoft.com/technet/scr...r/default.mspx and take the link named 'Hey Scripting Guy'. Steve Yandl "SteveDB1" wrote in message ... Morning all. I'm trying to modify an existing macro to move a file from its source to a secondary folder if it finds an error, for later resolution. I.e., I'm using an existing macro grouping to update a series of workbooks to make for a common format for all our files, then if it finds an error in any of the files, we want to move it from the source location to a "errorfolder" destination for later processing/fixing. The goal is to set aside the files that have errors so we can just focus on "healthy" files, so we're not constantly having to start/stop and then retrace our steps. We've been trying the FSO.Move And we keep getting either 438 or 450 errors. 1- What would we need to modify out of the code below to resolve this? We're using Excel 2007. 2- can we move an open file or do we need to close the file first, and then move it? (I know, stupid question, but this is our first use of this method) ----------------------------------------------------- Sub ASaveErrorToSubFolder() 'same routine as AsaveNewFormat but when any errors found will save to the '\ErrorSaveFiles subfolder With Application .DisplayAlerts = True 'these can be changed to either true or false, depending 'on if you want the alerts or not. .ScreenUpdating = True 'Prevents from having to watch updating of files. End With Folder = ActiveWorkbook.Path 'this sets the folder of the source file FName = ActiveWorkbook.Name 'this looks at the existing file's name 'remove extension FName = Left(FName, InStr(FName, ".") - 1) ' this appears to remove the existing 'file's extension SaveName = Folder & "\ErrorSaveFiles\" & FName & ".xlsx" ' this tells where to 'save it, but one directory deeper. ' if you wish to have it save to another directory, you must specify that directory. xx On Error Resume Next 'This bypasses an error to keep the routine moving. ActiveWorkbook.SaveAs Filename:=SaveName, FileFormat:=xlOpenXMLWorkbook With ActiveWorkbook 'this command grouping is to save workbook after processing 'is completed, .Save 'do a READ ONLY SaveAS, and close the workbook. .ChangeFileAccess Mode:=xlOpenXMLWorkbook 'xlReadOnly xx '.Close 'comment for now, other macro closes wkbk. End With End Sub -------------------------------------------------------------------------------------- Thank you. Best, SteveB. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
FSO.MoveFile
Thanks Steve.
I found the file, and have downloaded it. I'll be forwarding this along to my colleague. "Steve Yandl" wrote: Steve, you're welcome. On that scripting page, there are a couple of help files available for download. I find that Script56.CHM is a bit more help than the VBA help files when using FSO. I use it all the time. It may make life simpler down the road if you download it now. Steve Yandl "SteveDB1" wrote in message ... Hi Steve, I'll look into the scripting guy's page-- thank you. I've already created the errorfiles' folder, so that's set. I will however ensure that I add that to our code. Thank you for explaining the difference on .move, and .movefile. I was looking at both and the difference wasn't explicitly stated. And we'll look more closely at the split function. Thank you for your helps. Best, SteveB. "Steve Yandl" wrote: Steve, I don't have Excel 2007 so won't offer a complete solution, just a couple of comments. Be sure you're using FSO.MoveFile and not FSO.Move. You can move open files with the filesystemobject's MoveFile method although I've never tested from a Workbook when the code was running from within the workbook. You probably want to check that the "\ErrorSaveFiles" subfolder exists before you attempt to move the file there. If it doesn't exist, you can use the file system object to create it and then move your file there. In the line where you remove the extension from FName, you will run into problems if the file name contains any periods in addition to the one setting off the file extension. Unless you're certain that won't happen, I'd use the 'Split' function with the period as the delimiter and then reconstruct the array without the last member. The "Scripting.FileSystemObject" is borrowed from scripting, so some of the best help and examples will come from places that are dedicated to scripting. For some decent examples that can easily be modified to use within VBA, check out http://www.microsoft.com/technet/scr...r/default.mspx and take the link named 'Hey Scripting Guy'. Steve Yandl "SteveDB1" wrote in message ... Morning all. I'm trying to modify an existing macro to move a file from its source to a secondary folder if it finds an error, for later resolution. I.e., I'm using an existing macro grouping to update a series of workbooks to make for a common format for all our files, then if it finds an error in any of the files, we want to move it from the source location to a "errorfolder" destination for later processing/fixing. The goal is to set aside the files that have errors so we can just focus on "healthy" files, so we're not constantly having to start/stop and then retrace our steps. We've been trying the FSO.Move And we keep getting either 438 or 450 errors. 1- What would we need to modify out of the code below to resolve this? We're using Excel 2007. 2- can we move an open file or do we need to close the file first, and then move it? (I know, stupid question, but this is our first use of this method) ----------------------------------------------------- Sub ASaveErrorToSubFolder() 'same routine as AsaveNewFormat but when any errors found will save to the '\ErrorSaveFiles subfolder With Application .DisplayAlerts = True 'these can be changed to either true or false, depending 'on if you want the alerts or not. .ScreenUpdating = True 'Prevents from having to watch updating of files. End With Folder = ActiveWorkbook.Path 'this sets the folder of the source file FName = ActiveWorkbook.Name 'this looks at the existing file's name 'remove extension FName = Left(FName, InStr(FName, ".") - 1) ' this appears to remove the existing 'file's extension SaveName = Folder & "\ErrorSaveFiles\" & FName & ".xlsx" ' this tells where to 'save it, but one directory deeper. ' if you wish to have it save to another directory, you must specify that directory. xx On Error Resume Next 'This bypasses an error to keep the routine moving. ActiveWorkbook.SaveAs Filename:=SaveName, FileFormat:=xlOpenXMLWorkbook With ActiveWorkbook 'this command grouping is to save workbook after processing 'is completed, .Save 'do a READ ONLY SaveAS, and close the workbook. .ChangeFileAccess Mode:=xlOpenXMLWorkbook 'xlReadOnly xx '.Close 'comment for now, other macro closes wkbk. End With End Sub -------------------------------------------------------------------------------------- Thank you. Best, SteveB. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
FSO.MoveFile
Hey Steve,
Have you had any problems viewing the help files in that chm file? I downloaded it, and I just keep getting "page cannot be displayed" responses. "Steve Yandl" wrote: Steve, you're welcome. On that scripting page, there are a couple of help files available for download. I find that Script56.CHM is a bit more help than the VBA help files when using FSO. I use it all the time. It may make life simpler down the road if you download it now. Steve Yandl "SteveDB1" wrote in message ... Hi Steve, I'll look into the scripting guy's page-- thank you. I've already created the errorfiles' folder, so that's set. I will however ensure that I add that to our code. Thank you for explaining the difference on .move, and .movefile. I was looking at both and the difference wasn't explicitly stated. And we'll look more closely at the split function. Thank you for your helps. Best, SteveB. "Steve Yandl" wrote: Steve, I don't have Excel 2007 so won't offer a complete solution, just a couple of comments. Be sure you're using FSO.MoveFile and not FSO.Move. You can move open files with the filesystemobject's MoveFile method although I've never tested from a Workbook when the code was running from within the workbook. You probably want to check that the "\ErrorSaveFiles" subfolder exists before you attempt to move the file there. If it doesn't exist, you can use the file system object to create it and then move your file there. In the line where you remove the extension from FName, you will run into problems if the file name contains any periods in addition to the one setting off the file extension. Unless you're certain that won't happen, I'd use the 'Split' function with the period as the delimiter and then reconstruct the array without the last member. The "Scripting.FileSystemObject" is borrowed from scripting, so some of the best help and examples will come from places that are dedicated to scripting. For some decent examples that can easily be modified to use within VBA, check out http://www.microsoft.com/technet/scr...r/default.mspx and take the link named 'Hey Scripting Guy'. Steve Yandl "SteveDB1" wrote in message ... Morning all. I'm trying to modify an existing macro to move a file from its source to a secondary folder if it finds an error, for later resolution. I.e., I'm using an existing macro grouping to update a series of workbooks to make for a common format for all our files, then if it finds an error in any of the files, we want to move it from the source location to a "errorfolder" destination for later processing/fixing. The goal is to set aside the files that have errors so we can just focus on "healthy" files, so we're not constantly having to start/stop and then retrace our steps. We've been trying the FSO.Move And we keep getting either 438 or 450 errors. 1- What would we need to modify out of the code below to resolve this? We're using Excel 2007. 2- can we move an open file or do we need to close the file first, and then move it? (I know, stupid question, but this is our first use of this method) ----------------------------------------------------- Sub ASaveErrorToSubFolder() 'same routine as AsaveNewFormat but when any errors found will save to the '\ErrorSaveFiles subfolder With Application .DisplayAlerts = True 'these can be changed to either true or false, depending 'on if you want the alerts or not. .ScreenUpdating = True 'Prevents from having to watch updating of files. End With Folder = ActiveWorkbook.Path 'this sets the folder of the source file FName = ActiveWorkbook.Name 'this looks at the existing file's name 'remove extension FName = Left(FName, InStr(FName, ".") - 1) ' this appears to remove the existing 'file's extension SaveName = Folder & "\ErrorSaveFiles\" & FName & ".xlsx" ' this tells where to 'save it, but one directory deeper. ' if you wish to have it save to another directory, you must specify that directory. xx On Error Resume Next 'This bypasses an error to keep the routine moving. ActiveWorkbook.SaveAs Filename:=SaveName, FileFormat:=xlOpenXMLWorkbook With ActiveWorkbook 'this command grouping is to save workbook after processing 'is completed, .Save 'do a READ ONLY SaveAS, and close the workbook. .ChangeFileAccess Mode:=xlOpenXMLWorkbook 'xlReadOnly xx '.Close 'comment for now, other macro closes wkbk. End With End Sub -------------------------------------------------------------------------------------- Thank you. Best, SteveB. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
FSO.MoveFile
No problems for me although I downloaded it quite some time ago.
Steve Yandl "SteveDB1" wrote in message ... Hey Steve, Have you had any problems viewing the help files in that chm file? I downloaded it, and I just keep getting "page cannot be displayed" responses. "Steve Yandl" wrote: Steve, you're welcome. On that scripting page, there are a couple of help files available for download. I find that Script56.CHM is a bit more help than the VBA help files when using FSO. I use it all the time. It may make life simpler down the road if you download it now. Steve Yandl "SteveDB1" wrote in message ... Hi Steve, I'll look into the scripting guy's page-- thank you. I've already created the errorfiles' folder, so that's set. I will however ensure that I add that to our code. Thank you for explaining the difference on .move, and .movefile. I was looking at both and the difference wasn't explicitly stated. And we'll look more closely at the split function. Thank you for your helps. Best, SteveB. "Steve Yandl" wrote: Steve, I don't have Excel 2007 so won't offer a complete solution, just a couple of comments. Be sure you're using FSO.MoveFile and not FSO.Move. You can move open files with the filesystemobject's MoveFile method although I've never tested from a Workbook when the code was running from within the workbook. You probably want to check that the "\ErrorSaveFiles" subfolder exists before you attempt to move the file there. If it doesn't exist, you can use the file system object to create it and then move your file there. In the line where you remove the extension from FName, you will run into problems if the file name contains any periods in addition to the one setting off the file extension. Unless you're certain that won't happen, I'd use the 'Split' function with the period as the delimiter and then reconstruct the array without the last member. The "Scripting.FileSystemObject" is borrowed from scripting, so some of the best help and examples will come from places that are dedicated to scripting. For some decent examples that can easily be modified to use within VBA, check out http://www.microsoft.com/technet/scr...r/default.mspx and take the link named 'Hey Scripting Guy'. Steve Yandl "SteveDB1" wrote in message ... Morning all. I'm trying to modify an existing macro to move a file from its source to a secondary folder if it finds an error, for later resolution. I.e., I'm using an existing macro grouping to update a series of workbooks to make for a common format for all our files, then if it finds an error in any of the files, we want to move it from the source location to a "errorfolder" destination for later processing/fixing. The goal is to set aside the files that have errors so we can just focus on "healthy" files, so we're not constantly having to start/stop and then retrace our steps. We've been trying the FSO.Move And we keep getting either 438 or 450 errors. 1- What would we need to modify out of the code below to resolve this? We're using Excel 2007. 2- can we move an open file or do we need to close the file first, and then move it? (I know, stupid question, but this is our first use of this method) ----------------------------------------------------- Sub ASaveErrorToSubFolder() 'same routine as AsaveNewFormat but when any errors found will save to the '\ErrorSaveFiles subfolder With Application .DisplayAlerts = True 'these can be changed to either true or false, depending 'on if you want the alerts or not. .ScreenUpdating = True 'Prevents from having to watch updating of files. End With Folder = ActiveWorkbook.Path 'this sets the folder of the source file FName = ActiveWorkbook.Name 'this looks at the existing file's name 'remove extension FName = Left(FName, InStr(FName, ".") - 1) ' this appears to remove the existing 'file's extension SaveName = Folder & "\ErrorSaveFiles\" & FName & ".xlsx" ' this tells where to 'save it, but one directory deeper. ' if you wish to have it save to another directory, you must specify that directory. xx On Error Resume Next 'This bypasses an error to keep the routine moving. ActiveWorkbook.SaveAs Filename:=SaveName, FileFormat:=xlOpenXMLWorkbook With ActiveWorkbook 'this command grouping is to save workbook after processing 'is completed, .Save 'do a READ ONLY SaveAS, and close the workbook. .ChangeFileAccess Mode:=xlOpenXMLWorkbook 'xlReadOnly xx '.Close 'comment for now, other macro closes wkbk. End With End Sub -------------------------------------------------------------------------------------- Thank you. Best, SteveB. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
FSO.MoveFile
Morning Steve,
I tried downloading the file on my office computer and found that it gives me the same error-- cannot display page. I pointed my colleague to the same download page, had him download it on his, and found that he obtains the same error. Since chm formatted files are the standard help files today, I'm now wondering if it needs to be accessed through the builtin windows help tool, or placed in a specific directory, and then accessed. Any ideas? "Steve Yandl" wrote: No problems for me although I downloaded it quite some time ago. Steve Yandl "SteveDB1" wrote in message ... Hey Steve, Have you had any problems viewing the help files in that chm file? I downloaded it, and I just keep getting "page cannot be displayed" responses. "Steve Yandl" wrote: Steve, you're welcome. On that scripting page, there are a couple of help files available for download. I find that Script56.CHM is a bit more help than the VBA help files when using FSO. I use it all the time. It may make life simpler down the road if you download it now. Steve Yandl "SteveDB1" wrote in message ... Hi Steve, I'll look into the scripting guy's page-- thank you. I've already created the errorfiles' folder, so that's set. I will however ensure that I add that to our code. Thank you for explaining the difference on .move, and .movefile. I was looking at both and the difference wasn't explicitly stated. And we'll look more closely at the split function. Thank you for your helps. Best, SteveB. "Steve Yandl" wrote: Steve, I don't have Excel 2007 so won't offer a complete solution, just a couple of comments. Be sure you're using FSO.MoveFile and not FSO.Move. You can move open files with the filesystemobject's MoveFile method although I've never tested from a Workbook when the code was running from within the workbook. You probably want to check that the "\ErrorSaveFiles" subfolder exists before you attempt to move the file there. If it doesn't exist, you can use the file system object to create it and then move your file there. In the line where you remove the extension from FName, you will run into problems if the file name contains any periods in addition to the one setting off the file extension. Unless you're certain that won't happen, I'd use the 'Split' function with the period as the delimiter and then reconstruct the array without the last member. The "Scripting.FileSystemObject" is borrowed from scripting, so some of the best help and examples will come from places that are dedicated to scripting. For some decent examples that can easily be modified to use within VBA, check out http://www.microsoft.com/technet/scr...r/default.mspx and take the link named 'Hey Scripting Guy'. Steve Yandl "SteveDB1" wrote in message ... Morning all. I'm trying to modify an existing macro to move a file from its source to a secondary folder if it finds an error, for later resolution. I.e., I'm using an existing macro grouping to update a series of workbooks to make for a common format for all our files, then if it finds an error in any of the files, we want to move it from the source location to a "errorfolder" destination for later processing/fixing. The goal is to set aside the files that have errors so we can just focus on "healthy" files, so we're not constantly having to start/stop and then retrace our steps. We've been trying the FSO.Move And we keep getting either 438 or 450 errors. 1- What would we need to modify out of the code below to resolve this? We're using Excel 2007. 2- can we move an open file or do we need to close the file first, and then move it? (I know, stupid question, but this is our first use of this method) ----------------------------------------------------- Sub ASaveErrorToSubFolder() 'same routine as AsaveNewFormat but when any errors found will save to the '\ErrorSaveFiles subfolder With Application .DisplayAlerts = True 'these can be changed to either true or false, depending 'on if you want the alerts or not. .ScreenUpdating = True 'Prevents from having to watch updating of files. End With Folder = ActiveWorkbook.Path 'this sets the folder of the source file FName = ActiveWorkbook.Name 'this looks at the existing file's name 'remove extension FName = Left(FName, InStr(FName, ".") - 1) ' this appears to remove the existing 'file's extension SaveName = Folder & "\ErrorSaveFiles\" & FName & ".xlsx" ' this tells where to 'save it, but one directory deeper. ' if you wish to have it save to another directory, you must specify that directory. xx On Error Resume Next 'This bypasses an error to keep the routine moving. ActiveWorkbook.SaveAs Filename:=SaveName, FileFormat:=xlOpenXMLWorkbook With ActiveWorkbook 'this command grouping is to save workbook after processing 'is completed, .Save 'do a READ ONLY SaveAS, and close the workbook. .ChangeFileAccess Mode:=xlOpenXMLWorkbook 'xlReadOnly xx '.Close 'comment for now, other macro closes wkbk. End With End Sub -------------------------------------------------------------------------------------- Thank you. Best, SteveB. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
FSO.MoveFile
You may be suffering from another "improvement" in Office 2007? Try downloading the same file from here... http://www.microsoft.com/downloads/d...DisplayLang=en -- Jim Cone Portland, Oregon USA "SteveDB1" wrote in message Morning Steve, I tried downloading the file on my office computer and found that it gives me the same error-- cannot display page. I pointed my colleague to the same download page, had him download it on his, and found that he obtains the same error. Since chm formatted files are the standard help files today, I'm now wondering if it needs to be accessed through the builtin windows help tool, or placed in a specific directory, and then accessed. Any ideas? |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
FSO.MoveFile
Steve,
I took a look on my system and see that Script56.CHM is located in a subfolder of the "Program Files" folder named "\Microsoft Windows Script\VBSdocs". In the same folder I see VBS55.CHM and two inf files. Steve Yandl "SteveDB1" wrote in message ... Morning Steve, I tried downloading the file on my office computer and found that it gives me the same error-- cannot display page. I pointed my colleague to the same download page, had him download it on his, and found that he obtains the same error. Since chm formatted files are the standard help files today, I'm now wondering if it needs to be accessed through the builtin windows help tool, or placed in a specific directory, and then accessed. Any ideas? "Steve Yandl" wrote: No problems for me although I downloaded it quite some time ago. Steve Yandl "SteveDB1" wrote in message ... Hey Steve, Have you had any problems viewing the help files in that chm file? I downloaded it, and I just keep getting "page cannot be displayed" responses. "Steve Yandl" wrote: Steve, you're welcome. On that scripting page, there are a couple of help files available for download. I find that Script56.CHM is a bit more help than the VBA help files when using FSO. I use it all the time. It may make life simpler down the road if you download it now. Steve Yandl "SteveDB1" wrote in message ... Hi Steve, I'll look into the scripting guy's page-- thank you. I've already created the errorfiles' folder, so that's set. I will however ensure that I add that to our code. Thank you for explaining the difference on .move, and .movefile. I was looking at both and the difference wasn't explicitly stated. And we'll look more closely at the split function. Thank you for your helps. Best, SteveB. "Steve Yandl" wrote: Steve, I don't have Excel 2007 so won't offer a complete solution, just a couple of comments. Be sure you're using FSO.MoveFile and not FSO.Move. You can move open files with the filesystemobject's MoveFile method although I've never tested from a Workbook when the code was running from within the workbook. You probably want to check that the "\ErrorSaveFiles" subfolder exists before you attempt to move the file there. If it doesn't exist, you can use the file system object to create it and then move your file there. In the line where you remove the extension from FName, you will run into problems if the file name contains any periods in addition to the one setting off the file extension. Unless you're certain that won't happen, I'd use the 'Split' function with the period as the delimiter and then reconstruct the array without the last member. The "Scripting.FileSystemObject" is borrowed from scripting, so some of the best help and examples will come from places that are dedicated to scripting. For some decent examples that can easily be modified to use within VBA, check out http://www.microsoft.com/technet/scr...r/default.mspx and take the link named 'Hey Scripting Guy'. Steve Yandl "SteveDB1" wrote in message ... Morning all. I'm trying to modify an existing macro to move a file from its source to a secondary folder if it finds an error, for later resolution. I.e., I'm using an existing macro grouping to update a series of workbooks to make for a common format for all our files, then if it finds an error in any of the files, we want to move it from the source location to a "errorfolder" destination for later processing/fixing. The goal is to set aside the files that have errors so we can just focus on "healthy" files, so we're not constantly having to start/stop and then retrace our steps. We've been trying the FSO.Move And we keep getting either 438 or 450 errors. 1- What would we need to modify out of the code below to resolve this? We're using Excel 2007. 2- can we move an open file or do we need to close the file first, and then move it? (I know, stupid question, but this is our first use of this method) ----------------------------------------------------- Sub ASaveErrorToSubFolder() 'same routine as AsaveNewFormat but when any errors found will save to the '\ErrorSaveFiles subfolder With Application .DisplayAlerts = True 'these can be changed to either true or false, depending 'on if you want the alerts or not. .ScreenUpdating = True 'Prevents from having to watch updating of files. End With Folder = ActiveWorkbook.Path 'this sets the folder of the source file FName = ActiveWorkbook.Name 'this looks at the existing file's name 'remove extension FName = Left(FName, InStr(FName, ".") - 1) ' this appears to remove the existing 'file's extension SaveName = Folder & "\ErrorSaveFiles\" & FName & ".xlsx" ' this tells where to 'save it, but one directory deeper. ' if you wish to have it save to another directory, you must specify that directory. xx On Error Resume Next 'This bypasses an error to keep the routine moving. ActiveWorkbook.SaveAs Filename:=SaveName, FileFormat:=xlOpenXMLWorkbook With ActiveWorkbook 'this command grouping is to save workbook after processing 'is completed, .Save 'do a READ ONLY SaveAS, and close the workbook. .ChangeFileAccess Mode:=xlOpenXMLWorkbook 'xlReadOnly xx '.Close 'comment for now, other macro closes wkbk. End With End Sub -------------------------------------------------------------------------------------- Thank you. Best, SteveB. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
FSO.MoveFile
Steve and Jim,
I did some further digging and found that there was a windowsxp newsgroup and posted there. I got the answer, and after a couple of tries, getting new doc, etc... it finally works. Thanks again for your help. "Steve Yandl" wrote: Steve, I took a look on my system and see that Script56.CHM is located in a subfolder of the "Program Files" folder named "\Microsoft Windows Script\VBSdocs". In the same folder I see VBS55.CHM and two inf files. Steve Yandl "SteveDB1" wrote in message ... Morning Steve, I tried downloading the file on my office computer and found that it gives me the same error-- cannot display page. I pointed my colleague to the same download page, had him download it on his, and found that he obtains the same error. Since chm formatted files are the standard help files today, I'm now wondering if it needs to be accessed through the builtin windows help tool, or placed in a specific directory, and then accessed. Any ideas? "Steve Yandl" wrote: No problems for me although I downloaded it quite some time ago. Steve Yandl "SteveDB1" wrote in message ... Hey Steve, Have you had any problems viewing the help files in that chm file? I downloaded it, and I just keep getting "page cannot be displayed" responses. "Steve Yandl" wrote: Steve, you're welcome. On that scripting page, there are a couple of help files available for download. I find that Script56.CHM is a bit more help than the VBA help files when using FSO. I use it all the time. It may make life simpler down the road if you download it now. Steve Yandl "SteveDB1" wrote in message ... Hi Steve, I'll look into the scripting guy's page-- thank you. I've already created the errorfiles' folder, so that's set. I will however ensure that I add that to our code. Thank you for explaining the difference on .move, and .movefile. I was looking at both and the difference wasn't explicitly stated. And we'll look more closely at the split function. Thank you for your helps. Best, SteveB. "Steve Yandl" wrote: Steve, I don't have Excel 2007 so won't offer a complete solution, just a couple of comments. Be sure you're using FSO.MoveFile and not FSO.Move. You can move open files with the filesystemobject's MoveFile method although I've never tested from a Workbook when the code was running from within the workbook. You probably want to check that the "\ErrorSaveFiles" subfolder exists before you attempt to move the file there. If it doesn't exist, you can use the file system object to create it and then move your file there. In the line where you remove the extension from FName, you will run into problems if the file name contains any periods in addition to the one setting off the file extension. Unless you're certain that won't happen, I'd use the 'Split' function with the period as the delimiter and then reconstruct the array without the last member. The "Scripting.FileSystemObject" is borrowed from scripting, so some of the best help and examples will come from places that are dedicated to scripting. For some decent examples that can easily be modified to use within VBA, check out http://www.microsoft.com/technet/scr...r/default.mspx and take the link named 'Hey Scripting Guy'. Steve Yandl "SteveDB1" wrote in message ... Morning all. I'm trying to modify an existing macro to move a file from its source to a secondary folder if it finds an error, for later resolution. I.e., I'm using an existing macro grouping to update a series of workbooks to make for a common format for all our files, then if it finds an error in any of the files, we want to move it from the source location to a "errorfolder" destination for later processing/fixing. The goal is to set aside the files that have errors so we can just focus on "healthy" files, so we're not constantly having to start/stop and then retrace our steps. We've been trying the FSO.Move And we keep getting either 438 or 450 errors. 1- What would we need to modify out of the code below to resolve this? We're using Excel 2007. 2- can we move an open file or do we need to close the file first, and then move it? (I know, stupid question, but this is our first use of this method) ----------------------------------------------------- Sub ASaveErrorToSubFolder() 'same routine as AsaveNewFormat but when any errors found will save to the '\ErrorSaveFiles subfolder With Application .DisplayAlerts = True 'these can be changed to either true or false, depending 'on if you want the alerts or not. .ScreenUpdating = True 'Prevents from having to watch updating of files. End With Folder = ActiveWorkbook.Path 'this sets the folder of the source file FName = ActiveWorkbook.Name 'this looks at the existing file's name 'remove extension FName = Left(FName, InStr(FName, ".") - 1) ' this appears to remove the existing 'file's extension SaveName = Folder & "\ErrorSaveFiles\" & FName & ".xlsx" ' this tells where to 'save it, but one directory deeper. ' if you wish to have it save to another directory, you must specify that directory. xx On Error Resume Next 'This bypasses an error to keep the routine moving. ActiveWorkbook.SaveAs Filename:=SaveName, FileFormat:=xlOpenXMLWorkbook With ActiveWorkbook 'this command grouping is to save workbook after processing 'is completed, .Save 'do a READ ONLY SaveAS, and close the workbook. .ChangeFileAccess Mode:=xlOpenXMLWorkbook 'xlReadOnly xx '.Close 'comment for now, other macro closes wkbk. End With End Sub -------------------------------------------------------------------------------------- Thank you. Best, SteveB. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
FSO.MoveFile
Morning all....
Well, since this wound up being such a nightmarish project, I wanted to post my fix-- for future searchers.... Per Steve Yandl's recommendation, I was able to download the script56.chm file from MS. In that file there is indeed a much better explanation of the whole file scripting object tools. We finally got it working-- this morning-- and while iterating through each file, when the file was still open, we got each one moved. So, based on a handful of modified solutions provided by the various MVP's, Techs, and VBA programmers here, below is our fix. You'll of course need to modify it to meet your needs. To whom I am deeply grateful-- each and everyone of you-all. While I'm sure that there are indeed more elegant, or simpler ways of writing some of this code, this will move one file at a time, based on your criteria. (and if I've stated something wrong, my apologies....) -------------------------------------------------------------------------------------- Sub AFileSearch() Dim myNames() As String Dim fCtr As Long Dim MyFile As String Dim Mypath As String Dim myProcessedPath As String Dim myFileNoExt As String Dim fso As Object Dim AlreadyProcessed As Boolean Dim TempWkbk As Workbook 'use whatever you know to get the folder 'The myPath setting is a preliminary test location. 'modify this to go with your new location once the 'file location is determined. Mypath = "C:\StevesTemp\PreRun\" If Mypath = "" Then Exit Sub If Right(Mypath, 1) < "\" Then Mypath = Mypath & "\" End If 'Also set myProcessedPath for the portion after the & symbol. myProcessedPath = Mypath & "PostRun" If myProcessedPath = "" Then Exit Sub If Right(myProcessedPath, 1) < "\" Then myProcessedPath = myProcessedPath & "\" End If MyFile = "" On Error Resume Next MyFile = Dir(Mypath & "*.xl*") On Error GoTo 0 If MyFile = "" Then MsgBox "no files found" Exit Sub End If Set fso = CreateObject("Scripting.FileSystemObject") 'get the list of files fCtr = 0 Do While MyFile < "" 'this will populate a list of file names. If LCase(MyFile) Like LCase("DTR*.xl*") Then myFileNoExt = Left(MyFile, InStrRev(MyFile, ".") - 1) 'If you want the file format to be different than an xlsx format, change this ' to the format of your choosing. AlreadyProcessed = fso.FileExists(myProcessedPath & myFileNoExt & ".xlsx") If AlreadyProcessed = True Then MsgBox "The File: " & myFileNoExt & " has already been processed." Else 'not there, so include that file fCtr = fCtr + 1 ReDim Preserve myNames(1 To fCtr) myNames(fCtr) = MyFile End If End If MyFile = Dir() 'when you set your watches for analysis, do not set a watch on Dir() ' it will cause an error to be thrown. Loop If fCtr 0 Then For fCtr = LBound(myNames) To UBound(myNames) Set TempWkbk = Workbooks.Open(FileName:=Mypath & myNames(fCtr)) 'Run "ASaveNewFormat" 'this macro saves the next xls file in line to a new format- xlsx. 'ASaveNewFormat then calls to Compact all sheets to 'formally process all of the workbooks to 'do some macro Call MoveAFile TempWkbk.Close savechanges:=False ' True 'or ' Next fCtr End If End Sub Sub MoveAFile() Dim Mypath As String Mypath = "C:\StevesTemp\PreRun\" Dim MyFile As String MyFile = Dir(Mypath & "*.xl*") Drivespec = Mypath & MyFile Dim myProcessedPath As String myProcessedPath = Mypath & "PostRun\" Dim fso Set fso = CreateObject("Scripting.FileSystemObject") fso.moveFile Drivespec, myProcessedPath End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help!!! MoveFile Method | Excel Programming | |||
Movefile "Permission Denied" | Excel Programming | |||
MoveFile problems | Excel Programming | |||
How to maintain the original date and time for the MoveFile command | Excel Programming |