Will not open workbook

[CrispyAsian]

Hey all,

Having a small issue with my vba code. The code is supposed to throw up a bunch of prompts to confirm saving and then clears out the forms and vba codes and then is supposed to open the new file and then closes the application. However, no matter where I put the Workbooks.open line, it will not open the new workbook. Currently I have it looking like this:

Private Sub ExportButton()Dim answer As Integer
    answer = MsgBox("Are you sure you want to export RAP?  Document will close without saving after export.", vbYesNo + vbQuestion, "Empty Sheet")
    If answer = vbYes Then
    Dim i As Integer
    If ActiveSheet.ProtectContents = True Then
        MsgBox "The Current Workbook or the Worksheets which it contains are protected." & vbLf & "Please resolve these issues and try again."
    End If
    
    On Error Resume Next
    For i = 1 To 10000
        ActiveWorkbook.Sheets(i).Buttons.Delete
    Next i
    
    On Error Resume Next
    Dim Element As Object
    For Each Element In ActiveWorkbook.VBProject.VBComponents
        ActiveWorkbook.VBProject.VBComponents.Remove Element
    Next
Dim save_as As Variant
Dim file_name As String
Dim ProgramName As String
file_name = ProgramName
    ' Get the file name.
    save_as = Application.GetSaveAsFilename(file_name, _
        FileFilter:="Excel Files,*.xlsx,All Files,*.*")
    ' See if the user canceled.
    If save_as = False Then Exit Sub
    ' Save the file with the new name.
    Application.DisplayAlerts = False
    If LCase$(Right$(save_as, 4)) <> ".xlsx" Then
        file_name = save_as & ".xlsx"
    End If
    ActiveWorkbook.SaveAs Filename:=save_as, FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
    MsgBox ("Document Successfully Exported!")
    
    Application.Quit
    Application.ActiveWindow.Close SaveChanges:=False
    ActiveWorkbook.Close SaveChanges:=False
    
    Application.Workbooks.Open Filename:=save_as & ".xlsx"
    
    Else
        'do nothing
    End If
    
End Sub

I have no idea where to put the workbooks.open line to make it work or what I'm doing wrong. Anybody have any ideas?

 

[JoeMo]

Not sure I understand what you want to do, but I would think you want the Open line before you quit the excel application. Maybe move the Application.Quit line to just after the Open line.

Also, if save_as includes the file extension, then on the Open line you want:

Filename:=save_as

not

Filename:=save_as & ".xlsx"

 

[CrispyAsian]

Thanks for the suggestions. So all I'm trying to do is fix my code so the newly created workbook that is saved in the code will open before closing the current workbook. I moved the Workbooks.Open line as you suggested and used file_name as it incorporates the ".xlsx" in it already. Didn't change anything. Still will not open the new workbook. Code looks like this now:

Private Sub ImportMasterButton()
Dim ws As Worksheet
For Each ws In Worksheets
    If ws.Name = "Master" Then
        Application.DisplayAlerts = False
        Sheets("Master").Delete
        Application.DisplayAlerts = True
    End If
Next
Dim wb1 As Workbook, wb2 As Workbook, wbNm As String
Set wb1 = Workbooks("330th FY16 RAP.xlsm")
Set wb2 = Workbooks.Open("L:\ERM\OPS\DOT\03-Office Administrative Files\03-01-RAP Management\AS17\RAP Remaining by Position 330 CTS.xlsx")
On Error GoTo 0
    For Each Sh In wb2.Sheets
         Sh.Copy After:=wb1.Sheets(wb1.Sheets.Count)
    Next
    
Sheets("Sheet1").Name = "Master"
Workbooks("RAP Remaining by Position 330 CTS.xlsx").Close
    With Sheets("Master")
        .Cells.Font.Name = "Arial"
        .Cells.Font.Size = 10
    End With
    
ActiveSheet.Buttons.Add(Range("E1").Left, Range("E1").Top, 130, 25).Select
    Selection.Name = "Master Import"
    Selection.OnAction = "ImportMasterButton"
    Selection.Caption = "Import Master Data"
    
 ActiveSheet.Buttons.Add(Range("I1").Left, Range("I1").Top, 130, 25).Select
    Selection.Name = "Export and Delete"
    Selection.OnAction = "ExportButton"
    Selection.Caption = "Export RAP (Save first!)"
    
End Sub
Private Sub ImportOverallButton()
Dim ws As Worksheet
For Each ws In Worksheets
    If ws.Name = "Overall" Then
        Application.DisplayAlerts = False
        Sheets("Overall").Delete
        Application.DisplayAlerts = True
    End If
Next
Dim wb1 As Workbook, wb2 As Workbook, wbNm As String
Set wb1 = Workbooks("330th FY16 RAP.xlsm")
Set wb2 = Workbooks.Open("L:\ERM\OPS\DOT\03-Office Administrative Files\03-01-RAP Management\AS17\Overall RAP Review 330 CTS.xlsx")
On Error GoTo 0
    For Each Sh In wb2.Sheets
         Sh.Copy Before:=wb1.Sheets(wb1.Sheets.Count)
    Next
    
Sheets("Sheet1").Move Before:=Sheets("Pilot")
    
