C
Chris Freeman
This is the same request as below, but now I've attached the code being used:
Sub Funds_Ops_Reconciliation()
'
Dim lngCount As Long
Dim FO As String
Dim TA As String
Dim FundsOps As String
Dim TrustAcct As String
Dim FileName As Variant
Application.DisplayAlerts = False
Application.ScreenUpdating = False
' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Title = "Select the original Funds Ops Check Request file"
.Show
For Each FileName In .SelectedItems 'code stops here
TrustAcct = FileName
Next
End With
'Open workbooks
Workbooks.Open
FileName:="\\sandprdob01\StdReg\Escheat_TrustAcct\Escheat_Trust_out.csv"
FO = ActiveWorkbook.Name 'code stops here
Workbooks.Open FileName:=TrustAcct
TA = ActiveWorkbook.Name 'code stops here
'Create values for Funds ops File: _
Total of Serial numbers (since its a number field) _
Total of check amounts _
Count of Records
Workbooks(FO).Activate
Range("H:H").Select
RC = WorksheetFunction.Count(Range("H:H"))
CA = WorksheetFunction.Sum(Range("H:H"))
FOTotal = Format(CA, "$ #,##0.00;$ -#,##0.00")
FOCount = Format(RC, comma)
'Create values for Trust Acct File: _
Total of Serial numbers (since its a number field) _
Total of check amounts _
Count of Records
Workbooks(TA).Activate
RC = WorksheetFunction.Count(Range("F:F"))
CA = WorksheetFunction.Sum(Range("F:F"))
TATotal = Format(CA, "$ #,##0.00;$ -#,##0.00")
TACount = Format(RC, comma)
ReconTotal = FOTotal - TATotal
ReconCount = FOCount - TACount
ReconTotal = Format(ReconTotal, "$ #,##0.00;$ -#,##0.00 ")
If ReconTotal <> 0 Then
MsgBox "The reconciliation process is out of balance:" & _
vbCrLf & "Trust Acct sent checks amounting to: " & TATotal & _
vbCrLf & "Funds Ops sent checks amounting to: " & FOTotal & _
vbCrLf & "We are out of balance by " & ReconTotal & ""
End If
Workbooks(FO).Close
Workbooks(TA).Close
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Workbooks("Funds Ops Return File macro.xls").Close
End Sub
The code stops in three places everything, regardless of file selected or
changes to code. I've been streamlining code and still it stops in the same
places. The only constant is that the files are on a server, and that file
retrieval is kind of slow.
Thanks in advance for assistance
Sub Funds_Ops_Reconciliation()
'
Dim lngCount As Long
Dim FO As String
Dim TA As String
Dim FundsOps As String
Dim TrustAcct As String
Dim FileName As Variant
Application.DisplayAlerts = False
Application.ScreenUpdating = False
' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Title = "Select the original Funds Ops Check Request file"
.Show
For Each FileName In .SelectedItems 'code stops here
TrustAcct = FileName
Next
End With
'Open workbooks
Workbooks.Open
FileName:="\\sandprdob01\StdReg\Escheat_TrustAcct\Escheat_Trust_out.csv"
FO = ActiveWorkbook.Name 'code stops here
Workbooks.Open FileName:=TrustAcct
TA = ActiveWorkbook.Name 'code stops here
'Create values for Funds ops File: _
Total of Serial numbers (since its a number field) _
Total of check amounts _
Count of Records
Workbooks(FO).Activate
Range("H:H").Select
RC = WorksheetFunction.Count(Range("H:H"))
CA = WorksheetFunction.Sum(Range("H:H"))
FOTotal = Format(CA, "$ #,##0.00;$ -#,##0.00")
FOCount = Format(RC, comma)
'Create values for Trust Acct File: _
Total of Serial numbers (since its a number field) _
Total of check amounts _
Count of Records
Workbooks(TA).Activate
RC = WorksheetFunction.Count(Range("F:F"))
CA = WorksheetFunction.Sum(Range("F:F"))
TATotal = Format(CA, "$ #,##0.00;$ -#,##0.00")
TACount = Format(RC, comma)
ReconTotal = FOTotal - TATotal
ReconCount = FOCount - TACount
ReconTotal = Format(ReconTotal, "$ #,##0.00;$ -#,##0.00 ")
If ReconTotal <> 0 Then
MsgBox "The reconciliation process is out of balance:" & _
vbCrLf & "Trust Acct sent checks amounting to: " & TATotal & _
vbCrLf & "Funds Ops sent checks amounting to: " & FOTotal & _
vbCrLf & "We are out of balance by " & ReconTotal & ""
End If
Workbooks(FO).Close
Workbooks(TA).Close
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Workbooks("Funds Ops Return File macro.xls").Close
End Sub
The code stops in three places everything, regardless of file selected or
changes to code. I've been streamlining code and still it stops in the same
places. The only constant is that the files are on a server, and that file
retrieval is kind of slow.
Thanks in advance for assistance