Heya everyone,
I have been having problems with a script and hoped that someone could help.
The script below scans through a specified folder on a network and scans every xls file inside that folder for a value. When it finds the spreadsheet which contains that value (a policy number) it outputs some of the data from the matching file and also provides a link to the sheet in question.
It worked perfectly until I saved, exited and re-opened the file and now I am getting a 1004 run-time error every time I try and execute the script.
I cant for the life of me work out a cause.
More baffling, the wording of the error says that the file 'R:\Dir\Cancellations.xls' could not be found.
At no point is a file called 'Cancellations.xls' specified. Why has the script decided to search for a file called 'Cancellations'?
Baffling,
Thanks in advance,
DomV
I have been having problems with a script and hoped that someone could help.
The script below scans through a specified folder on a network and scans every xls file inside that folder for a value. When it finds the spreadsheet which contains that value (a policy number) it outputs some of the data from the matching file and also provides a link to the sheet in question.
It worked perfectly until I saved, exited and re-opened the file and now I am getting a 1004 run-time error every time I try and execute the script.
I cant for the life of me work out a cause.
More baffling, the wording of the error says that the file 'R:\Dir\Cancellations.xls' could not be found.
At no point is a file called 'Cancellations.xls' specified. Why has the script decided to search for a file called 'Cancellations'?
Baffling,
Thanks in advance,
DomV
Code:
Sub Search()
ActiveSheet.unprotect
ActiveWorkbook.unprotect
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
Dim DirLoc As String
Dim PolNum As String
ActiveSheet.Range("C13:G25").ClearContents
Range("E18").FormulaR1C1 = " Trawling..."
Range("E18").Font.Bold = True
DirLoc = Workbooks("Computrawl.xls").Sheets("Computrawl").Range("E29")
PolNum = Workbooks("Computrawl.xls").Sheets("Computrawl").Range("E9")
Application.ScreenUpdating = False
If Range("E9") = "" Then
Range("E18").FormulaR1C1 = "Please Insert Policy No."
Range("E18").Font.Bold = True
ActiveSheet.unprotect
ActiveWorkbook.unprotect
Exit Sub
End If
MyPath = "R:\" & DirLoc 'path of folder containing xls files
ChDir MyPath
TheFile = Dir("*.xls") 'for all .xls files
Do While TheFile <> ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
Windows(TheFile).Activate
If Range("B3").Value = PolNum Then
Range("A4:D15").Copy
Windows("Computrawl.xls").Activate
Range("C13").PasteSpecial xlPasteValues
Windows(TheFile).Activate
Application.CutCopyMode = False
Workbooks("Computrawl.xls").Sheets("Computrawl").Range("C25").Value = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
Workbooks("Computrawl.xls").Activate
Sheets("Computrawl").Range("C25").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=Range("C25").Value, TextToDisplay:=Range("C25").Value
End If
wb.Close SaveChanges:=False 'Closes the open file without saving
TheFile = Dir
Loop
Windows("Computrawl.xls").Activate
If Range("C13").Value = "" Then
Range("C13:G25").Select
ActiveSheet.Range("C13:G25").ClearContents
Range("E18").FormulaR1C1 = "No Records."
Selection.Font.Bold = True
End If
Range("A1").Activate
Application.ScreenUpdating = True
ActiveSheet.protect
ActiveWorkbook.protect
End Sub