PDA

View Full Version : Help: "Method 'SaveAs' of object'_Workbook' failed" - Tried different codes, no luck



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

SamT
11-03-2017, 03:27 PM
I made a couple of very unneeded tweaks to demonstrate some VBA Code shortcuts.

The first thing I would do is move SaveFilePath to the top as a Module level Constant.

Then I would move all "If...Then...Exit Subs" to boolean Functions.


If WS.Name = "Summary" Or WS.Name = "Audit_Tool_Options" Or WS.Name = "EndSheet" Or WS.Name = "Month" Then 'DoNothing
Else
'do it all
Could be

If Not CBool(Instr(" Summary Audit_Tool_Options EndSheet Month ", WS.Name)) Then
'do it all
End If

Wen.Satingin
11-03-2017, 03:48 PM
I made a couple of very unneeded tweaks to demonstrate some VBA Code shortcuts.

The first thing I would do is move SaveFilePath to the top as a Module level Constant.

Then I would move all "If...Then...Exit Subs" to boolean Functions.


If WS.Name = "Summary" Or WS.Name = "Audit_Tool_Options" Or WS.Name = "EndSheet" Or WS.Name = "Month" Then 'DoNothing
Else
'do it all
Could be

If Not CBool(Instr(" Summary Audit_Tool_Options EndSheet Month ", WS.Name)) Then
'do it all
End If

Thank you for your fast response.
I have moved SaveFilePath just below the Password constant and unfortunately that didn't solve the problem.
I still get the SaveAs error.

I appreciate the advice on boolean function. I'm a newbie so I didn't really know how that works. I used your code and it worked out just fine... until it got to the SaveAs code.

SamT
11-04-2017, 07:30 AM
Sample Boolean Functions:


Function CheckPassword() As Boolean

If InputBox("Enter Password", "Molina Healthcare | Utilization Management Authorization Review") _
= Password Then CheckPassword = True
End Function


Function Message_BadPassword() As Boolean
If 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 Message_BadPassword = True
End Sub


Function ShowReminder() As Boolean
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 ShowReminder = TRue
End Function

How to use in main code


If Not CheckPassword Then Exit Sub
If Not ShowReminder Then Exit Sub
If Not GetAuditYEar Then Exit Sub
If Not GetAuditMonth Then Exit Sub
If Not GetHealthPlanState the Exit Sub


I still get the SaveAs error.
SaveFilePath = "\\mhi\DavWWWRoot\sites\medicalaffairs\CPP_RD\CAM\Audit Results\2017\Audit_Summary\"
HealthPlanState = InputBox("Enter Health Plan or Depar...)
Filename:=SaveFilePath & HealthPlanState & "_" & AuditMonth & "_" & AuditYear & "_HPSummary.xlsm"

The actual "SaveAs" line looks good to me.
You might need a Drive letter in SaveFilePath

After you get the basic Boolean Functions working, expand them so they validate the InputBox's output so that a bad value can not be used in the main code. For example, you can verify that HealthPlanState is a legitimate choice.