Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Star Lounger
    Join Date
    May 2013
    Posts
    59
    Thanks
    34
    Thanked 6 Times in 6 Posts

    Inject code into another workbook

    Hi all,

    I have high hopes this can be done based on an example from the thread: Can I change code on the fly?

    At work I have a workbook (I will call "Main.xlsm) that creates XML files from data uploaded from several other workbooks (Source1.xlsm, Source2.xlsm, etc.). These source books draw their data via a macro from a weekly generated spreadsheet named with the prefix EC followed by what appears to be a random string (EC_6734ad5.xls). The code in each of the source books, to get the data from that file, looks like this:

    Code:
    Public Sub GetData()
    Dim Wb As Workbook
    Dim InputFilename As String
    InputFilename = "\\PDshare1\Eco\EC_6734ad5.xls"
    Set Wb = Application.Workbooks.Open(InputFilename)
    Wb.Sheets(1).Activate
    
    'more code
    
    End Sub
    Each Monday the source books need to look for a new file which requires me to manually update the code. I could email the employees which file they will need to use and then modify the above code to prompt them to select the file but that opens up a wide margin of error. They may easily select the wrong file the way they are named. What I am looking for an automatic way that I can update each source book with the correct input file by changing the line of code:

    InputFilename = "\\PDshare1\Eco\EC_6734ad5.xls"

    I have been toying with it but just can't get it right. It would just be wonderful if this process could be automated.

    Thank you so much in advance.
    Alexandra

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Alexandra

    One way to do this is to have the source books 'fetch' the name for the new weekly spreadsheet file from a 'standard master workbook' that contains the relevant new weekly filename (and network path, if necessary).

    i.e. you create a simple Excel file say, [bookname.xls] that has the new weekly filename in a defined location e.g. cell [A1] on "Sheet1"
    e.g. [A1] = "\\PDshare1\Eco\EC_6734ad5.xls"
    (you just update this entry yourself each week with the new filename etc)

    Your vba lines in the source .xlsm files would then use something like this:

    Workbooks.open filename:="bookname.xls", readonly:=true
    Workbooks("bookname.xls").activate
    InputFilename = [A1].value
    workbooks("bookname.xls").close savechages:=false


    zeddy

  3. The Following User Says Thank You to zeddy For This Useful Post:

    Alouso (2013-08-07)

  4. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How are the file names generated? Can the code figure it out at runtime, can it look it up somewhere? For example you could (if nothing else) create a workbook on a shared drive and place the filename in a cell. the code could open that file, read the name then run the rest of the procedure. You could daily, weekly (or whenever) edit the file and resave it and anytime the code runs it will use the most recent name in that file.

    Steve

  5. The Following User Says Thank You to sdckapr For This Useful Post:

    Alouso (2013-08-07)

  6. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Zeddy, Great minds...

    One comment, there is no need to activate the workbook, you could just use something like:
    InputFilename = Workbooks("bookname.xls").worksheets("sheet1").ran ge("A1")

    Steve

  7. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts

    Vb code to inject code

    Alexandra,

    The Main workbook has the code to change the macro in the Source workbook. Clicking on the "Run" button in Main will open the dialogue box to navigate to the weekly generated file. After selecting it, the code will then update the routine in Source with the path and file. You will need to replace with the correct path with for the Source files. I have assumed that the module that contains the code in the Source workbook is Module1.

    InjectCode1.png InjectCode2.png

    Place in the Main workbook
    Code:
    Public Sub UpdateSource()
    On Error GoTo ErrorHandling
    Application.ScreenUpdating = True
    'DECLARE VARIABLES
    Dim file As String
    Dim Filter As String
    Dim Caption As String
    Dim Filename As String
    '------------------------------------------
    'SET VARIABLES
    Filter = "Text files (*.xls),*.xls"
    Caption = "Please Select the source file "
    Filename = Application.GetOpenFilename(Filter, , Caption)
    file = "InputFilename = " & Chr(34) & Filename & Chr(34)
    '-----------------------------------------
    'REPLACE UPDATED OPERATORS LINE 2 NEWCODE ROUTINE IN SHEET1 MODULE
    Application.Workbooks.Open ("C:\Users\Maudibe\Desktop\Source.xlsm") 'CHANGE PATH
    With Workbooks("Source.xlsm").VBProject.VBComponents("Module1")  'CHANGE MODULE NAME
        .Activate
        .CodeModule.DeleteLines 4
        .CodeModule.InsertLines 4, file
    End With
    '-------------------------------
    'CLEAR VARIABLES
    file = vbNullString
    Workbooks("Source.xlsm").Save
    Workbooks("Source.xlsm").Close
    Application.ScreenUpdating = True
    Exit Sub
    ErrorHandling:
    MsgBox "The Souce file could not be updated.  Please make sure the file is located in the correct directory and try again."
    End Sub
    In this instance, I would agree with Steve and Zeddy on having a dedicated file with a cell value containing the name of the new workbook. The source book's code would then point it to obtain the file name. It would be easier to maintain especially if you alter the number of lines in the Source book's code and the above code would need to loop for each source workbook. To make it easier, you could use the navigation part of my routine to find the file and populate the cell from the Main workbook or from within the Dedicated workbook itself.

    Although the technique is very unique, there are other instances that it can be well suited (ex. reference post).

    HTH,
    Maud
    Attached Files Attached Files

  8. The Following User Says Thank You to Maudibe For This Useful Post:

    Alouso (2013-08-07)

  9. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Just to throw in my 2c: if you are writing code to change code, your model is probably flawed. Although it can be done, it's best to try and avoid the need for it in the first place (as per the options Steve and Zeddy presented). This is especially true if you need the end users to be able to run the code that changes the code since it will require altering their security settings in order to trust access to the VB project.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Rory,

    It actually works very well but it comes at cost of looping for each workbook and the discipline of making sure the numbers of code lines from the top of the module do not change. If you were to have scrolled down further, you would have noticed that I recommended that the approach Zeddy and Steve suggested is the one I would follow. BTW, this code would not be run by the end user, rather by the administrator.

  11. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    My comment was not aimed at you - it was a general comment about this technique.

    Although it can work, it is usually dependent on too many factors to be a good solution.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    In this situation, I agree. There is a less obtrusive alternative. Not to debate, but primarily, the only factor that really comes it to play is the consistency of line number of the code line being modified. The other factors would be the same contingencies any other code would encounter. That said, it is very viable and reliable as a solution to many problems.

    I have been experimenting and testing this for over a year and have not had any issues thus far short of having the VB project locked on another workbook.

  13. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    My point was more the fact that is shouldn't be necessary in the first place, not that it can't (or even shouldn't) be done. It is however the sort of code that is often flagged as malicious by anti-virus engines and (in the enterprise anyway) deleted immediately.
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Rory,

    Your point is well noted.

    Thanks

  15. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi All

    Re: post#4:
    Steve, you are correct. I didn't need to Activate the workbook. It was just force of habit. Whenever I use vba to open a workbook, I always follow it with a vba line to activate it. Many will say that by, default, an opened workbook is already active, but I seem to recall that there are circumstances when this is NOT the case. Anyway, for the particular task required, on reflection, I wouldn't open the workbook anyway, as we can use vba to fetch the required value from a closed Excel workbook i.e. without opening it.

    Re: post#6:
    Rory, I can't believe you threw in 2c. The US version of the phrase two cents' worth is pre-dated by the British 'two-penneth'. Due to inflation, if you must use the US-variant, may I suggest you say "my 'two bits' worth". Perhaps you couldn't give tuppence what I think, but I just wanted to share this with others on both sides of the pond.*

    zeddy

    * To 'put one's two cents' worth in' referred to the cost of a letter to the editor, the president, or whomever was deserving". According to the Encyclopaedia Britannica, the US first-class postal rate was 2 cents an ounce between 1883 and 1932 . . .

  16. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Zeddy,
    The possible changing of the active workbook is to me the main reason why you should avoid working with the "active workbook". Working with assigned workbooks as variables is a better way to me to keep everything straight and avoid issues with taking things from the wrong workbook or wrong worksheet...

    Steve

  17. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Alex,

    Perhaps we can streamline this a bit. Instead of maintaining 4 files (Main, Source, Dedicated file to hold the name of the new file, and the new file itself), what if you can send the name of the file directly from the Main workbook to the Source workbooks? The following code will allow you to navigate to the new file and select it. It will then send the name to the Source workbook and run the code in the Source book. The Source book will update automatically and be ready for your team on Monday morning.

    Since you are concerned with margin of error, there will be no fourth file that may be accidentally deleted, no chance that the file name could be misspelled or an improper path is entered.

    HTH,
    Maud

    MAIN WORKBOOK STANDARD MODULE
    Code:
    Public Sub SendFile()
    On Error GoTo ErrorHandling
    Application.ScreenUpdating = False
    '------------------------------------------
    'DECLARE VARIABLES
    Dim Filter As String
    Dim Caption As String
    Dim Filename As String
    '------------------------------------------
    'SET VARIABLES
    Filter = "Text files (*.xls),*.xls"
    Caption = "Please Select the source file "
    Filename = Application.GetOpenFilename(Filter, , Caption)
    '------------------------------------------
    'RUN THE CODE IN THE SOURCE WORKBOOK AND PASS THE NEWFILE NAME
    Application.Run "'C:\Users\Maudibe\Desktop\Source.xlsm'!GetData", Filename
    Workbooks("Source.xlsm").Close
    Exit Sub
    '------------------------------------------
    ErrorHandling:
    MsgBox "The file could not be opened"
    Application.ScreenUpdating = True
    End Sub
    SOURCE WORKBOOK STANDARD MODULE
    Code:
    Public Sub GetData(InputFilename As String)
    'DECLARE VARIABLES
    Dim Wb As Workbook
    '----------------------------------
    'OPEN THE NEWFILE USING THE PASSED FILENAME
    Set Wb = Application.Workbooks.Open(InputFilename)
    Wb.Sheets(1).Activate
    End Sub

  18. The Following User Says Thank You to Maudibe For This Useful Post:

    Alouso (2013-08-07)

  19. #15
    Star Lounger
    Join Date
    May 2013
    Posts
    59
    Thanks
    34
    Thanked 6 Times in 6 Posts
    Thank you everyone for your input. My goal was to reduce the amount of labor involved in updating the data sent to the “Source” books from a new weekly file. I loved the idea that you came up with Maudibe, to have a macro change the code. Initially, I couldn’t get it to work but then I followed the code you posted. I made a few changes where indicated and it ran perfectly across the network for one of the source file. There were no issues with security misinterpretations of the changes the code had made. Although this is the only macro in the Source book’s module, I didn’t see the worry about the lines changing.

    But I will always accept suggestions, so I looked into the alternative of the file name updated in a special book that the code in the “Source” books will look for. It was an additional file but it much was easier to maintain than manually change the macro code. From Maudibe’s sample, I borrowed the code to navigate for the name and was working on the rest of the code, following Zeddy’s and sdckapr’s example to populate the special file with the name of the new workbook (EC_something.xls).

    Maudibe, you last post came at a perfect time and it is awesome. Not only does it eliminate the need for a special file, but it sends the file name like you first code did using the Navigation box. However, instead of actually changing the string in the code line, it changes the value of a variable representing the string. The best thing of all, is that it will automatically run the code to update it. So many times I have created XML files from data that was sent to me using the wrong data because they forgot to update.
    Once again, thanks to the members of this forum who are so willing to help.

    Regards,
    Alexandra

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •