Save as Macro-Enabled File with VBA without Opening New File

[JustJerry2C]

Hi Everyone,

I have a file that I keep updating. I've added new coding which I've pieced together from here and now need to do a SaveAs function for Macro enabled. However, since trying to do this, the 'new' file keeps wanting to open, and I don't want that, as I am looping through an array to save multiple files. Before I had all the coding, this used to work just fine. Is there a way to save a file macro-enabled without it opening automatically? My ACTIVEWORKBOOK.CLOSE is not working. (Note: I previously was able to save with the .xlsx extension)

Not sure what I'm missing. I found the FileFormat:=52 here, which helped create the new file.

For i = 1 To cntPrint   'Set Counter to cycle through all students
            
            If Range(SID) = "Y" Or Range(SID) = "y" Then
                For Each wsc In Worksheets
                    If wsc.CodeName = "S" & i & "ReportCard" Then
                        Set myrws = wsc
                        Exit For
                    End If
                Next wsc
                For Each wsc In Worksheets
                    If wsc.CodeName = "S" & i & "CheckList" Then
                        Set mycws = wsc
                        Exit For
                    End If
                Next wsc
                
                cntStudent = cntStudent + 1
                txtSheet = myrws.Name   'Assign sheet1 selected name to use for file name on next line
                strSaveName = OutputFolderName & ControlSheet.Range("B" & SIDN).Value & " " & ControlSheet.Range("C" & SIDN).Value
                mycws.Select (False)
                txtSheet2 = mycws.Name  'Assign sheet2 with selected checklist sheet name
                                
                Sheets(Array(txtSheet, txtSheet2, txtSheet3, txtSheet4, txtSheet5, txtSheet6)).Copy
                Worksheets(4).Visible = xlSheetHidden
                Worksheets(3).Visible = xlSheetHidden
                Worksheets(2).Visible = xlSheetHidden
                Worksheets(1).Visible = xlSheetHidden
                ActiveWorkbook.SaveAs strSaveName, FileFormat:=52  'Save new file
                ActiveWorkbook.Close   'close new file
            End If
            
            'Advance to Next Student
            SIDN = SIDN + 1
            
            SID = "D" & SIDN
            SIDB = "B" & SIDN
            SIDC = "C" & SIDN
            
        Next i

 

[rlv01]

One possibility is that instead of this

            ActiveWorkbook.SaveAs strSaveName, FileFormat:=52  'Save new file
            ActiveWorkbook.Close   'close new file

use this

    ActiveWorkbook.SaveCopyAs Filename:=strSaveName

 

[JustJerry2C]

riv01, I was just looking at a thread about that very thing. The only thing I'd have to figure out is if I can hide all the sheets that don't need to be seen for each file. Each file is for a student, and as you can see from my original code, it hides four sheets that need to be in each file, and the only sheets visible are the report card and checklist for each student. The hidden sheets contain all the data the visible sheets need to view properly.

I believe my problem started when I added a module for each sheet to refer to which was to make it impossible for someone to delete or insert sheets. Once I did this, my code stopped working and I've been looking for a solution.

Not only that, since the Module itself needs to be part of the new file, that is causing me issues as well, and it appears copying a module from one workbook to another is not a simple task from what I've found so far.

Thanks for the reply

 

[rlv01]

Note that if you use .SaveCopyAs, you don't necessarily need to have this statement any more:

Sheets(Array(txtSheet, txtSheet2, txtSheet3, txtSheet4, txtSheet5, txtSheet6)).Copy

Which solves your problem of copying the code module(s). You don't have to since you are saving a copy of your current workbook including all forms and CODE modules. But that's ALSO going to save all worksheets to the WB copy, not just txtSheet, txtSheet2, txtSheet3, txtSheet4, txtSheet5, txtSheet6. Only you know whether that's acceptable. If you want certain sheets to be hidden, you just need to programatically hide them before the .SaveCopyAs operation then unhide them again when you are done saving. If you want to make it more difficult (but not impossible) for your students to unhide the hidden sheets, then instead of this

Worksheets(4).Visible = xlSheetHidden
Worksheets(3).Visible = xlSheetHidden
Worksheets(2).Visible = xlSheetHidden
Worksheets(1).Visible = xlSheetHidden

Use this

