[ 2012 @ 23.02.2015. 14:32 ] @
Prvo da napomenem da sam detalnji pretrazio ceo Excel podforum pre postavljanja ovog pitanja. Iskreno verujem da je pitanje zanimljivo i da cemo zajedno doci do resenja. Imam jednu excel aplikaciju (to je izvestaj) koji se svakodnevno dopunjuje, po zavrsetku se izmene snime i taj deo savrseno radi, tu nemam pitanja. Medjutim ostaju sledeci problemi, po zavrsetku izvestaja izvestaj zelim: - snimiti na drugoj particiji (ali samo value i da se zadrzi izgled - formating, bez formula) - dokument treba poslati meilom ( ali zelim poslati samo value i format, bez formula) Trazio sam po internetu, kupio sam i jednu veoma prakticnu knjigu u vezi toga, ali mi sve to ne resava problem. Ja cu zakaciti fajlove koji delimicno resavaju problem, kao i sam code, pa da probamo da nadjemo resenje, verujem da se cesto ovakva potreba moze javiti i drugim diskutantima. Ovo je macro koji pravi rezervnu kopiju dokumenta sa dodavanjem datuma. Gde u ovom kodu dodati lokaciju i gde treba dodati da kopirani fajl sadrzi samo value vrednosti i da se zadrzi izgled (format) Code: Sub Macro15() 'Step 1: Save workbook with new file name ThisWorkbook.SaveCopyAs _ Filename:=ThisWorkbook.Path & "\" & _ Format(Date, "MM-DD-YY") & " " & _ ThisWorkbook.Name End Sub Sledeci makro je za slanje aktivne knjige (workbook) kao attachment Code: Sub Macro85() 'Step 1: Declare your variables Dim OLApp As Outlook.Application Dim OLMail As Object 'Step 2: Open Outlook start a new mail item Set OLApp = New Outlook.Application Set OLMail = OLApp.CreateItem(0) OLApp.Session.Logon 'Step 3: Build your mail item and send With OLMail .To = "[email protected]; [email protected]" .CC = "" .BCC = "" .Subject = "This is the Subject line" .Body = "Hi there" .Attachments.Add ActiveWorkbook.FullName .Display 'Change to .Send to send without reviewing End With 'Step 4: Memory cleanup Set OLMail = Nothing Set OLApp = Nothing End Sub Opet imam problem sto salje kompletnu knjigu sa formulama, a meni treba samo value vrednosti Sledeci makro salje pojedinacnu stranu iz knjige Code: Sub Macro87() 'Step 1: Declare your variables Dim OLApp As Outlook.Application Dim OLMail As Object 'Step 2: Copy Worksheet, paste to new workbook, and save it Sheets("Revenue Table").Copy ActiveWorkbook.SaveAs ThisWorkbook.Path & "\TempRangeForEmail.xlsx" 'Step 3: Open Outlook start a new mail item Set OLApp = New Outlook.Application Set OLMail = OLApp.CreateItem(0) OLApp.Session.Logon 'Step 4: Build your mail item and send With OLMail .To = "[email protected]; [email protected]" .CC = "" .BCC = "" .Subject = "This is the Subject line" .Body = "New Workbook Attached" .Attachments.Add (ThisWorkbook.Path & "\TempRangeForEmail.xlsx") .Display 'Change to .Send to send without reviewing End With 'Step 5: Delete the temporary Excel file ActiveWorkbook.Close SaveChanges:=True Kill ThisWorkbook.Path & "\TempRangeForEmail.xlsx" 'Step 6: Memory cleanup Set OLMail = Nothing Set OLApp = Nothing End Sub Ali ni ovo ne vrsi posao zato sto u knjizi (izvestaju ima 4-5 strana (sheets) Poslednji makro salje odredjeno podrucje iz dokumenta (knjige) kao attachment Code: Sub Macro86() 'Step 1: Declare your variables Dim OLApp As Outlook.Application Dim OLMail As Object 'Step 2: Copy range, paste to new workbook, and save it Sheets("Revenue Table").Range("A1:E7").Copy Workbooks.Add Range("A1").PasteSpecial xlPasteValues Range("A1").PasteSpecial xlPasteFormats ActiveWorkbook.SaveAs ThisWorkbook.Path & "\TempRangeForEmail.xlsx" 'Step 3: Open Outlook start a new mail item Set OLApp = New Outlook.Application Set OLMail = OLApp.CreateItem(0) OLApp.Session.Logon 'Step 4: Build your mail item and send With OLMail .To = "[email protected]; [email protected]" .CC = "" .BCC = "" .Subject = "This is the Subject line" .Body = "Hi there" .Attachments.Add (ThisWorkbook.Path & "\TempRangeForEmail.xlsx") .Display 'Change to .Send to send without reviewing End With 'Step 5: Delete the temporary Excel file ActiveWorkbook.Close SaveChanges:=True Kill ThisWorkbook.Path & "\TempRangeForEmail.xlsx" 'Step 6: Memory cleanup Set OLMail = Nothing Set OLApp = Nothing End Sub U ovom makrou ima navedeno da ono sto se salje bude values i da bude formatirano, ali je problem sto makro salje samo odredjeni deo knjige. Kako da se iskombinuje da se naprave dva odvojena makroa koji ce snimiti rezervnu kopiju i drugi koji ce poslati kopiju meilom, ali da to u oba slucaja bude samo value bez formula. |