Application Filesearch

S

Samuell

New Member
Joined
Apr 30, 2013
Messages
1
Hi there,

I have been trying to find a replacement in Excel 07 for the application filesearch macro below with limited success. Any assistance would be really appreciated. Thanks



Private Sub CommandButton1_Click()
'Macro written by Stuart Davis
EnableCalculation = False
For Each a In Worksheets
a.Visible = True
Next a
Worksheets("Instructions").Activate
Date_value = ActiveSheet.Range("d4").Value
Worksheets("Comparison").Activate
ActiveSheet.Rows("1:400").Delete
Dim c() As String
i = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)
path1 = "S:\Share Plan Services\EssShared\Marketing Share Plans All\Sharesave\Sharesave Modelling\Live Plans\"
With Application.FileSearch
.NewSearch
.LookIn = path1
.FileType = msoFileTypeExcelWorkbooks
.SearchSubFolders = False
.Filename = "*.xls"
If .Execute > 0 Then
h = .FoundFiles.Count
For g = 1 To h
ReDim Preserve c(g) As String
c(g) = Mid(.FoundFiles(g), Len(path1) + 1, Len(.FoundFiles(g)) - Len(path1) - 4)
Next g
e = 1
For d = 1 To h
If c(d) <> i Then
Workbooks.Open Filename:=path1 & c(d) & ".xls", UpdateLinks:=3, ReadOnly:=False, WriteResPassword:="openup", IgnoreReadOnlyRecommended:=True
EnableCalculation = True
Workbooks(c(d) & ".xls").Worksheets("Inputs for Model").Range("c19").Value = Date_value
EnableCalculation = False
Start_date = Workbooks(c(d) & ".xls").Worksheets("Inputs for Model").Range("d8").Value
active_months = ((Year(Date_value) - Year(Start_date)) * 12) + Month(Date_value) - Month(Start_date) + 1
Balance3 = Workbooks(c(d) & ".xls").Worksheets("Inputs for Model").Range("d19").Value
Balance5 = Workbooks(c(d) & ".xls").Worksheets("Inputs for Model").Range("e19").Value
Balance7 = Workbooks(c(d) & ".xls").Worksheets("Inputs for Model").Range("f19").Value
If Workbooks(c(d) & ".xls").Worksheets("Inputs for Model").Range("d15").Value < 0 Then
Profit3 = Workbooks(c(d) & ".xls").Worksheets("Inputs for Model").Range("d15").Value
Else
Profit3 = Workbooks(c(d) & ".xls").Worksheets("Inputs for Model").Range("d17").Value
End If
If Workbooks(c(d) & ".xls").Worksheets("Inputs for Model").Range("e15").Value < 0 Then
Profit5 = Workbooks(c(d) & ".xls").Worksheets("Inputs for Model").Range("e15").Value
Else
Profit5 = Workbooks(c(d) & ".xls").Worksheets("Inputs for Model").Range("e17").Value
End If
If Workbooks(c(d) & ".xls").Worksheets("Inputs for Model").Range("f15").Value < 0 Then
Profit7 = Workbooks(c(d) & ".xls").Worksheets("Inputs for Model").Range("f15").Value
Else
Profit7 = Workbooks(c(d) & ".xls").Worksheets("Inputs for Model").Range("f17").Value
End If
MonthlyConts3 = Workbooks(c(d) & ".xls").Worksheets("Inputs for Model").Range("d10").Value
MonthlyConts5 = Workbooks(c(d) & ".xls").Worksheets("Inputs for Model").Range("e10").Value
MonthlyConts7 = Workbooks(c(d) & ".xls").Worksheets("Inputs for Model").Range("f10").Value
For k = 0 To 84
MonthlyConts3 = MonthlyConts3 - Workbooks(c(d) & ".xls").Worksheets("Updater").Range("e4").Offset(k, 0).Value
MonthlyConts5 = MonthlyConts5 - Workbooks(c(d) & ".xls").Worksheets("Updater").Range("g4").Offset(k, 0).Value
MonthlyConts7 = MonthlyConts7 - Workbooks(c(d) & ".xls").Worksheets("Updater").Range("i4").Offset(k, 0).Value
Next k
StartConts3 = Workbooks(c(d) & ".xls").Worksheets("Inputs for Model").Range("d10").Value
StartConts5 = Workbooks(c(d) & ".xls").Worksheets("Inputs for Model").Range("e10").Value
StartConts7 = Workbooks(c(d) & ".xls").Worksheets("Inputs for Model").Range("f10").Value
Windows(c(d) & ".xls").Close SaveChanges:=False
Workbooks(i & ".xls").Worksheets("Comparison").Activate
If MonthlyConts3 > 0 Then
ActiveSheet.Range("A1").Offset(e, 0).Value = c(d)
ActiveSheet.Range("A1").Offset(e, 1).Value = "3-Year Plan"
ActiveSheet.Range("A1").Offset(e, 2).Value = Start_date
ActiveSheet.Range("A1").Offset(e, 3).Value = Balance3
ActiveSheet.Range("A1").Offset(e, 5).Formula = "=" & ActiveSheet.Range("A1").Offset(e, 4).Address & "-" & ActiveSheet.Range("A1").Offset(e, 3).Address
ActiveSheet.Range("A1").Offset(e, 7).Value = Profit3
ActiveSheet.Range("A1").Offset(e, 9).Value = MonthlyConts3
ActiveSheet.Range("A1").Offset(e, 10).Value = StartConts3
e = e + 1
End If
If MonthlyConts5 > 0 Then
ActiveSheet.Range("A1").Offset(e, 0).Value = c(d)
ActiveSheet.Range("A1").Offset(e, 1).Value = "5-Year Plan"
ActiveSheet.Range("A1").Offset(e, 2).Value = Start_date
ActiveSheet.Range("A1").Offset(e, 3).Value = Balance5
ActiveSheet.Range("A1").Offset(e, 5).Formula = "=" & ActiveSheet.Range("A1").Offset(e, 4).Address & "-" & ActiveSheet.Range("A1").Offset(e, 3).Address
ActiveSheet.Range("A1").Offset(e, 7).Value = Profit5
ActiveSheet.Range("A1").Offset(e, 9).Value = MonthlyConts5
ActiveSheet.Range("A1").Offset(e, 10).Value = StartConts5
e = e + 1
End If
If MonthlyConts7 > 0 Then
ActiveSheet.Range("A1").Offset(e, 0).Value = c(d)
ActiveSheet.Range("A1").Offset(e, 1).Value = "7-Year Plan"
ActiveSheet.Range("A1").Offset(e, 2).Value = Start_date
ActiveSheet.Range("A1").Offset(e, 3).Value = Balance7
ActiveSheet.Range("A1").Offset(e, 5).Formula = "=" & ActiveSheet.Range("A1").Offset(e, 4).Address & "-" & ActiveSheet.Range("A1").Offset(e, 3).Address
ActiveSheet.Range("A1").Offset(e, 7).Value = Profit7
ActiveSheet.Range("A1").Offset(e, 9).Value = MonthlyConts7
ActiveSheet.Range("A1").Offset(e, 10).Value = StartConts7
e = e + 1
End If
End If
Next d
e = e + 1
End If
End With

