Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy code into new wbook (2000 sr 1)

    With this routine i transfer module from a wbook to another, is possible to copy the sheet code of wbook into anoter sheet code...

    CopyCodeModule ThisWorkbook, wrk, "PROVA"

    Public Sub CopyCodeModule( _
    ByRef SourceWorkbook As Workbook, _
    ByRef DestWorkbook As Workbook, _
    ByVal ModuleName As String)
    Dim FilePath As String
    FilePath = SourceWorkbook.Path & "PROVA.txt"
    SourceWorkbook.VBProject.VBComponents(ModuleName). Export FilePath
    DestWorkbook.VBProject.VBComponents.Import FilePath
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy code into new wbook (2000 sr 1)

    You can use this procedure:

    Sub CopySheetModule( _
    wbkSource As Workbook, _
    strSource As String, _
    wbkTarget As Workbook, _
    strTarget As String)
    Dim strCode As String
    Dim mdl As CodeModule
    Set mdl = wbkSource.VBProject.VBComponents(strSource).CodeMo dule
    strCode = mdl.Lines(1, mdl.CountOfLines)
    Set mdl = wbkTarget.VBProject.VBComponents(strTarget).CodeMo dule
    mdl.DeleteLines 1, mdl.CountOfLines
    mdl.AddFromString strCode
    End Sub

    Call it for example like this:

    Sub TestIt()
    Dim wbkSource As Workbook
    Dim strSource As String
    Dim wbkTarget As Workbook
    Dim strTarget As String
    Set wbkSource = Workbooks("This.xls")
    Set wbkTarget = Workbooks("That.xls")
    strSource = "Sheet1"
    strTarget = "Sheet2"
    CopySheetModule wbkSource, strSource, wbkTarget, strTarget
    End Sub

    This will copy the code from Sheet1 in This.xls to Sheet2 in That.xls.

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy code into new wbook (2000 sr 1)

    ... Hans your code work fine in test, bu in my code have a prob:
    The trghet wbook is a varibale name from thsi string:

    strBodyText = "GCD01F4500DATIPUBBLICASTORICO_INPS" & strNomeFile & "_" & strData & ".xls"

    and strNomeFile strData chenge during a cocling of other routine how to assign the name to the wbkTarget?


    piece of my code:


    strBodyText = "GCD01F4500DATIPUBBLICASTORICO_INPS" & strNomeFile & "_" & strData & ".xls"

    StrBodyNewWbook = Right((strBodyText), 17)

    Set wbkSource = Workbooks("MASTER_INPS.XLS")

    Set wbkTarget = Workbooks("StrBodyNewWbook")

    strSource = "foglio15"
    strTarget = "foglio1"

    CopySheetModule wbkSource, strSource, wbkTarget, strTarget

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy code into new wbook (2000 sr 1)

    What happens if you change

    Set wbkTarget = Workbooks("StrBodyNewWbook")

    to

    Set wbkTarget = Workbooks(StrBodyNewWbook)

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy code into new wbook (2000 sr 1)

    Solved for path of wbook...

    but excel go in crash here:

    mdl.AddFromString strCode

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy code into new wbook (2000 sr 1)

    Do you get an error message? If so, what does it say?

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy code into new wbook (2000 sr 1)

    <P ID="nt"><font size=-1>(No Text)</font>

  8. #8
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy code into new wbook (2000 sr 1)

    and here the scenario when crash Excel...

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy code into new wbook (2000 sr 1)

    and here the pice of macro:
    strBodyText = "GCD01F4500DATIPUBBLICASTORICO_INPS" & strNomeFile & "_" & strData & ".xls"

    StrBodyNewWbook = Right((strBodyText), 17)

    Set wbkSource = Workbooks("MASTER_INPS.XLS")

    Set wbkTarget = Workbooks(StrBodyNewWbook)


    strSource = "foglio13"
    strTarget = "foglio1"

    CopySheetModule wbkSource, strSource, wbkTarget, strTarget

    wrk.Save



    vntEmail = Split(strDest, "|")


    For intIndex = 0 To UBound(vntEmail)
    If vntEmail(intIndex) <> "" Then

    Dim strRecipient As String
    strRecipient = vntEmail(intIndex)
    SendEmail strOggetto, "GCD01F4500DATIPUBBLICASTORICO_INPS" & strNomeFile & "_" & strData & ".xls", strRecipient, strBodyText

    End If
    Next

    wrk.Close

    Application.DisplayAlerts = True

    End Sub

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy code into new wbook (2000 sr 1)

    Does this work better?

    Sub CopySheetModule( _
    wbkSource As Workbook, _
    strSource As String, _
    wbkTarget As Workbook, _
    strTarget As String)
    Dim f As Integer
    Dim i As Integer
    f = FreeFile
    Open "Export.txt" For Output As #f
    With wbkSource.VBProject.VBComponents(strSource).CodeMo dule
    For i = 1 To .CountOfLines
    Print #f, .Lines(i, 1)
    Next i
    End With
    Close #f
    With wbkTarget.VBProject.VBComponents(strTarget).CodeMo dule
    .DeleteLines 1, .CountOfLines
    .AddFromFile "Export.txt"
    End With
    Kill "Export.txt"
    End Sub

  11. #11
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy code into new wbook (2000 sr 1)

    ....Same crash.....!!!!!!
    Attached my last project...

    start with:Sub cmdInvia()

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy code into new wbook (2000 sr 1)

    This is a general problem, I don't know a solution for it. But if you copy the A.T.CAMPANIA worksheet to a new workbook, then modify its contents, you will copy the code with the workbook, so you don't need to use separate code to transfer the worksheet code.

  13. #13
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy code into new wbook (2000 sr 1)

    sure... but this strategy is my first idea....
    Remember ".... create new wbook from... other wbook TEMPLATE"
    I have maked variuos line of code but reslut= 0
    if you have time...
    If you wount to help me use the alreday existis code to filtre the A.T. CAMPANIA, ecc...
    Tks .
    Sal.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy code into new wbook (2000 sr 1)

    You can use the Copy method of the A.T.CAMPANIA worksheet to copy it to a new workbook, then clear it and use the code you already have to copy the filtered data to the new workbook.

  15. #15
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy code into new wbook (2000 sr 1)

    Is possible (to maintain this code) is i use CMDINVIA_SOLL modifie the the line

    Call INVIA(strDest, "INPS - T8327 RATE PENSIONI DA RIACCREDITARE", strCodice, rng)
    in

    Call INVIA(strDest, "INPS - T8327 RATE PENSIONI DA RIACCREDITARE - SOLLECITO ", strCodice, rng)

    If i use the macro CMDINVIA use the old

    Call INVIA(strDest, "INPS - T8327 RATE PENSIONI DA RIACCREDITARE", strCodice, rng)

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
  •