Wen.Satingin
11-03-2017, 01:49 PM
Hello,
I am looking for an advice how to resolve the error that comes up when I run my VBA code.
The error that I get is "Method 'SaveAs' of object'_Workbook' failed."
I have tried to use different codes such as ActiveWorkbook.SaveAs, ThisWorkbook.SaveAs, or CurrentWorkbook.SaveAs but no luck.
I also made sure that I declared all the Strings.
I checked my permission level to SharePoint where I try to save the file, didn't work either. I tried to save in my desktop and it did not work either.
Any recommendations/suggestions would be helpful.
Thank you.
Here's the full stretch of code. The error line is at the bottom part, just few lines above the End Sub.
Sub Create_HPSummary()
Dim SourceFolder As String
Dim SourceFile As String
Dim SourceWB As Workbook
Dim DestinationWB As Workbook
Dim WS As Worksheet
Dim StartTime As Double
Dim HealthPlanState As String
Dim AuditYear As String
Dim AuditMonth As String
Dim SaveFilePath As String
Dim MinutesElapsed As String
StartTime = Timer
Password = "mhium200"
UserInput = InputBox("Enter Password", "Molina Healthcare | Utilization Management Authorization Review")
If UserInput <> Password Then
MsgBox "Wrong Password or Operation Cancelled", vbExclamation, "Molina Healthcare | Utilization Management Authorization Review"
Exit Sub
ElseIf MsgBox(" I M P O R T A N T R E M I N D E R S......", vbOKCancel, "Molina Healthcare | Utilization Management Authorization Review") = vbCancel Then
Exit Sub
End If
HealthPlanState = InputBox("Enter Health Plan or Department 3-Letter Code", "Molina Healthcare | Utilization Management Authorization Review")
If HealthPlanState = vbNullString Then
MsgBox "Cannot be Empty or Operation Cancelled", vbExclamation, "Molina Healthcare | Utilization Management Authorization Review"
Exit Sub
End If
AuditYear = InputBox("Required: Enter Audit Year in 4-digit format (YYYY)" & vbNewLine & vbNewLine & "For example: 2017, 2018, 2019...", "Molina Healthcare | Utilization Management Authorization Review")
If AuditYear = vbNullString Then
MsgBox "Cannot be Empty or Operation Cancelled", vbExclamation, "Molina Healthcare | Utilization Management Authorization Review"
Exit Sub
End If
AuditMonth = InputBox("Required: Enter Audit Month" & vbNewLine & "Do not use abbreviation" & vbNewLine & vbNewLine & "For example: January, February, March...", "Molina Healthcare | Utilization Management Authorization Review")
If AuditYear = vbNullString Then
MsgBox "Cannot be Empty or Operation Cancelled", vbExclamation, "Molina Healthcare | Utilization Management Authorization Review"
Exit Sub
End If
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With
Set DestinationWB = ThisWorkbook
DestinationWB.Unprotect Password
SourceFolder = "\\mhi\DavWWWRoot\sites\medicalaffairs\CPP_RD\CAM\Audit Results\" & AuditYear & "\" & HealthPlanState
SourceFile = Dir(SourceFolder & "\" & "*.xl*")
Do Until SourceFile = ""
Set SourceWB = Workbooks.Open(SourceFolder & "\" & SourceFile)
SourceWB.Unprotect Password
For Each WS In ActiveWorkbook.Sheets
If WS.Name = AuditMonth Then
WS.Copy Before:=DestinationWB.Sheets("EndSheet")
End If
Next WS
SourceWB.Close SaveChanges:=False
Application.CutCopyMode = False
SourceFile = Dir()
Loop
DestinationWB.Activate
For Each WS In ActiveWorkbook.Worksheets
ActiveWindow.DisplayHeadings = False
ActiveWindow.DisplayGridlines = False
WS.Unprotect Password
If WS.Name = "Summary" Or WS.Name = "Audit_Tool_Options" Or WS.Name = "EndSheet" Or WS.Name = "Month" Then
Else
Set SummarySheet = Worksheets("Summary")
SummarySheet.Unprotect Password
WS.Range("D5").Copy
SummarySheet.Cells(Rows.Count, "DE").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
WS.Range("W5").Copy
SummarySheet.Cells(Rows.Count, "DF").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
WS.Range("Z5").Copy
SummarySheet.Cells(Rows.Count, "DG").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
End If
Next WS
Worksheets("Summary").Range("D5").Value = HealthPlanState
Worksheets("Summary").Range("D6").Value = HealthPlanState
Worksheets("Summary").Range("M6").Value = AuditMonth
Worksheets("Summary").Range("Z73").Value = "Wt. Ave."
If Worksheets("Summary").Range("W74").Value = AuditMonth Then
Worksheets("Summary").Range("Y74").Formula = "=SUM(DF95:DF1048576)"
Worksheets("Summary").Range("Z74").Formula = "=SUMPRODUCT(DG95:DG1048576,DF95:DF1048576)/SUM(DF95D:F1048576)"
Else
End If
Worksheets("Summary").Columns("DE:DG").EntireColumn.Hidden = False
For Each WS In ActiveWorkbook.Worksheets
WS.Protect Password
Next WS
ActiveWorkbook.Protect Password, Structure:=True, Windows:=False
SaveFilePath = "\\mhi\DavWWWRoot\sites\medicalaffairs\CPP_RD\CAM\Audit Results\2017\Audit_Summary\"
ActiveWorkbook.SaveAs Filename:=SaveFilePath & HealthPlanState & "_" & AuditMonth & "_" & AuditYear & "_HPSummary.xlsm", FileFormat:=52
Set SourceWB = Nothing
Set DestinationWB = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
End With
MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
MsgBox "Health Plan Audit_Summary was completed in " & vbNewLine & vbNewLine & " " & MinutesElapsed & " minutes.", vbInformation, "Molina Healthcare | Utilization Management Authorization Review"
End Sub
I am looking for an advice how to resolve the error that comes up when I run my VBA code.
The error that I get is "Method 'SaveAs' of object'_Workbook' failed."
I have tried to use different codes such as ActiveWorkbook.SaveAs, ThisWorkbook.SaveAs, or CurrentWorkbook.SaveAs but no luck.
I also made sure that I declared all the Strings.
I checked my permission level to SharePoint where I try to save the file, didn't work either. I tried to save in my desktop and it did not work either.
Any recommendations/suggestions would be helpful.
Thank you.
Here's the full stretch of code. The error line is at the bottom part, just few lines above the End Sub.
Sub Create_HPSummary()
Dim SourceFolder As String
Dim SourceFile As String
Dim SourceWB As Workbook
Dim DestinationWB As Workbook
Dim WS As Worksheet
Dim StartTime As Double
Dim HealthPlanState As String
Dim AuditYear As String
Dim AuditMonth As String
Dim SaveFilePath As String
Dim MinutesElapsed As String
StartTime = Timer
Password = "mhium200"
UserInput = InputBox("Enter Password", "Molina Healthcare | Utilization Management Authorization Review")
If UserInput <> Password Then
MsgBox "Wrong Password or Operation Cancelled", vbExclamation, "Molina Healthcare | Utilization Management Authorization Review"
Exit Sub
ElseIf MsgBox(" I M P O R T A N T R E M I N D E R S......", vbOKCancel, "Molina Healthcare | Utilization Management Authorization Review") = vbCancel Then
Exit Sub
End If
HealthPlanState = InputBox("Enter Health Plan or Department 3-Letter Code", "Molina Healthcare | Utilization Management Authorization Review")
If HealthPlanState = vbNullString Then
MsgBox "Cannot be Empty or Operation Cancelled", vbExclamation, "Molina Healthcare | Utilization Management Authorization Review"
Exit Sub
End If
AuditYear = InputBox("Required: Enter Audit Year in 4-digit format (YYYY)" & vbNewLine & vbNewLine & "For example: 2017, 2018, 2019...", "Molina Healthcare | Utilization Management Authorization Review")
If AuditYear = vbNullString Then
MsgBox "Cannot be Empty or Operation Cancelled", vbExclamation, "Molina Healthcare | Utilization Management Authorization Review"
Exit Sub
End If
AuditMonth = InputBox("Required: Enter Audit Month" & vbNewLine & "Do not use abbreviation" & vbNewLine & vbNewLine & "For example: January, February, March...", "Molina Healthcare | Utilization Management Authorization Review")
If AuditYear = vbNullString Then
MsgBox "Cannot be Empty or Operation Cancelled", vbExclamation, "Molina Healthcare | Utilization Management Authorization Review"
Exit Sub
End If
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With
Set DestinationWB = ThisWorkbook
DestinationWB.Unprotect Password
SourceFolder = "\\mhi\DavWWWRoot\sites\medicalaffairs\CPP_RD\CAM\Audit Results\" & AuditYear & "\" & HealthPlanState
SourceFile = Dir(SourceFolder & "\" & "*.xl*")
Do Until SourceFile = ""
Set SourceWB = Workbooks.Open(SourceFolder & "\" & SourceFile)
SourceWB.Unprotect Password
For Each WS In ActiveWorkbook.Sheets
If WS.Name = AuditMonth Then
WS.Copy Before:=DestinationWB.Sheets("EndSheet")
End If
Next WS
SourceWB.Close SaveChanges:=False
Application.CutCopyMode = False
SourceFile = Dir()
Loop
DestinationWB.Activate
For Each WS In ActiveWorkbook.Worksheets
ActiveWindow.DisplayHeadings = False
ActiveWindow.DisplayGridlines = False
WS.Unprotect Password
If WS.Name = "Summary" Or WS.Name = "Audit_Tool_Options" Or WS.Name = "EndSheet" Or WS.Name = "Month" Then
Else
Set SummarySheet = Worksheets("Summary")
SummarySheet.Unprotect Password
WS.Range("D5").Copy
SummarySheet.Cells(Rows.Count, "DE").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
WS.Range("W5").Copy
SummarySheet.Cells(Rows.Count, "DF").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
WS.Range("Z5").Copy
SummarySheet.Cells(Rows.Count, "DG").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
End If
Next WS
Worksheets("Summary").Range("D5").Value = HealthPlanState
Worksheets("Summary").Range("D6").Value = HealthPlanState
Worksheets("Summary").Range("M6").Value = AuditMonth
Worksheets("Summary").Range("Z73").Value = "Wt. Ave."
If Worksheets("Summary").Range("W74").Value = AuditMonth Then
Worksheets("Summary").Range("Y74").Formula = "=SUM(DF95:DF1048576)"
Worksheets("Summary").Range("Z74").Formula = "=SUMPRODUCT(DG95:DG1048576,DF95:DF1048576)/SUM(DF95D:F1048576)"
Else
End If
Worksheets("Summary").Columns("DE:DG").EntireColumn.Hidden = False
For Each WS In ActiveWorkbook.Worksheets
WS.Protect Password
Next WS
ActiveWorkbook.Protect Password, Structure:=True, Windows:=False
SaveFilePath = "\\mhi\DavWWWRoot\sites\medicalaffairs\CPP_RD\CAM\Audit Results\2017\Audit_Summary\"
ActiveWorkbook.SaveAs Filename:=SaveFilePath & HealthPlanState & "_" & AuditMonth & "_" & AuditYear & "_HPSummary.xlsm", FileFormat:=52
Set SourceWB = Nothing
Set DestinationWB = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
End With
MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
MsgBox "Health Plan Audit_Summary was completed in " & vbNewLine & vbNewLine & " " & MinutesElapsed & " minutes.", vbInformation, "Molina Healthcare | Utilization Management Authorization Review"
End Sub