ActiveSheet.Range("A1").Value = "Grant"
ActiveSheet.Range("B1").Value = "Plan"
ActiveSheet.Range("C1").Value = "Start Date"
ActiveSheet.Range("D1").Value = "Expected Cash Balance"
ActiveSheet.Range("E1").Value = "Actual Cash Balance"
ActiveSheet.Range("F1").Value = "Difference"
ActiveSheet.Range("H1").Value = "Plan Profit"
ActiveSheet.Range("J1").Value = "Monthly Contributions"
ActiveSheet.Range("K1").Value = "Starting Contributions"

ActiveSheet.Range("A1").Offset(e, 0).Value = "Total for All Plans"
ActiveSheet.Range("A1").Offset(e, 3).Formula = "=subtotal(9,D2:D" & e - 1 & ")"
ActiveSheet.Range("A1").Offset(e, 4).Formula = "=subtotal(9,E2:E" & e - 1 & ")"
ActiveSheet.Range("A1").Offset(e, 5).Formula = "=subtotal(9,F2:F" & e - 1 & ")"
ActiveSheet.Range("A1").Offset(e, 7).Formula = "=subtotal(9,H2:H" & e - 1 & ")"
ActiveSheet.Range("A1").Offset(e, 9).Formula = "=subtotal(9,J2:J" & e - 1 & ")"
ActiveSheet.Range("A1").Offset(e, 10).Formula = "=subtotal(9,K2:K" & e - 1 & ")"
ActiveSheet.Range("D2:K" & e + 1).NumberFormat = ("£#,##0.00; [Red]-£#,##0.00")
ActiveSheet.Range("C2:C" & e + 1).NumberFormat = ("mmm-yy")
ActiveSheet.Range("D" & e + 1 & ":F" & e + 1 & ",H" & e + 1 & ",J" & e + 1 & ":K" & e + 1).Borders(xlEdgeBottom).Weight = xlThin
ActiveSheet.Range("D" & e + 1 & ":F" & e + 1 & ",H" & e + 1 & ",J" & e + 1 & ":K" & e + 1).Borders(xlEdgeTop).Weight = xlThin
With ActiveSheet.Range("A1:F1,H1,J1:K1")
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlInsideVertical).Weight = xlThin
.Font.Bold = True
.WrapText = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
ActiveSheet.Columns("A:C").AutoFit
EnableCalculation = True
End Sub
 
Last edited:

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

J
  • Question Question
Assistance changing VBA code to display as HTML or picture of a selection in Lotus Notes
Replies
2
Views
200
jularobert
J
N
  • Question Question
My code is suddenly really slow
Replies
1
Views
358
RobP
R
S
  • Solved
Working with multiple Target Ranges in Worksheet Code
Replies
3
Views
744
sspatriots
S
S
  • Question Question
Worksheet Change Event
Replies
2
Views
676
sspatriots
S
S
  • Question Question
Need code that adds multiple new rows to table to include a check box in column "L" for each new row
Replies
0
Views
534
sspatriots
S

Forum statistics

Threads
1,223,246
Messages
6,170,999
Members
452,373
Latest member
TimReeks

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top