ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¦h±i¤u§@ªí¸ê®Æ¾ã¦X©óÁ`ªí

¦^´_ 11# missbb

¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub ex()
  3.     Set d = CreateObject("Scripting.Dictionary")
  4.     With Worksheets("attendance report")
  5.         For Each a In .Range(.[E4], .[E4].End(xlDown))
  6.             d(a.Value) = ""         '¨ú±o©Ò¦³¤£­«½Æ¤À©±
  7.         Next
  8.         F = InputBox("Enter your month")
  9.         For Each ky In d.keys
  10.             .Range("B4").AutoFilter Field:=4, Criteria1:=ky
  11.             If Dir("C:\Users\mariasfy\Desktop\" & ky & "_" & F & ".pdf") <> "" Then Kill "C:\Users\mariasfy\Desktop\" & ky & "_" & F & "201507.pdf" '¦P¦WÀɮקR°£
  12.             .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
  13.             "C:\Users\mariasfy\Desktop\" & ky & "_" & F & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
  14.             IgnorePrintAreas:=False, OpenAfterPublish:=False  '¥t¦s¦¨PDFÀÉ®×
  15.             '************************************************************
  16.             SendMail "C:\Users\mariasfy\Desktop\" & ky & "_" & F & ".pdf"
  17.             '************************************************************
  18.         Next
  19.     End With
  20. End Sub
  21. Sub SendMail(xFile As String)
  22.     'VBA ¥\¯àªí«ü¥O: ¤u¨ã->³]©w¤Þ¥Î¶µ¥Ø ·s¼W -> [Microsoft CDO for Windows 2000 Library]
  23.     '¥i Google   CreateObject("CDO.Message") ¤F¸Ñ
  24.     Dim objEmail As Object
  25.     Set objEmail = CreateObject("CDO.Message")      '«Ø¥ß CDO ª«¥ó
  26.     With objEmail
  27.         With .Configuration.Fields
  28.             .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
  29.             .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "msa.hinet.net"              '¨Ï¥Î msa.hinet.net ¶Ç°e¶l¥ó
  30.             .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
  31.             .Update
  32.         End With
  33.         .From = "±H¥óªÌ@pchome.com"           '±H¥óªÌ¡]ºô°ì¥²¶·¦s¦b¡^
  34.         .To = "¦¬¥óªÌ@gmail.com"
  35.         .Subject = "CreateObjectCDO.Message CDO¶l¥ó´ú¸Õ"    '¶l¥ó¥D¦®
  36.         .HTMLBody = "¶l¥ó¥»¤å"   'HTML¶l¥ó¤º¤å
  37.         .AddAttachment xFile   'ªþÀÉ
  38.         .Send
  39.     End With
  40. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 11# GBKEE

Åý§Ú¸Õ¤@¤U, ·P¿E!:'(

TOP

¥»©«³Ì«á¥Ñ missbb ©ó 2015-8-8 23:29 ½s¿è

¦^´_ 12# missbb

§Ú¥i¥H±N¤u§@ªíATTENDANCE REPORT V3:AZ3ªº¤é´ÁÂà¸m¨ì¤u§@ªíLEAVE SUMMAy, ¦ýv3:AZ3¦p¦ó¥Hcopy 30¦¸¹ïÀ³¨C­Ó­û¤u, ¦]­û¤u¤£­û1­Ó?

¦p¦ó¥i¥H±N¨C­Ó­û¤uªºATTENDANCE REPORT¤ºªº"°²´Á/¨Ò°²/³Æµù"¦C¤ºªº³Ò©Î¨Ò«ö­û¤u¤Î¤é´Á°t¸m©óleave summaryªºdÄæ©O? ¥Î¨ç¼Æ(IF(ISERROR(INDEX('ATTENDANCE REPORT'!$1:$1048576,MATCH($A$2,'ATTENDANCE REPORT'!$M:$M,0),MATCH(DAY($C2),'ATTENDANCE REPORT'!$3:$3,0))),"",INDEX('ATTENDANCE REPORT'!$1:$1048576,MATCH($A$2,'ATTENDANCE REPORT'!$M:$M,0),MATCH(DAY($C2),'ATTENDANCE REPORT'!$3:$3,0)))¬O¥i¥H°µ¨ì, ©Î¦p¦ó±N¨ç¼Æ¥[¤Jvba¤º?

¥¼§¹ªºvba:
Sheets("attendance report").Range("v3:az3").Copy
Sheets("leave summary").Range("c2").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks:=False, Transpose:=True
   
·P¿E! Âà¸m¨ç¼Æ.rar (285.08 KB)

TOP

        ÀR«ä¦Û¦b : ¤£©È¨Æ¦h¡A¥u©È¦h¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD