How do I automate macros to be placed in many workbooks that won't be on my PC

[K0st4din]

Hello everyone,
I have read many things on the internet, but I could not find a solution to my problem. I don't know if there will ever be a macro that can solve my problem. In one workbook I have 28 worksheets. then with a macro I separate 14 workbooks with 2 worksheets in each workbook. I have 2 macros in vba, the 1st is in Thisworkbook, the 2nd is in Module. This is where my problem starts: I transfer these two macros in all 14 workbooks with copy/paste, which totally kills me. Because these macros make it so that they put restrictions on copying, printing, etc., and the 2nd one after certain days totally deletes the workbook. So, if it's just for my excel, I read how to do things, like what file to always have the macros, but in this case, as you can see, it's about 14 workbooks that must have these restrictions, so that different users cannot do anything with the files. I read that there was a way to make a macro or something that was sent to every single user and when they installed it, the macros were implemented in their excels. But that's not a solution because firstly they won't be able to handle it and secondly there's no way I can be sure they will. So my dilemma remains - how can I as quickly as possible put these macros into each workbook and then be able to send the files to the specific people. I hope I have explained well what I am trying to automate. Of course I remain available if I need to explain anything further. And I'm really hoping for some help because I'm desperate to copy and paste every month. Thanks in advance!

 

[Edgar_]

Hello,
you mean to keep them like this, this way.
Are we continuing with the idea that I have taken out all 14 workbooks ....?
View attachment 108459

Once you have exported all your workbooks and they're ready to receive modules and code, put them in a folder, regardless of how many you have. Use that folder path in this line:
Set folder = GetAFolder("C:\path\to\your\folder")
You said you had a module that you were adding to all your files. Write the name of the module in this line, keep the .bas extension.
ThisWorkbook.VBProject.VBComponents("someModule").Export ThisWorkbook.Path & "\someModule.bas"

You said you were writing sheet code to the worksheets of the exported file. Write the name of the worksheet that has that code in the parent file in this line:
With ThisWorkbook.VBProject.VBComponents("sheetNameWhereYouHaveSheetCode").CodeModule

You said you had code in your ThisWorkbook module that you were copying to the exported files. The procedure will take care of that.

Test it and let me know what happens.

 

[K0st4din]

I don't think I understand it.
Just so there's no confusion, here's what I did.
I made a brand new excel file. In it I put the 2 modules and the macro in ThisWorkbook, I also put your macro (in which I put the path where the 14 files are located and the name of the new file in which the necessary macros are located) - is this how I should do it, because in the main workbook i have a few more modules with macros and i'm worried that it will copy everything, even the unnecessary ones?
However, when I'm already at the finish, I make a button to attach and activate your macro, but I get 2 options and I don't know which one to activate.

in ThisWorkbook
' Code of ThisWorkbook module

Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
   MsgBox "Please print via [Print] or [Pdf] button"
  Cancel = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  MsgBox "Please save via [Save] button"
  Cancel = SaveAsUI
End Sub

Sub SaveButton()
  Application.EnableEvents = False
  Me.SaveAs Me.Name, FileFormat:=xlOpenXMLWorkbookMacroEnabled
  Application.EnableEvents = True
End Sub

Sub PrintButton()
  Application.EnableEvents = False
  Me.PrintOut
  Application.EnableEvents = True
End Sub