Worksheets(4).Visible = xlSheetVeryHidden
Worksheets(3).Visible = xlSheetVeryHidden
Worksheets(2).Visible = xlSheetVeryHidden
Worksheets(1).Visible = xlSheetVeryHidden

also, if you just want to prevent students from adding or deleting sheets, why not keep it simple and protect the workbook structure with a password? Again, this is something you can apply w/VBA before saving, and remove afterwords.

 

[JustJerry2C]

Ok, I am trying to figure out the best way to hide the sheets before doing what you suggested. I came up with the following code:

For Each wsc In Worksheets
                    If wsc.CodeName = "S" & i & "ReportCard" Or wsc.CodeName = "S" & i & "CheckList" Then
                        GoTo Line10:
                    Else
                        Set myrws = wsc
                        myrws.Visible = xlSheetHidden
                    End If
Line10:
                Next wsc

The first five sheets pertain to the grading of all the students, then there are two sheets for each student following these. I have enough sheets for 33 students.

I came up with the code above to cycle through all the sheets and hide everything but the 'Report Card' sheet and 'Checklist' sheet for the currently selected student. This code will hide the very first sheet, then I get a Run-time error '1004' Method Visible of Object _Worksheet failed.

If you have any suggestions, I'll gladly try them. I will look at this later today when I have more free time.

Thank you

 

[rlv01]

Is there a reason you are using the worksheet's codename instead of just the name? When I have to hide some sheets and show others, I like to use the Select Case statement.

    For Each wsc In Worksheets
        With wsc
            .Visible = xlSheetVisible
            Set myrws = Nothing
            Set mycws = Nothing
            Select Case .Name   'or codename
            Case "S" & I & "ReportCard"
                Set myrws = wsc
            Case "S" & I & "CheckList"
                Set mycws = wsc
            Case Else
                .Visible = xlSheetHidden
            End Select
        End With
    Next wsc


    If myrws Is Nothing Then
        MsgBox "Student 'Report Card' sheet not found", vbExclamation
        Exit Sub
    End If


    If mycws Is Nothing Then
        MsgBox "Student 'Checklist' sheet not found", vbExclamation
        Exit Sub
    End If

 

[JustJerry2C]

Hi, and thank you for the suggestion. I will try this as soon as I can, but wanted to answer your question about the codename. In the year I've been making this work, and changing or updating it, I came across the codename suggestion. Reason is, the first sheet has cells where they enter student names, and when the names are entered, the sheets are automatically renamed to match all the students, so the sheet names can always be changing. And, I was trying to find an easy way (current coding as it stands now) to be able to create my files with the two student sheets IF a teacher moved the sheets out of order by accident. My coding used to be dependent on the sheets remaining in the exact order, so that was eliminated by using the codename, of which I wanted to name so they were easily identified.....if that makes sense. It all worked well until I added the coding to prevent the sheets from being deleted, and I think you've got me all straightened out on that as soon as my feeble brain wraps around it!

 

[JustJerry2C]

Riv - I tried your code and was getting the same error I mentioned before. I then got to thinking about the code I had put in to prevent sheets from accidentally being deleted. Side note - Protecting the workbook structure caused me issues, and I tired protecting the workbook with this new code you gave me and it won't work either. Anyway, I deleted the code in the module, and the code that appears on each sheet that pertained to the 'delete' process, and this worked perfectly. Except, I had to eliminate this:

    If myrws Is Nothing Then
        MsgBox "Student 'Report Card' sheet not found", vbExclamation
        Exit Sub
    End If


    If mycws Is Nothing Then
        MsgBox "Student 'Checklist' sheet not found", vbExclamation
        Exit Sub
    End If

Every time I tried to run the code, it would come back saying the "ReportCard SHeet" not found. Once I negated this part of your code, the new file was created. I then added code to make visible all sheets, and then tried doing this same thing with two students, and it worked perfectly. It seems I just can't have the code to prevent sheets from being deleted.

In case you are wondering, here is the code I found that I put into a module for the Delete issue:

Sub UnprotectBook()
ThisWorkbook.Unprotect
End Sub

Then, at the top of each sheet I had the following code:

Private Sub Worksheet_Deactivate()
ThisWorkbook.Protect , True
Application.OnTime Now, "UnprotectBook"
End Sub