I just switched to Excel 2007 & getting Run time error 438 at bold & underlined statement below, Can any one help me for solution.
VBA Code:
PTH2 = Left(pth, InStr(1, pth, "CCARI") + 5) & "\SiteWise Workings\" pth3 = Left(pth, InStr(1, pth, "CCARI") + 5) & "\" & ActiveWorkbook.Name If Dir(pth3) = "" Then msg = MsgBox(PTH2 & vbCr & "is Invalid Path/Folder for this report" & vbCr & "The program requires this file to be loaded from path like \..CCARI\SiteWise Workings\", vbOKOnly, "GMS Finance Automation") Exit Sub End If
Range("B2") = Left(PTH2, Len(PTH2) - 1)
Range("A3") = "" Range("B3") = "" Range("a4:B3000").ClearContents Dim listoffiles As Object
Set fs = Application.FileDialog(msoFileDialogSaveAs) With fs
.LookIn = PTH2 .FileName = "*.xls" .SearchSubFolders = True If .Execute > 0 Then X = 1 For i = 1 To .FoundFiles.Count
myfile = .FoundFiles(i) If InStr(1, myfile, "OtherFiles") = 0 Then Sheets("FilesList").Range("B3").Offset(X, 0).Value = Mid(myfile, Len(PTH2), 200) If Left(Sheets("FilesList").Range("B3").Offset(X, 0).Value, 1) <> "\" Then Sheets("FilesList").Range("B3").Offset(X, 0).Value = "\" & Sheets("FilesList").Range("B3").Offset(X, 0).Value End If X = X + 1 End If Next i
The object methods are different in Excel 2007. In this case .LookIn should be changed to: VBA Code:
.InitialFileName = PTH2
Nevertheless, you will likely encounter other invalid object methods further in the code.
The object members for the FileDialog object in Excel 2007 are: Methods: Execute, Show Properties: AllowMultiSelect, AllowMultiSelect, Application, ButtonName, Creator, DialogType, FilterIndex, Filters, InitialFileName, InitialView, Item, Parent, SelectedItems, Title
