Macro from workbook that was generated by "master" workbook launches "master" workbook to run

[karlv02]

Greetings!

First off, a big thank you to everyone one here. I have been lurking here for the last week and have "stolen" help from you guys and its all been great!

second, i hope i am not re-posting a question. I wasn't really finding anything for my search terms, so i apologize in case this is a re-post.

I have a work order template that i made, that with a click of a button, creates a new workbook with a incremented file name, imports a module into the workbook, emails notification to someone that the work order has been created, and prints out the work order.

The module that i imported contains a basic print macro, and a macro to send a notification to the creator of the work order that the status of the work order has changed. When i run either of these macros from the generated workbook, they launch the "master" workbook in order to run.

My goal is to make the new workbook independent from the master workbook.

The code i use to generate the file is:

Sub SavePrintSend()
   Dim NewFN As Variant
   Dim OutApp As Object
   Dim OutMail As Object
   Dim strbody As String
   Dim FileLocation As String
   Dim NewFN2 As Variant
   Dim NewFN3 As String
   
   Dim CodeLocation As String
    Dim DateCreate As String
    
    'insert date into cell
    DateCreate = Format(Date, "Long Date")
    Range("E6").Value = DateCreate
    
    ' copy invoice to a new workbook
    Sheets().Copy
   
    'get file location of workbook to save to and name new workbook
    NewFN2 = ThisWorkbook.Path & "\WO" & Range("E5").Value & ".xlsm"
    NewFN3 = ThisWorkbook.Path & "\WO" & Range("E5").Value
    ActiveWorkbook.SaveAs NewFN2, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    'copy VBA Module 2 for print and update buttons
    CodeLocation = ThisWorkbook.Path & "code.txt"
    ThisWorkbook.VBProject.VBComponents("Module2").Export CodeLocation
    ActiveWorkbook.VBProject.VBComponents.Import CodeLocation
    ActiveWorkbook.Save
    
    'send to default printer
    ActiveWorkbook.PrintOut
    'email attachment, decomment below
    'Application.Dialogs(xlDialogSendMail).Show
    
    'email notification
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "A work order has been submitted to you." & vbNewLine & _
              "The File is located at: " & NewFN2 & vbNewLine
    
     On Error Resume Next
     With OutMail
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = "Word order " & Range("E5").Value
        .Body = strbody
        .Display
    
    End With
    On Error GoTo 0


    Set OutMail = Nothing
    Set OutApp = Nothing
    ' close new workbook and save and clear main workbook
        
    ActiveWorkbook.Close
    NextWorkorder


End Sub

and one of my macros in the created workbook is:

Sub SendUpdate()    Dim NewFN4 As Variant
    Dim strbody As String
    Dim ReturnEmail As String
    Dim Status As String
    Dim FN As String
    
    Status = Range("E8").Text
    ReturnEmail = Range("L3").Text
    NewFN4 = ThisWorkbook.Path
    FN = ThisWorkbook.Name
    
    
    'email notification
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "Your Work order " & Range("E5").Value & " has been updated" & vbNewLine & _
              "The File is located at: " & NewFN4 & FN & vbNewLine & _
              "The status has been changed to: " & Status
              
    
     On Error Resume Next
     With OutMail
        .To = ReturnEmail
        .CC = ""
        .BCC = ""
        .Subject = "Word order " & Range("E5").Value
        .Body = strbody
        .Display
    
    End With
    On Error GoTo 0


    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

I made sure all my Dims were unique as i assumed it was looking for previous Dims but that hasn't changed how these macros operate.

Thanks for the help in advance

 

[karlv02]

The more i look at it, the more i think i'm exporting/importing the Module wrong, but i'm still not sure how to fix that.

 

[karlv02]

Well, in case anyone was interested, after much research i was actually talking about "ChangeLink" , and the following code fixed everything.

    'move links to new file
    ActiveWorkbook.ChangeLink Name:=MainWB, NewName:=NewFN2, Type:=xlLinkTypeExcelLinks

Where MainWB was Dim as String and NewFN2 was Dim as Variant.

Cheers!

 

[NdNoviceHlp]

Seems like your question is an enigma wrapped within a conundrum The chicken and egg thing. Glad you were able to get that sorted out... without much help...and you shared. I was wondering what OS and edition of XL you are using if you care to share some more? The later versions of XL don't like you messin with the VBComponents and it seems like your code does an end run. Dave

 

[karlv02]

i wouldn't be surprised if my code has issues, i'm just a lucky and logical novice. good times though. the project i'm working on is now way bigger/feature rich that i hoped for. its great. I built the code with 2010 and excel 365/2013.

 

[NdNoviceHlp]

The more I look at this, the more I don't understand. What was your question? You stated your goal but not what the problem was. Your solution was a link... uggg. Where does the link code go? When you go to the wb that imported the module is it actually there? Still curious. Dave

 

[karlv02]

The problem I was having was that when I exported the module to the new workbook, the new work book still referenced the old workbook (which donated the module) and would launch the original workbook to run the macros inside the new workbook. I'm not sure if that makes sense. The changeling code when right after the import section of the code.

Now it works perfect. The original workbook generates the new workbook, exports another module into it, and then saves the new workbook. If I then close the original and launch the new workbook, it has my 2nd module from the first workbook and is able to run the VB code/macros inside it.


Not all my subs are here, just the section for creating the WB and the export/import of the module, and then the saving of of the file. The 2nd set of code was one of the subs in the 2nd module.


Does that make sense? Lol, too many uses of the word "workbook".

 

[NdNoviceHlp]

So the extra code was actually cancelling some unspecified link. Interesting. Thanks for posting. Have a nice day. Dave

 

[karlv02]

No problem! Yeah, it was kind of random as I was using unique names for all my subs and dims, but it seemed that just because I exported the module from workbookA that workbookA needed to be running once the macro was launched from workbookB. I tried breaking links, but that killed any link, including links between the sheets in the new workbook. But the changelink just clears references to workbookA from WorkbookB, and repoints workbookB to itself.