Run-time error '1004'

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ronster - 24 Sep 2007 18:21 GMT

I'm trying to rename a list of files held in an excel workbook. Column
A (a named range called "oldplanfile") holds part of the current file
name. Column B holds the corresponding part of the new file name.
Running the code traps the first error but throws up a warning ("Run-
time error '1004': <SourceBook> could not be found.") when it hits a
second missing file.

Any suggestions/help would be appreciated.

Cheers
R

################################################
Sub RenameLogFiles ()

Dim filepath, ListFile, SourceBook, OutputBook As String
filepath = "C:\Contact Log\"
ListFile = "C:\Contact Log\RA LogFiles.xls"
LogList = "RA LogFiles.xls"

Windows(LogList).Activate
Sheets("Sheet1").Activate
Range("oldplanfile").Select
On Error GoTo ErrHandle

Do
SourceBook = filepath & ActiveCell & ".xls"
OutputBook = filepath & ActiveCell.Offset(0, 1) & ".xls"
Workbooks.Open (SourceBook), UpdateLinks:=0

With ActiveWorkbook
.SaveAs (OutputBook)
.Close
End With

ResumePoint:

ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Value)

ErrHandle:
If Err.Number = 1004 Then
Windows(LogList).Activate
Sheets("ErrLog").Select
ActiveCell.Offset(R1, C0).Select
ActiveCell.Formula = "ERROR - " & SourceBook & " not found"
Sheets("Sheet1").Select
MsgBox (SourceBook & " not found - Details recorded on Errlog - Click
OK to resume macro")
Err.Clear

GoTo ResumePoint

Else
MsgBox (Err.Number & " " & Err.Description)
Exit Sub
End If

Windows(LogList).Activate
Sheets("ErrLog").Select

On Error GoTo 0
End Sub

Reply to this Message

Jim Thomlinson - 24 Sep 2007 18:54 GMT

Give something more likt this a try...

Sub RenameLogFiles ()
dim wbk as workbook
Dim filepath, ListFile, SourceBook, OutputBook As String
filepath = "C:\Contact Log\"
ListFile = "C:\Contact Log\RA LogFiles.xls"
LogList = "RA LogFiles.xls"

Windows(LogList).Activate
Sheets("Sheet1").Activate
Range("oldplanfile").Select
On Error GoTo ErrHandle

Do
set wbk = nothing
SourceBook = filepath & ActiveCell & ".xls"
OutputBook = filepath & ActiveCell.Offset(0, 1) & ".xls"
on error resume next
set wbk = Workbooks.Open (SourceBook), UpdateLinks:=0
On Error GoTo ErrHandle

if wbk is nothing then
Windows(LogList).Activate
Sheets("ErrLog").Select
ActiveCell.Offset(R1, C0).Select
ActiveCell.Formula = "ERROR - " & SourceBook & " not found"
Sheets("Sheet1").Select
MsgBox (SourceBook & " not found - Details recorded on Errlog - Click
OK to resume macro")
else
With wbk
..SaveAs (OutputBook)
..Close
End With
end if

ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Value)

Windows(LogList).Activate
Sheets("ErrLog").Select
exit sub

ErrHandle:
MsgBox (Err.Number & " " & Err.Description)
Exit Sub
End If

End Sub

Also note that most of your variables are of type variant and not string as
you probably believe. Check out this link...
http://www.cpearson.com/excel/variables.htm

Signature

HTH...

Jim Thomlinson

> I'm trying to rename a list of files held in an excel workbook. Column
> A (a named range called "oldplanfile") holds part of the current file
[quoted text clipped - 59 lines]
> On Error GoTo 0
> End Sub

Reply to this Message