Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Place code from current workbook to new workbook
I am using the following code to create a new workbook and send via outlook
to whatever adress the user enters. Private Sub Send1_Click() Dim strdate As String Sheets(Array("Emp24", "Main")).Copy strdate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss") Worksheets("Emp24").Name = "NewData" ActiveWorkbook.SaveAs "NewEmployeeData.xls" ActiveWorkbook.SendMail "", _ "Employee Attendance Data" ActiveWorkbook.ChangeFileAccess xlReadOnly Kill ActiveWorkbook.FullName ActiveWorkbook.Close False End Sub In the original workbook inside the This Workbook section I have the following: Private Sub Workbook_Open Dim lngColor As Long lngColor = RGB(221, 221, 221) ActiveWorkbook.Colors(16) = lngColor End Sub How can I place this 2nd code into the This Workbook section of the file being sent i the first sub? Thank You for any help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Place code from current workbook to new workbook
Hi Qaspec,
See Chip Pearson's 'Programming To The Visual Basic Editor' page at: http://www.cpearson.com/excel/vbe.htm --- Regards, Norman "Qaspec" wrote in message ... I am using the following code to create a new workbook and send via outlook to whatever adress the user enters. Private Sub Send1_Click() Dim strdate As String Sheets(Array("Emp24", "Main")).Copy strdate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss") Worksheets("Emp24").Name = "NewData" ActiveWorkbook.SaveAs "NewEmployeeData.xls" ActiveWorkbook.SendMail "", _ "Employee Attendance Data" ActiveWorkbook.ChangeFileAccess xlReadOnly Kill ActiveWorkbook.FullName ActiveWorkbook.Close False End Sub In the original workbook inside the This Workbook section I have the following: Private Sub Workbook_Open Dim lngColor As Long lngColor = RGB(221, 221, 221) ActiveWorkbook.Colors(16) = lngColor End Sub How can I place this 2nd code into the This Workbook section of the file being sent i the first sub? Thank You for any help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Place code from current workbook to new workbook
I've tried to use the suggestions made in the url. I can't even get to tell
if I can get it to work bacause a) it is specific to modules and b) they set off the virus scan at my company. They are not going to change the scan so is there another way to export or copy a sub from the This Workbook section in one file to the This Workbook section in another file? "Norman Jones" wrote: Hi Qaspec, See Chip Pearson's 'Programming To The Visual Basic Editor' page at: http://www.cpearson.com/excel/vbe.htm --- Regards, Norman "Qaspec" wrote in message ... I am using the following code to create a new workbook and send via outlook to whatever adress the user enters. Private Sub Send1_Click() Dim strdate As String Sheets(Array("Emp24", "Main")).Copy strdate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss") Worksheets("Emp24").Name = "NewData" ActiveWorkbook.SaveAs "NewEmployeeData.xls" ActiveWorkbook.SendMail "", _ "Employee Attendance Data" ActiveWorkbook.ChangeFileAccess xlReadOnly Kill ActiveWorkbook.FullName ActiveWorkbook.Close False End Sub In the original workbook inside the This Workbook section I have the following: Private Sub Workbook_Open Dim lngColor As Long lngColor = RGB(221, 221, 221) ActiveWorkbook.Colors(16) = lngColor End Sub How can I place this 2nd code into the This Workbook section of the file being sent i the first sub? Thank You for any help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Place code from current workbook to new workbook
Is it possible to use the code in my send macro to copy the entire workbook
and then delete the sheets that I don't need before sending via outlook? "Qaspec" wrote: I've tried to use the suggestions made in the url. I can't even get to tell if I can get it to work bacause a) it is specific to modules and b) they set off the virus scan at my company. They are not going to change the scan so is there another way to export or copy a sub from the This Workbook section in one file to the This Workbook section in another file? "Norman Jones" wrote: Hi Qaspec, See Chip Pearson's 'Programming To The Visual Basic Editor' page at: http://www.cpearson.com/excel/vbe.htm --- Regards, Norman "Qaspec" wrote in message ... I am using the following code to create a new workbook and send via outlook to whatever adress the user enters. Private Sub Send1_Click() Dim strdate As String Sheets(Array("Emp24", "Main")).Copy strdate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss") Worksheets("Emp24").Name = "NewData" ActiveWorkbook.SaveAs "NewEmployeeData.xls" ActiveWorkbook.SendMail "", _ "Employee Attendance Data" ActiveWorkbook.ChangeFileAccess xlReadOnly Kill ActiveWorkbook.FullName ActiveWorkbook.Close False End Sub In the original workbook inside the This Workbook section I have the following: Private Sub Workbook_Open Dim lngColor As Long lngColor = RGB(221, 221, 221) ActiveWorkbook.Colors(16) = lngColor End Sub How can I place this 2nd code into the This Workbook section of the file being sent i the first sub? Thank You for any help. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Place code from current workbook to new workbook
Hi Qaspec,
Your company blocks VB(A) macros because they might contain viruses. If it were possible readily to circumvent this, virus writers would be able to gain unfettered access to the company's computer systems. so is there another way to export or copy a sub from the This Workbook section in one file to the This Workbook section in another file? AFAIK, only with the assistance of the recipient. Perhaps, for example, you could send the macro as text together with instructions for copying this into the requisite module. --- Regards, Norman "Qaspec" wrote in message ... I've tried to use the suggestions made in the url. I can't even get to tell if I can get it to work bacause a) it is specific to modules and b) they set off the virus scan at my company. They are not going to change the scan so is there another way to export or copy a sub from the This Workbook section in one file to the This Workbook section in another file? "Norman Jones" wrote: Hi Qaspec, See Chip Pearson's 'Programming To The Visual Basic Editor' page at: http://www.cpearson.com/excel/vbe.htm --- Regards, Norman "Qaspec" wrote in message ... I am using the following code to create a new workbook and send via outlook to whatever adress the user enters. Private Sub Send1_Click() Dim strdate As String Sheets(Array("Emp24", "Main")).Copy strdate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss") Worksheets("Emp24").Name = "NewData" ActiveWorkbook.SaveAs "NewEmployeeData.xls" ActiveWorkbook.SendMail "", _ "Employee Attendance Data" ActiveWorkbook.ChangeFileAccess xlReadOnly Kill ActiveWorkbook.FullName ActiveWorkbook.Close False End Sub In the original workbook inside the This Workbook section I have the following: Private Sub Workbook_Open Dim lngColor As Long lngColor = RGB(221, 221, 221) ActiveWorkbook.Colors(16) = lngColor End Sub How can I place this 2nd code into the This Workbook section of the file being sent i the first sub? Thank You for any help. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Place code from current workbook to new workbook
I apologize for looking in the wrong section of the page you referred me to.
Instead of exporting modules I should have looked at creating an event. Using that and tweaking it I was able to modify my macro. Thanks for pointing me in the correct direction. So just in case anyone else has this similiar situation. Here is my finished working Sub. Private Sub Send1_Click() Dim strdate As String ActiveSheet.Copy strdate = Format(Date, "mm-dd-yy") ActiveSheet.Name = "Snapshot" Dim StartLine As Long With ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule StartLine = .CreateEventProc("Open", "Workbook") + 1 .InsertLines StartLine, _ "ActiveWorkbook.Colors(16) = lngColor" .InsertLines StartLine, _ "lngColor = RGB(221, 221, 221)" .InsertLines StartLine, _ "Dim lngColor As Long" End With ActiveWorkbook.SaveAs "QA Snapshot" & strdate & ".xls" Application.VBE.MainWindow.Visible = False ActiveWorkbook.SendMail "", _ "QA Snapshot" & " " & Range("A8") ActiveWorkbook.ChangeFileAccess xlReadOnly Kill ActiveWorkbook.FullName ActiveWorkbook.Close False End Sub Basically this allows me to copy the worksheet, save it as a new workbook that I can send to anyone I want and allows me to keep a custom color I created for my charts that the recipient will be able to see correctly when they open the new workbook. "Norman Jones" wrote: Hi Qaspec, Your company blocks VB(A) macros because they might contain viruses. If it were possible readily to circumvent this, virus writers would be able to gain unfettered access to the company's computer systems. so is there another way to export or copy a sub from the This Workbook section in one file to the This Workbook section in another file? AFAIK, only with the assistance of the recipient. Perhaps, for example, you could send the macro as text together with instructions for copying this into the requisite module. --- Regards, Norman "Qaspec" wrote in message ... I've tried to use the suggestions made in the url. I can't even get to tell if I can get it to work bacause a) it is specific to modules and b) they set off the virus scan at my company. They are not going to change the scan so is there another way to export or copy a sub from the This Workbook section in one file to the This Workbook section in another file? "Norman Jones" wrote: Hi Qaspec, See Chip Pearson's 'Programming To The Visual Basic Editor' page at: http://www.cpearson.com/excel/vbe.htm --- Regards, Norman "Qaspec" wrote in message ... I am using the following code to create a new workbook and send via outlook to whatever adress the user enters. Private Sub Send1_Click() Dim strdate As String Sheets(Array("Emp24", "Main")).Copy strdate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss") Worksheets("Emp24").Name = "NewData" ActiveWorkbook.SaveAs "NewEmployeeData.xls" ActiveWorkbook.SendMail "", _ "Employee Attendance Data" ActiveWorkbook.ChangeFileAccess xlReadOnly Kill ActiveWorkbook.FullName ActiveWorkbook.Close False End Sub In the original workbook inside the This Workbook section I have the following: Private Sub Workbook_Open Dim lngColor As Long lngColor = RGB(221, 221, 221) ActiveWorkbook.Colors(16) = lngColor End Sub How can I place this 2nd code into the This Workbook section of the file being sent i the first sub? Thank You for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy and paste using code from workbook to workbook | Excel Discussion (Misc queries) | |||
Close a the current workbook and load another specified workbook | Excel Programming | |||
Copy a range of cells in an unopened workbook and paste it to the current workbook | Excel Programming | |||
Place code in a new workbook with a macro. | Excel Programming | |||
Transparently updating another workbook from the current workbook | Excel Programming |