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

Google how to export code modules. It involves using the workbook's VBProject object, like:

Sub ExportModule(moduleName As String, destination As String)
    ThisWorkbook.VBProject.VBComponents(moduleName).Export destination
End Sub

Sub ImportModule(moduleFilePath As String)
    ThisWorkbook.VBProject.VBComponents.Import moduleFilePath
End Sub

Make sure to trust access to the code project in the trust center.

 

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

 

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

 

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

 

[K0st4din]

Hi Edgar_,
I'll give it a try, for sure.
Because just because of this google that was doing a perfect job for me, but for some time, the tables that I send lag so much and slow down that one could drink a bottle of wine.
The first time this problem occurred, I thought something was wrong with the table. I started all over again to make the tables, no formatting, formulas, it's like I write the numbers by hand. In a word, the most ordinary table (yes, of course there are a lot of numbers, but they are far less than the permissible, as a limit), but despite everything the logging and inscriptions like: Please wait......., straight it got awful.
And so I forced myself to change everything with macros and send the tables via emails, but then the protection of not being able to print, copy, download the file, etc. fails. Now I will try what you suggest and test it. Then I will write about the final result. Just to ask, these two macros, I guess should be in the parent file (from where I extract the 2 worksheets, right?

 

[K0st4din]

Hello again, apparently I don't do something like other people.
I placed these two macros in the main workbook, from where I then split into 2 worksheets.
However, the 1st macro written like this I can't insert it into a button to activate it, and also moduleName - when I wrote for example Module2, it showed me an error, then on moduleFilePath - do I have to show the path in my computer or will it do it automatically where the file itself is located.
So, I guess I didn't understand it. And I will something in the VBA itself in Thisworkbook I also have macros - how to move it to the already new workbook with the two worksheets.

Thank you in advance.

 

[Edgar_]

these two macros, I guess should be in the parent file (from where I extract the 2 worksheets, right?

Yes, they go in the parent file. They're working examples to export/import a code module. You just have to adapt them. Another strategy is to simply create 14 copies of the file and programmatically remove the sheets that don't belong. That way you will not have to do any exporting and importing.

 

[K0st4din]

Well I think the second suggestion can't work for me because with a macro, I select and take certain worksheets, then the finished file with both worksheets has an auto-generated name as well.
The question is that I am not very aware of macros and would you be able to help me. As I read, this was just an example, and I don't think I will be able to finalize (much to my regret) ((

 

[Edgar_]

Hi

the 1st macro written like this I can't insert it into a button to activate it

You have to call it from another sub, like:
ExportModule "Module2", ThisWorkbook.Path & "\Module2.bas"

do I have to show the path in my computer or will it do it automatically where the file itself is located

This snippet will use the current location:
ThisWorkbook.Path & "\MyDesiredFileName.bas"

I don't think I will be able to finalize

There are many ways to approach this. You could upload a sample file to see what I can come up with.