Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error '1004'
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error '1004'
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 -- HTH... Jim Thomlinson "Ronster" wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run time error 1004 general odbc error excel 2003 vba | Excel Programming | |||
Run Time Error 1004: Application or Object Defined Error | Excel Programming | |||
Run Time 1004 Error: Application or Object Difine Error | Excel Programming | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
Application Run Time Error 1004 and Stack Error | Excel Programming |