AW: Modul in eine andere offene Mappe kopieren
30.01.2019 16:47:52
Alex
ja die Zielmappe ist geöffnet. die SourceWB und TargetWB werden richtig angezeigt. Modul 1-4 gibts. Aber wenn ich in VBA auch Modul9999 eingebe, passiert nichts. Hab Workbooks(MName) geändert zu ActiveWorkbook und vor SaveAs platziert. Funktioniert immer noch nicht
Worin ich die Copymodule eingebaut habe.
With objMail
.SentOnBehalfOfName = ""
.To = Address
.Subject = SubjectLine
.BodyFormat = olFormatHTML
.HTMLBody = MsgBody
For i = 0 To UBound(SheetNames, 1)
letzteSheets = SrcWkb.Worksheets(SheetNames(i)).Cells(Rows.Count, 2).End(xlUp).Row
Set DstWkb = Workbooks.Add(xlWBATWorksheet)
SrcWkb.Worksheets(SheetNames(i)).Copy After:=DstWkb.Worksheets(DstWkb.Worksheets.Count)
With ActiveSheet
MName = Right(SheetNames(i), 8) & " POC-Liste_" & Format(Now, "dd.mm.yy") & ".xlsm"
Application.DisplayAlerts = False
CopyModule ThisWorkbook, "Modul4", ActiveWorkbook
ActiveWorkbook.SaveAs Filename:=Environ("TEMP") & "\" & MName, FileFormat:= _
xlOpenXMLWorkbookMacroEnabled
Application.DisplayAlerts = True
objMail.Attachments.Add DstWkb.FullName, 1, 1
End With
Next i
.Display 'send
End With
Sub CopyModule(SourceWB As Workbook, strModuleName As String, TargetWB As Workbook)
' requires a reference to the Microsoft Visual Basic Extensibility library
Dim strFolder As String, strTempFile As String
strFolder = SourceWB.Path
If Len(strFolder) = 0 Then strFolder = CurDir
strFolder = strFolder & "\"
strTempFile = strFolder & "~test.bas"
MsgBox SourceWB.Name
MsgBox TargetWB.Name
On Error Resume Next
SourceWB.VBProject.VBComponents(strModuleName).Export strTempFile
TargetWB.VBProject.VBComponents.Import strTempFile
Kill strTempFile
On Error GoTo 0
End Sub