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_]

With ThisWorkbook.VBProject.VBComponents("VZIMANE MODULI").CodeModule 'this is the file with modules
I thought everything was in your parent workbook, is this another workbook? If it's in the same workbook, then this modification of ProcessFiles should work.

Sub ProcessFiles()

    Set fso = New Scripting.FileSystemObject
  
    'access folder
    Dim folder As Scripting.folder
    Set folder = GetAFolder("C:\Users\Nevidim\Desktop\EMO I SASHO SALES\PRODAJBI SASHO MESECI\")
  
    'export module to import later
    ThisWorkbook.VBProject.VBComponents("Module11").Export ThisWorkbook.Path & "\Module11.bas"
    ThisWorkbook.VBProject.VBComponents("Module2").Export ThisWorkbook.Path & "\Module2.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 & "\Module11.bas"
            wb.VBProject.VBComponents.Import ThisWorkbook.Path & "\Module2.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

If that does not work, let me know. I'll take a look tomorrow.

 

[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.

 

[Edgar_]

Add a reference to Microsoft scripting runtime. For that, go to the vba window, look for the tools menu, select references and scroll until you find it. Make sure you do this when the error is not active.

 

[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:

https://file.io/supRZijdBRfe

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.

 

[Edgar_]

Try this:

VZIMANE MODULI.xlsm - Anonym File

File VZIMANE MODULI.xlsm hosted on ANONYMFILE

anonymfile.com

Someone must have downloaded the file before you.