Sub PdfButton()
  Dim PdfFile As String, char
  Application.EnableEvents = False
  ' Build PdfFile's name
  PdfFile = Me.Name & "_" & Format(Date, "yymmdd_hhmmss")
  ' Replace unallowed symbols by the underscore char
  For Each char In Split("? "" / \ < > * | :")
    PdfFile = Replace(PdfFile, char, "_")
  Next
  ' Add path of this workbook
  PdfFile = Left(Me.Path & "\" & PdfFile, 251) & ".pdf"
  ' Create Pdf
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                         Filename:=PdfFile, _
                         Quality:=xlQualityStandard, _
                         IncludeDocProperties:=True, _
                         IgnorePrintAreas:=False, _
                         OpenAfterPublish:=False
  Application.EnableEvents = True
End Sub

Private Sub Workbook_Activate()
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_Deactivate()
Application.CellDragAndDrop = True
Application.OnKey "^c"
Application.CutCopyMode = False
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.CellDragAndDrop = True
Application.OnKey "^c"
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
MsgBox "Right click menu deactivated." & vbCrLf & _
"Cannot copy or ''drag & drop''.", 16, "For this workbook:"
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Application.CutCopyMode = False
End Sub

Private Sub Workbook_Open()
If Now() > #3/15/2039# Then Call SuicideSub ' #mesec/den/godina#
End Sub

in Module 11
Sub ProtectAll()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:=k0s, DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True
Next ws

End Sub

Sub DeProtectAll()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:=k0s
Next ws

End Sub

in Module 1 your CODE

Option Explicit

Private fso As Scripting.FileSystemObject

Public Function GetAFolder(filePath As String) As Scripting.folder
    Set fso = New Scripting.FileSystemObject

    Dim someFolder As Scripting.folder
    Set someFolder = fso.GetFolder(filePath)
  
    Set GetAFolder = someFolder
End Function

Sub ProcessFiles()

    Set fso = New Scripting.FileSystemObject
  
    'access folder
    Dim folder As Scripting.folder
    Set folder = GetAFolder("C:\Users\Nevidim\Desktop\.................") 'my path
  
    'export module to import later
    ThisWorkbook.VBProject.VBComponents("someModule").Export ThisWorkbook.Path & "\someModule.bas"
  
    'declare some vars
    Dim wb As Workbook
    Dim file As Scripting.file
    Dim fileExt As String
    Dim ws As Worksheet
    Dim codeString As String
  
    'loop folder
    For Each file In folder.Files
        'ignore non excel files
        If fso.GetExtensionName(file.Path) = "xlsx" Or fso.GetExtensionName(file.Path) = "xlsm" Then
            'open wb from file
            Set wb = Workbooks.Open(file.Path, False, False)
          
            'import module
            wb.VBProject.VBComponents.Import ThisWorkbook.Path & "\someModule.bas"
          
            'write code to all worksheet modules
            For Each ws In wb.Worksheets
                'get the code
                With ThisWorkbook.VBProject.VBComponents("VZIMANE MODULI").CodeModule 'this is the file with modules
                    codeString = .Lines(2, .CountOfLines)
                End With
              
                'write the code
                wb.VBProject.VBComponents(ws.CodeName).CodeModule.AddFromString codeString
            Next ws
          
            'get code from thisworkbook
            With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
                codeString = .Lines(2, .CountOfLines)
            End With
          
            'write code to the thisworkbook module of target file
            wb.VBProject.VBComponents("ThisWorkbook").CodeModule.AddFromString codeString
          
            'save and close
            wb.SaveAs ThisWorkbook.Path & "\" & Left(wb.Name, Len(wb.Name) - 4) & "xlsm", 52
            wb.Close True
        End If
    Next file
End Sub

in Module 2

Sub SuicideSub()
'courtesy Tom Ogilvy
With ThisWorkbook
.Saved = True
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End Sub

You said you were writing sheet code to the worksheets of the exported file. Write the name of the worksheet that has that code in the parent file in this line:
With ThisWorkbook.VBProject.VBComponents("sheetNameWhereYouHaveSheetCode").CodeModule

---------------------------------------------------

I don't understand it, if it's the way I think, i.e. to have a macro in, for example, Sheet1(Sheet1), I don't have one.
Macros are only in Modules and ThisWorkbook


And how to get not only one module, but all of them, because in the line where I have to write it, the name of the module is only 1?

ThisWorkbook.VBProject.VBComponents("Module2" how to put Module11, Module1).Export........................

 

[K0st4din]

https://drive.google.com/file/d/11XoDYuziPz0at-QmL8t-J8nW8u2R-op9/view?usp=sharing

link to my file to download

 

[K0st4din]

Well actually, after we wrote to each other yesterday, I decided for the last things, to export the final macros to a new workbook. In the other where everything is located, I have over 18 modules and your idea is to first export the 14 workbooks I need with 2 worksheets in them and with the new file let only the necessary modules, which are not many, be transferred.
The idea is great, because with your macro, if necessary, I will be able to add another module, if there is a need, of course.
However, now in the last macro uploaded by you, it gives me the following error, apparently I'm missing something.
(if you can see what I'm leaving out).
In the made folder I have saved all 14 files and when I open VZEMANE MODULI.xlsm, from/in Sheet1 I press the button and guess it will transfer my idea to all files.
Thank you very much, I'll stay tuned to see why it's giving me this error in the first place.

 

[K0st4din]

I think I managed to find it and I clicked it to activate it, but I still get some error, something was not defined, and I put the path where the file is located....


Errors:

And how do I get the macro from ThisWorkbook?

 

[Edgar_]

Make sure you only add the reference I told you, I think you might have added unnecessary references by the looks of your screenshot. That error is resolved when the right reference ticked.

The routine already takes care of getting the code from the ThisWorkbook code module, it's the last thing it does before saving.

 

[K0st4din]

Apparently I don't understand something. You are trying to tell me that I should leave only Microsoft scripting runtime ticked and remove everything else. If, as I think, all the other ticks I have set and I need them, will they be excluded from how to say the main excel, or am I messing things up terribly and this exclusion will only apply to this workbook? Does it work for you when the file is tested. At least it's easy to put in even two blank excel files and see if it transfers the macros.....

 

[K0st4din]

One more little detail, I'm using office 2007 - does that make any difference!?
But to repeat myself, it seems that I chose exactly what you told me. Why if I have other things selected that I need for other performances will they mess up, just another extra option?
Thank you very much!
P.S. I can give you an anydesk link, you can see for yourself why my excel is crashing

 

[Edgar_]

I just wanted to make sure these references were not ticked because you were looking for the Microsoft Scripting Runtime:
Microsoft Office Runtime 1.0 Type Library
Microsoft Script Control 1.0

When I open your file, I do not get these references and I don't see any code needing them.

will they be excluded from how to say the main excel, or am I messing things up terribly and this exclusion will only apply to this workbook

This is not something to worry about until you export your files and write code to them using the routine provided.

Here's your file:

Loading…

file.io

Note:
I made some changes in regards to Option Explicit.
In my VBA editor, it is automatically added to all modules, which is a good practice. I noticed some of your modules did not have Option Explicit at the top, you should change that, all your modules should always have it at the top, first line. I added it to all your modules. Why is that important?
1. because we're dealing with the writing of code modules, and that directive is the first thing the compiler looks for. Turn it on.
2. because my code needs you to specify a range of lines that should be copied, I'm starting on line 2, because line 1 should always be Option Explicit and all my modules should have that turned on. Make sure your existing modules all have Option Explicit at the top, first line.

Additional note:
Your BeforeSave routine gets in the way, I don't know what it's supposed to do, but it's only being annoying, it's not stopping anyone from saving. Cancel is always false.

Additional note:
Change the folder in the routine. I left my testing folder.

Why if I have other things selected that I need for other performances will they mess up, just another extra option?

I don't know what you're talking about. Please check this works before going into IT mode.

 

[K0st4din]

Hi, I am trying to download the file but it says it has been deleted.