Directory/File movement with VBA

[Guest]

I have a series of files that I must extract data from each week. What I'd
like to be able to do is run my VBA code against the sheet, then when I'm
finished with the sheet, save it as a different name, in a different
directory, in order to alert other users that the sheets have been processed.

Where should I start in order to achieve my goals?

I currently use the following code to verify & open sheet;
DartRef = "CPCT " & xlmonth2 & " CDART.xls"
DartPth = "\\V1sacpdofc1\CPCTProjects\PMO\DART\F0" & XlYear & "_DART\" &
xlmonth2 & " DART 0" & XlYear & "\" & DartRef

If Dir(DartPth) <> "" Then
xlApp.Workbooks.Open (DartPth), 0, ReadOnly = True
xlApp.ActiveWorkbook.Windows(1).Visible = False
Else
XLMonth = Month(Date) - 1
If XLMonth = 0 Then
XLMonth = "12"
xlmonth2 = "Dec"
XlYear = 5
DartRef = "CPCT " & xlmonth2 & " CDART.xls"
DartPth = "\\V1sacpdofc1\CPCTProjects\PMO\DART\F0" & XlYear &
"_DART\Dec DART 04\" & DartRef
xlApp.Worksheets("Top Sheet").Range("a1") = DartPth
xlApp.Workbooks.Open (DartPth), 0, ReadOnly = True
xlApp.ActiveWorkbook.Windows(1).Visible = False
End If
End If

With xlApp.Workbooks(DartRef).Worksheets(ShtRef)
.Cells.Replace What:="=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Set xlSheet = xlApp.Worksheets.Add
xlSheet.Name = ShtRef
.Cells.Copy xlApp.ActiveWorkbook.Sheets(ShtRef).Range("A1")

.Cells.Replace What:="=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End With

 

[Tom Ogilvy]

DartRef = "CPCT " & xlmonth2 & " CDART.xls"
DartPth = "\\V1sacpdofc1\CPCTProjects\PMO\DART\F0" & XlYear & "_DART\" &
xlmonth2 & " DART 0" & XlYear & "\" & DartRef

If Dir(DartPth) <> "" Then
xlApp.Workbooks.Open (DartPth), 0, ReadOnly = True
xlApp.ActiveWorkbook.Windows(1).Visible = False
Else
XLMonth = Month(Date) - 1
If XLMonth = 0 Then
XLMonth = "12"
xlmonth2 = "Dec"
XlYear = 5
DartRef = "CPCT " & xlmonth2 & " CDART.xls"
DartPth = "\\V1sacpdofc1\CPCTProjects\PMO\DART\F0" & XlYear &
"_DART\Dec DART 04\" & DartRef
xlApp.Worksheets("Top Sheet").Range("a1") = DartPth
xlApp.Workbooks.Open (DartPth), 0, ReadOnly = True
xlApp.ActiveWorkbook.Windows(1).Visible = False
End If
End If

With xlApp.Workbooks(DartRef).Worksheets(ShtRef)
.Cells.Replace What:="=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Set xlSheet = xlApp.Worksheets.Add
xlSheet.Name = ShtRef
.Cells.Copy xlApp.ActiveWorkbook.Sheets(ShtRef).Range("A1")

.Cells.Replace What:="=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End With
sName = Left(activeworkbook.Name, len(activeworkbook.Name)-4)
ActiveWorkbook.SaveAs FileName:="C:\Someplace\" &
sName & "_processed.xls"