Sheets("Sheet1").Name = "Overall"
Workbooks("Overall RAP Review 330 CTS.xlsx").Close
ActiveSheet.Buttons.Add(Range("E1").Left, Range("E1").Top, 130, 25).Select
    Selection.Name = "Overall Import"
    Selection.OnAction = "ImportOverallButton"
    Selection.Caption = "Import Overall Data"
End Sub
Private Sub ExportButton()
Dim answer As Integer
    answer = MsgBox("Are you sure you want to export RAP?  Document will close without saving after export.", vbYesNo + vbQuestion, "Empty Sheet")
    If answer = vbYes Then
    Dim i As Integer
    If ActiveSheet.ProtectContents = True Then
        MsgBox "The Current Workbook or the Worksheets which it contains are protected." & vbLf & "                          Please resolve these issues and try again."
    End If
    
    On Error Resume Next
    For i = 1 To 10000
        ActiveWorkbook.Sheets(i).Buttons.Delete
    Next i
    
    On Error Resume Next
    Dim Element As Object
    For Each Element In ActiveWorkbook.VBProject.VBComponents
        ActiveWorkbook.VBProject.VBComponents.Remove Element
    Next
Dim save_as As Variant
Dim file_name As String
Dim ProgramName As String
file_name = ProgramName
    ' Get the file name.
    save_as = Application.GetSaveAsFilename(file_name, _
        FileFilter:="Excel Files,*.xlsx,All Files,*.*")
    ' See if the user canceled.
    If save_as = False Then Exit Sub
    ' Save the file with the new name.
    Application.DisplayAlerts = False
    If LCase$(Right$(save_as, 4)) <> ".xlsx" Then
        file_name = save_as & ".xlsx"
    End If
    ActiveWorkbook.SaveAs Filename:=save_as, FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
    MsgBox ("Document Successfully Exported!")
    
    Application.Workbooks.Open Filename:=file_name
    Application.Quit
    Application.ActiveWindow.Close SaveChanges:=False
    ActiveWorkbook.Close SaveChanges:=False
    
    
    Else
        'do nothing
    End If
    
End Sub

Any other ideas?

 

[CrispyAsian]

Oops, posted extra codes by accident. Sorry the others are fine, this is the one I'm having the issue with:

Private Sub ExportButton()
Dim answer As Integer
    answer = MsgBox("Are you sure you want to export RAP?  Document will close without saving after export.", vbYesNo + vbQuestion, "Empty Sheet")
    If answer = vbYes Then
    Dim i As Integer
    If ActiveSheet.ProtectContents = True Then
        MsgBox "The Current Workbook or the Worksheets which it contains are protected." & vbLf & "Please resolve these issues and try again."
    End If
    
    On Error Resume Next
    For i = 1 To 10000
        ActiveWorkbook.Sheets(i).Buttons.Delete
    Next i
    
    On Error Resume Next
    Dim Element As Object
    For Each Element In ActiveWorkbook.VBProject.VBComponents
        ActiveWorkbook.VBProject.VBComponents.Remove Element
    Next
Dim save_as As Variant
Dim file_name As String
Dim ProgramName As String
file_name = ProgramName
    ' Get the file name.
    save_as = Application.GetSaveAsFilename(file_name, _
        FileFilter:="Excel Files,*.xlsx,All Files,*.*")
    ' See if the user canceled.
    If save_as = False Then Exit Sub
    ' Save the file with the new name.
    Application.DisplayAlerts = False
    If LCase$(Right$(save_as, 4)) <> ".xlsx" Then
        file_name = save_as & ".xlsx"
    End If
    ActiveWorkbook.SaveAs Filename:=save_as, FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
    MsgBox ("Document Successfully Exported!")
    
    Application.Workbooks.Open Filename:=file_name
    Application.Quit
    Application.ActiveWindow.Close SaveChanges:=False
    ActiveWorkbook.Close SaveChanges:=False
    
    
    Else
        'do nothing
    End If
    
End Sub

 

[JoeMo]

The workbook may open, but then you immediately close Excel. What's the point of that?

 

[CrispyAsian]

You're right. I need a work-around for not using that line or some to prevent closing the new workbook. Originally, I put that line in to get rid of the excel window after it closed the ActiveWorkbook, but even without the Application.Quit it still doesn't open the new workbook. Is the ActiveWorkbook.Close closing the new before I can ever see it? Because it still successfully closes the original workbook I'm intending to close. Or is there something else entirely that I'm doing wrong?

 

[JoeMo]

Step through the code using the F8 key to see if the workbook is actually opening. Note: if you open a workbook, it automatically becomes the active workbook. Keep this in mind for the lines that follow the Open line.

 

[CrispyAsian]

So I took out the application.quit statement and stepped through the code. It never actually opens the new workbook. I've racked my brain and I just can't understand why it won't open...

 

[CrispyAsian]

So I figured out the simpliest work around which was to get rid of the Application.ActiveWindow.Close SaveChanges:=False and ActiveWorkbook.Close SaveChanges:=False lines and replaced it with Workbooks("filenamegoeshere.xlsm").close SaveChanges:=False and works like a charm. Unfortunately now I have an even weirder anomaly happening. If I "view code" in the newly opened workbook, all the vba coding for every sheet is still in there. However if I close Excel and reopen the new workbook, it's all gone. Anybody have any ideas why that's happening and how to make the code go away?