exporting modules with created workbook

  • Thread starter Thread starter BJthebear
  • Start date Start date
B

BJthebear

I am using the following code courtesy of Ron de Bruin to create a new
workbook which is just a small part of the main spreadsheet but it
will be emailed to individualk members of staff.

As I have written generic coding I would like the modules to also be
incorporated in this email as part of the new workbook but I have no
idea at all how I would attach and incorporate the five modules that
are in the original workbook

Can anyone point me in the right direction


Sub Mail_Sheets_Array_single()

'Macro Courtesy of Ron de Bruin

'Working in 97-2010
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim sh As Worksheet
Dim TheActiveWindow As Window
Dim TempWindow As Window
Dim I As Long


Set Sourcewb = ActiveWorkbook

'Copy the sheets to a new workbook
'We add a temporary Window to avoid the Copy problem
'if there is a List or Table in one of the sheets and
'if the sheets are grouped
With Sourcewb
Set TheActiveWindow = ActiveWindow
Set TempWindow = .NewWindow
.Sheets(Array("UserList", "HoursWorkedexpenses",
"ProjectList", "CLSstageList", "Input")).Copy
End With

'Close temporary Window
TempWindow.Close

Set Destwb = ActiveWorkbook

TempFilePath = Environ$("temp") & "\"
TempFileName = "Part of " & Sourcewb.Name _
& " " & Format(Now, "dd-mmm-yy h-mm-ss")

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, _
FileFormat:=FileFormatNum
On Error Resume Next
For I = 1 To 3
.SendMail "", _
"Latest Timesheet"
If Err.Number = 0 Then Exit For
Next I
On Error GoTo 0



' only add this line if you do not want to send email
' .Close SaveChanges:=False
End With

'Delete the file you have sent
' This line removes all trace of the email
' Kill TempFilePath & TempFileName & FileExtStr

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
 
I am using the following code courtesy of Ron de Bruin to create a new
workbook which is just a small part of the main spreadsheet but it
will be emailed to individualk members of staff.

As I have written generic coding I would like the modules to also be
incorporated in this email as part of the new workbook but I have no
idea at all how I would attach and incorporate the five modules that
are in the original workbook

Can anyone point me in the right direction

Sub Mail_Sheets_Array_single()

'Macro Courtesy of Ron de Bruin

'Working in 97-2010
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim sh As Worksheet
    Dim TheActiveWindow As Window
    Dim TempWindow As Window
    Dim I As Long

Set Sourcewb = ActiveWorkbook

    'Copy the sheets to a new workbook
    'We add a temporary Window to avoid the Copy problem
    'if there is a List or Table in one of the sheets and
    'if the sheets are grouped
    With Sourcewb
        Set TheActiveWindow = ActiveWindow
        Set TempWindow = .NewWindow
        .Sheets(Array("UserList", "HoursWorkedexpenses",
"ProjectList", "CLSstageList", "Input")).Copy
    End With

    'Close temporary Window
    TempWindow.Close

    Set Destwb = ActiveWorkbook

TempFilePath = Environ$("temp") & "\"
    TempFileName = "Part of " & Sourcewb.Name _
                 & " " & Format(Now, "dd-mmm-yy h-mm-ss")

    With Destwb
        .SaveAs TempFilePath & TempFileName & FileExtStr, _
                FileFormat:=FileFormatNum
        On Error Resume Next
        For I = 1 To 3
            .SendMail "", _
                      "Latest Timesheet"
            If Err.Number = 0 Then Exit For
        Next I
        On Error GoTo 0

        ' only add this line if you do not want to send email
        ' .Close SaveChanges:=False
    End With

    'Delete the file you have sent
    '   This line removes all trace of the email
    '  Kill TempFilePath & TempFileName & FileExtStr

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub
Click to expand...

The modules are already part of the file you attach to an email
 
The modules are already part of the file you attach to an email
Click to expand...


The thing is that I am only sending a small number of the worksheets -
not a copy of the whole workbook.
Certainly when I open the "New" Workbook there is nothing in the VBA
modules and if I want to run a macro it refers back to the original
file.

Thanks

Brian
 
The thing is that I am only sending a small number of the worksheets -
not a copy of the whole workbook.
Certainly when I open the "New" Workbook there is nothing in the VBA
modules and if I want to run a macro it refers back to the original
file.

Thanks

Brian- Hide quoted text -

- Show quoted text -
Click to expand...

Easiest way would be to delete the undesired sheets>saveas>send that
 
Easiest way would be to delete the undesired sheets>saveas>send that- Hide quoted text -

- Show quoted text -
Click to expand...

I had thought of that but I am wary of deleting the main sheet with
over 20000 entries each time I want to send a copy out to a member of
staff for completion.

I have come across the following code which I am trying to adapt:-

Sub CopyOneModule()

Dim FName As String
With Workbooks("2006Bud1.xls")
FName = .Path & "\code.txt"
..VBProject.VBComponents("Module1").Export FName
End With
Workbooks("book2").VBProject.VBComponents.Import FName

End Sub

Thanks once again

Brian
Scotland
 
Back
Top