FSO.MoveFile

[SteveDB1]

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 Su

 

[Simon Murphy]

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]

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.

 

[SteveDB1]

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.

 

[SteveDB1]

Thanks Steve.
I found the file, and have downloaded it.
I'll be forwarding this along to my colleague.

 

[SteveDB1]

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.

 

[SteveDB1]

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?

 

[Jim Cone]

You may be suffering from another "improvement" in Office 2007?
Try downloading the same file from here...
http://www.microsoft.com/downloads/...48-207d-4be1-8a76-1c4099d7bbb9&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?

 

[SteveDB1]

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]

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