Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dumping data to .pdf, taking screenshots, mirroring a sheet, or other report type?
Hello all,
I have a tool that essentially takes a snapshot of the current status of the company. This is run every morning to encompass the entire previous day's activities. The next step in progression with this tool is to send a summary out to all managers / VP's, etc. I'd love to come up with something in VBA to make this easier. Currently this already logs all data into access for historical tracking over time, but I would prefer to come up with a way to either take a screenshot, create a pdf of the sheet, or create a new sheet lacking formulas but containing the values and formatting. This is a long shot, but can it be done? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dumping data to .pdf, taking screenshots, mirroring a sheet, or ot
Take a look at this...
http://www.excelguru.ca/node/21 -- HTH... Jim Thomlinson "S Davis" wrote: Hello all, I have a tool that essentially takes a snapshot of the current status of the company. This is run every morning to encompass the entire previous day's activities. The next step in progression with this tool is to send a summary out to all managers / VP's, etc. I'd love to come up with something in VBA to make this easier. Currently this already logs all data into access for historical tracking over time, but I would prefer to come up with a way to either take a screenshot, create a pdf of the sheet, or create a new sheet lacking formulas but containing the values and formatting. This is a long shot, but can it be done? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dumping data to .pdf, taking screenshots, mirroring a sheet, or ot
On Feb 13, 11:02 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote: Take a look at this... http://www.excelguru.ca/node/21 -- HTH... Jim Thomlinson "S Davis" wrote: Hello all, I have a tool that essentially takes a snapshot of the current status of the company. This is run every morning to encompass the entire previous day's activities. The next step in progression with this tool is to send a summary out to all managers / VP's, etc. I'd love to come up with something in VBA to make this easier. Currently this already logs all data into access for historical tracking over time, but I would prefer to come up with a way to either take a screenshot, create a pdf of the sheet, or create a new sheet lacking formulas but containing the values and formatting. This is a long shot, but can it be done?- Hide quoted text - - Show quoted text - Thanks for the reply.. that works great and I am going to look into using it. However, given that the generation of the PDF will be spread across more users than myself, I'm hesitant to use an outside program (ie. not Adobe). That requires widespread installation of the program, which then becomes an IS issue... real hassle. I noticed however that you can go to File - Send to - Mail Recipient, and then mail off an entire worksheet or multiple worksheets. This seems to be good enough for me, but I would like to just incorporate it into some code that would automatically happen every time I upload the history to Access via a button. Possible? Can I perhaps modify this code to mail off a selection, range, or entire worksheet?: ********* Dim OutApp As Object Dim OutMail As Object Dim strbody As String Set OutApp = CreateObject("Outlook.Application") 'Set OutMail = OutApp.CreateItem(olMailItem) Set OutMail = OutApp.CreateItem(0) strbody = "*email content deleted*" & vbNewLine & vbNewLine & _ "*email content deleted*" & vbNewLine & vbNewLine & _ "*email content deleted*" & vbNewLine & vbNewLine & _ "*email content deleted*" With OutMail .To = " .CC = " .BCC = "" .Subject = "*email subject*" .Body = strbody .Send End With Set OutMail = Nothing Set OutApp = Nothing ThisWorkbook.Close SaveChanges:=True End Sub ********* |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dumping data to .pdf, taking screenshots, mirroring a sheet, or other report type?
create a new sheet lacking formulas but containing the values and
formatting Sub MakeRecord() activesheet.copy activesheet.usedrange.formula = activesheet.usedrange.value Activeworkbook.SaveAs "C:\Archive\" & format(date-1,"yyyymmdd") & ".xls" ActiveWorkbook.Close SaveChanges:=False End Sub -- Regards, Tom Ogilvy "S Davis" wrote in message ups.com... Hello all, I have a tool that essentially takes a snapshot of the current status of the company. This is run every morning to encompass the entire previous day's activities. The next step in progression with this tool is to send a summary out to all managers / VP's, etc. I'd love to come up with something in VBA to make this easier. Currently this already logs all data into access for historical tracking over time, but I would prefer to come up with a way to either take a screenshot, create a pdf of the sheet, or create a new sheet lacking formulas but containing the values and formatting. This is a long shot, but can it be done? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dumping data to .pdf, taking screenshots, mirroring a sheet, or other report type?
Private Sub Workbook.Open()
activesheet.usedrange.formula = activesheet.usedrange.value Activeworkbook.SaveAs "C:\Archive\" & format(date-1,"yyyymmdd") & ".xls" ActiveWorkbook.SendMail " ActiveWorkbook.Close SaveChanges:=False End Sub in the Thisworkbook module. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... create a new sheet lacking formulas but containing the values and formatting Sub MakeRecord() activesheet.copy activesheet.usedrange.formula = activesheet.usedrange.value Activeworkbook.SaveAs "C:\Archive\" & format(date-1,"yyyymmdd") & ".xls" ActiveWorkbook.Close SaveChanges:=False End Sub -- Regards, Tom Ogilvy "S Davis" wrote in message ups.com... Hello all, I have a tool that essentially takes a snapshot of the current status of the company. This is run every morning to encompass the entire previous day's activities. The next step in progression with this tool is to send a summary out to all managers / VP's, etc. I'd love to come up with something in VBA to make this easier. Currently this already logs all data into access for historical tracking over time, but I would prefer to come up with a way to either take a screenshot, create a pdf of the sheet, or create a new sheet lacking formulas but containing the values and formatting. This is a long shot, but can it be done? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dumping data to .pdf, taking screenshots, mirroring a sheet, or other report type?
On Feb 13, 11:41 am, "Tom Ogilvy" wrote:
create a new sheet lacking formulas but containing the values and formatting Sub MakeRecord() activesheet.copy activesheet.usedrange.formula = activesheet.usedrange.value Activeworkbook.SaveAs "C:\Archive\" & format(date-1,"yyyymmdd") & ".xls" ActiveWorkbook.Close SaveChanges:=False End Sub Thanks Tom. I'm using this. I will do a search after this post, but for completeness sake it would be great if I could get a response on how to replace all "activesheet" with a worksheet name. Worksheet is titled "Daily Email". |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dumping data to .pdf, taking screenshots, mirroring a sheet, or other report type?
Private Sub Workbook.Open()
' you only need to change the first one ActiveWorkbook.Worksheets("Daily Email").copy ' now the activesheet/activeworkbook is the copy ActiveSheet.usedrange.formula = Activesheet.usedrange.value Activeworkbook.SaveAs "C:\Archive\" & format(date-1,"yyyymmdd") & ".xls" ActiveWorkbook.SendMail " ActiveWorkbook.Close SaveChanges:=False End Sub "S Davis" wrote in message oups.com... On Feb 13, 11:41 am, "Tom Ogilvy" wrote: create a new sheet lacking formulas but containing the values and formatting Sub MakeRecord() activesheet.copy activesheet.usedrange.formula = activesheet.usedrange.value Activeworkbook.SaveAs "C:\Archive\" & format(date-1,"yyyymmdd") & ".xls" ActiveWorkbook.Close SaveChanges:=False End Sub Thanks Tom. I'm using this. I will do a search after this post, but for completeness sake it would be great if I could get a response on how to replace all "activesheet" with a worksheet name. Worksheet is titled "Daily Email". |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dumping data to .pdf, taking screenshots, mirroring a sheet, or other report type?
Looks like the copy line got omitted. It is still needed - however, see my
response to your next question. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Private Sub Workbook.Open() activesheet.usedrange.formula = activesheet.usedrange.value Activeworkbook.SaveAs "C:\Archive\" & format(date-1,"yyyymmdd") & ".xls" ActiveWorkbook.SendMail " ActiveWorkbook.Close SaveChanges:=False End Sub in the Thisworkbook module. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... create a new sheet lacking formulas but containing the values and formatting Sub MakeRecord() activesheet.copy activesheet.usedrange.formula = activesheet.usedrange.value Activeworkbook.SaveAs "C:\Archive\" & format(date-1,"yyyymmdd") & ".xls" ActiveWorkbook.Close SaveChanges:=False End Sub -- Regards, Tom Ogilvy "S Davis" wrote in message ups.com... Hello all, I have a tool that essentially takes a snapshot of the current status of the company. This is run every morning to encompass the entire previous day's activities. The next step in progression with this tool is to send a summary out to all managers / VP's, etc. I'd love to come up with something in VBA to make this easier. Currently this already logs all data into access for historical tracking over time, but I would prefer to come up with a way to either take a screenshot, create a pdf of the sheet, or create a new sheet lacking formulas but containing the values and formatting. This is a long shot, but can it be done? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dumping data to .pdf, taking screenshots, mirroring a sheet, or other report type?
On Feb 13, 12:54 pm, "Tom Ogilvy" wrote:
Looks like the copy line got omitted. It is still needed - however, see my response to your next question. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Private Sub Workbook.Open() activesheet.usedrange.formula = activesheet.usedrange.value Activeworkbook.SaveAs "C:\Archive\" & format(date-1,"yyyymmdd") & ".xls" ActiveWorkbook.SendMail " ActiveWorkbook.Close SaveChanges:=False End Sub in the Thisworkbook module. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... create a new sheet lacking formulas but containing the values and formatting Sub MakeRecord() activesheet.copy activesheet.usedrange.formula = activesheet.usedrange.value Activeworkbook.SaveAs "C:\Archive\" & format(date-1,"yyyymmdd") & ".xls" ActiveWorkbook.Close SaveChanges:=False End Sub -- Regards, Tom Ogilvy "S Davis" wrote in message roups.com... Hello all, I have a tool that essentially takes a snapshot of the current status of the company. This is run every morning to encompass the entire previous day's activities. The next step in progression with this tool is to send a summary out to all managers / VP's, etc. I'd love to come up with something in VBA to make this easier. Currently this already logs all data into access for historical tracking over time, but I would prefer to come up with a way to either take a screenshot, create a pdf of the sheet, or create a new sheet lacking formulas but containing the values and formatting. This is a long shot, but can it be done?- Hide quoted text - - Show quoted text - This is great. Absolutely great. Im just mucking around trying to get it to mail more than one recipient but that shouldnt be too hard. As it stands, it works perfectly, just as I wanted. Thank you! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dumping data to .pdf, taking screenshots, mirroring a sheet, or other report type?
")
as an example. -- Regards, Tom Ogilvy "S Davis" wrote in message ups.com... On Feb 13, 12:54 pm, "Tom Ogilvy" wrote: Looks like the copy line got omitted. It is still needed - however, see my response to your next question. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Private Sub Workbook.Open() activesheet.usedrange.formula = activesheet.usedrange.value Activeworkbook.SaveAs "C:\Archive\" & format(date-1,"yyyymmdd") & ".xls" ActiveWorkbook.SendMail " ActiveWorkbook.Close SaveChanges:=False End Sub in the Thisworkbook module. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... create a new sheet lacking formulas but containing the values and formatting Sub MakeRecord() activesheet.copy activesheet.usedrange.formula = activesheet.usedrange.value Activeworkbook.SaveAs "C:\Archive\" & format(date-1,"yyyymmdd") & ".xls" ActiveWorkbook.Close SaveChanges:=False End Sub -- Regards, Tom Ogilvy "S Davis" wrote in message roups.com... Hello all, I have a tool that essentially takes a snapshot of the current status of the company. This is run every morning to encompass the entire previous day's activities. The next step in progression with this tool is to send a summary out to all managers / VP's, etc. I'd love to come up with something in VBA to make this easier. Currently this already logs all data into access for historical tracking over time, but I would prefer to come up with a way to either take a screenshot, create a pdf of the sheet, or create a new sheet lacking formulas but containing the values and formatting. This is a long shot, but can it be done?- Hide quoted text - - Show quoted text - This is great. Absolutely great. Im just mucking around trying to get it to mail more than one recipient but that shouldnt be too hard. As it stands, it works perfectly, just as I wanted. Thank you! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dumping data to .pdf, taking screenshots, mirroring a sheet, or other report type?
On Feb 14, 2:58 pm, "Tom Ogilvy" wrote:
","L...@ mm.com") as an example. -- Regards, Tom Ogilvy "S Davis" wrote in message ups.com... On Feb 13, 12:54 pm, "Tom Ogilvy" wrote: Looks like the copy line got omitted. It is still needed - however, see my response to your next question. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message .. . Private Sub Workbook.Open() activesheet.usedrange.formula = activesheet.usedrange.value Activeworkbook.SaveAs "C:\Archive\" & format(date-1,"yyyymmdd") & ".xls" ActiveWorkbook.SendMail " ActiveWorkbook.Close SaveChanges:=False End Sub in the Thisworkbook module. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... create a new sheet lacking formulas but containing the values and formatting Sub MakeRecord() activesheet.copy activesheet.usedrange.formula = activesheet.usedrange.value Activeworkbook.SaveAs "C:\Archive\" & format(date-1,"yyyymmdd") & ".xls" ActiveWorkbook.Close SaveChanges:=False End Sub -- Regards, Tom Ogilvy "S Davis" wrote in message roups.com... Hello all, I have a tool that essentially takes a snapshot of the current status of the company. This is run every morning to encompass the entire previous day's activities. The next step in progression with this tool is to send a summary out to all managers / VP's, etc. I'd love to come up with something in VBA to make this easier. Currently this already logs all data into access for historical tracking over time, but I would prefer to come up with a way to either take a screenshot, create a pdf of the sheet, or create a new sheet lacking formulas but containing the values and formatting. This is a long shot, but can it be done?- Hide quoted text - - Show quoted text - This is great. Absolutely great. Im just mucking around trying to get it to mail more than one recipient but that shouldnt be too hard. As it stands, it works perfectly, just as I wanted. Thank you!- Hide quoted text - - Show quoted text - Thanks:) You seem to know most everything I ever have a question for... any recommended reading or resources? And while we're at it, any way to CC some people on this email? I have two distinct management classes to deal with here... Thanks again. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dumping data to .pdf, taking screenshots, mirroring a sheet, or other report type?
I don't believe there is any provision for CC. You would need to use the
Outlook approach you posted for that. http://www.rondebruin.nl/sendmail.htm -- Regards, Tom Ogilvy "S Davis" wrote in message oups.com... On Feb 14, 2:58 pm, "Tom Ogilvy" wrote: ","L...@ mm.com") as an example. -- Regards, Tom Ogilvy "S Davis" wrote in message ups.com... On Feb 13, 12:54 pm, "Tom Ogilvy" wrote: Looks like the copy line got omitted. It is still needed - however, see my response to your next question. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message .. . Private Sub Workbook.Open() activesheet.usedrange.formula = activesheet.usedrange.value Activeworkbook.SaveAs "C:\Archive\" & format(date-1,"yyyymmdd") & ".xls" ActiveWorkbook.SendMail " ActiveWorkbook.Close SaveChanges:=False End Sub in the Thisworkbook module. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... create a new sheet lacking formulas but containing the values and formatting Sub MakeRecord() activesheet.copy activesheet.usedrange.formula = activesheet.usedrange.value Activeworkbook.SaveAs "C:\Archive\" & format(date-1,"yyyymmdd") & ".xls" ActiveWorkbook.Close SaveChanges:=False End Sub -- Regards, Tom Ogilvy "S Davis" wrote in message roups.com... Hello all, I have a tool that essentially takes a snapshot of the current status of the company. This is run every morning to encompass the entire previous day's activities. The next step in progression with this tool is to send a summary out to all managers / VP's, etc. I'd love to come up with something in VBA to make this easier. Currently this already logs all data into access for historical tracking over time, but I would prefer to come up with a way to either take a screenshot, create a pdf of the sheet, or create a new sheet lacking formulas but containing the values and formatting. This is a long shot, but can it be done?- Hide quoted text - - Show quoted text - This is great. Absolutely great. Im just mucking around trying to get it to mail more than one recipient but that shouldnt be too hard. As it stands, it works perfectly, just as I wanted. Thank you!- Hide quoted text - - Show quoted text - Thanks:) You seem to know most everything I ever have a question for... any recommended reading or resources? And while we're at it, any way to CC some people on this email? I have two distinct management classes to deal with here... Thanks again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to design a stock taking report sheet | Excel Discussion (Misc queries) | |||
Need help Taking alot data from one sheet (if not blank) and copying toa list on another sheet. | Excel Worksheet Functions | |||
Report Taking Me Too Long | Excel Programming | |||
Using a comparison and taking data from one sheet and putting it i | Excel Worksheet Functions | |||
Dumping the contents of a VBA array to a sheet | Excel Programming |