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

[µo°Ý] ¦Û°Ê²£¥Í¸ê®Æ§¨

[µo°Ý] ¦Û°Ê²£¥Í¸ê®Æ§¨

Sir,
§Ú¦³¤@­Ó"NEDoc" ªº¤å¥ó,§Ú§Æ±æ¥¦¯à¥Hsheet:INVªºÀx¦s®æS8_Q8_I8_¦Û©R¬°¤@­Ó·sªº¸ê®Æ§¨"55767_TE_CI12-182",¨ä¤¤S8¬°«È¤Hªº­q³æ¸¹½X,¥u¨ú¥kÃ䪺5½X´N¦n,
·s¸ê®Æ§¨¦WºÙ¤Î¸ô®|¬°P:\Shipping Doc\NE-Logistics\55767_TE_CI12-182
¥¨¶°¦s©ñ¦ì¸m¤ÎÀɮ׬°P:\PJ-Home\VBA Cluster
­ì"NEDoc"¤å¥óªº¦s©ñ¦ì¸m¤ÎÀɦW¬°P:\PJ-Home\NEDoc
§Úªº»Ý¨D¦p¤U,½Ð¨D¦U¦ìÀ°¦£:
1 ¦Û°Ê²£¥Í·sªº¸ê®Æ§¨(¤è¦¡¦p«e©Ò´£¤Î)
2 ²£¥Í¸ê®Æ§¨«á,­n­ì¥»ªº"NEDoc"¥HÀx¦s®æI8_PO#S8¥t¦s¤@­Ó·sªºÀɮצWºÙ(§Y:CI12-182_PO#4500055767)¸m©ó¸ê®Æ§¨"55767_TE_CI12-182"¤¤

NEDoc.zip (52.69 KB)

¦^´_ 1# PJChen
¸Õ¸Õ¬Ý
  1. Sub Ex()
  2. Set fdo = CreateObject("Scripting.FilesystemObject")
  3. With Workbooks.Open("P:\PJ-Home\NEDoc.xlsx")
  4.     With .Sheets("INV")
  5.        fs = Right(.[S8], 5) & "_" & .[Q8] & "_" & .[I8]
  6.        fd = "P:\Shipping Doc\NE-Logistics\" & fs
  7.        k = fdo.folderexists(fd)
  8.        ar = Split(fd, "\")
  9.        fn = ar(0)
  10.        For i = 1 To UBound(ar)
  11.        fn = fn & "\" & ar(i)
  12.           If fdo.folderexists(fn) = False Then fdo.createfolder fn
  13.        Next
  14.     End With
  15.     .SaveCopyAs fd & "\" & fs & ".xlsx"
  16.     .Close
  17. End With
  18. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# Hsieh

ÁÂÁ±z,°õ¦æ«Ü¦¨¥\!
§Ú­Y·Q±NNEDoc¥´¶}«á(¦]¦³®ÉÁÙ­n­×§ï¤å¥ó)¦A°õ¦æ¤W­zªº¥¨¶°,½Ð°Ý­n¦p¦ó§ï?
¬O§_§ï¥H¤Uªºµ{¦¡:With Workbooks.Open("P:\PJ-Home\NEDoc.xlsx")
§ï¦¨ With Workbooks("P:\PJ-Home\NEDoc.xlsx") ,¦ý¥¦¤£¯à°õ¦æ,½Ð°Ý°ÝÃD¥X¦b­þ¸Ì?

TOP

¦^´_ 2# Hsieh
¥t¥~­è¤~¨S¦³Àˬd¨ì,ExcelªºÀɮצWºÙ­n¥Î¥H¤Uªº¤è¦¡¦sÀÉ,½Ð°Ý¸Ó«ç»ò­×§ïµ{¦¡?
²£¥Í¸ê®Æ§¨«á,­n­ì¥»ªº"NEDoc"¥HÀx¦s®æI8_PO#S8¥t¦s¤@­Ó·sªºÀɮצWºÙ(§Y:CI12-182_PO#4500055767)¸m©ó¸ê®Æ§¨"55767_TE_CI12-182"¤¤

TOP

¦^´_ 2# Hsieh
§Ú·Q±Nµo²{ªº°ÝÃDÁ`µ²¦p¤U,½Ð¦A«ü±Ð!
1)  ExcelªºÀɮצWºÙ­n¥Î¥H¤Uªº¤è¦¡¦sÀÉ,½Ð°Ý¸Ó«ç»ò­×§ïµ{¦¡? (¨Ò:§Y:CI12-182_PO#4500055767)
       ²£¥Í¸ê®Æ§¨«á,­n­ì¥»ªº"NEDoc"¥HÀx¦s®æI8_PO#S8¥t¦s¤@­Ó·sªºÀɮצWºÙ(§Y:CI12-182_PO#4500055767)¸m©ó¸ê®Æ§¨"55767_TE_CI12-182"¤¤
2)  §Ú­Y·Q±NNEDoc¥´¶}«á(¦]¦³®ÉÁÙ­n­×§ï¤å¥ó)¦A°õ¦æ¤W­zªº¥¨¶°,½Ð°Ý­n¦p¦ó§ï? ¬O§_§ï¥H¤Uªºµ{¦¡:With Workbooks.Open("P:\PJ-Home\NEDoc.xlsx")
       §ï¦¨ With Workbooks("P:\PJ-Home\NEDoc.xlsx") ,¦ý¥¦¤£¯à°õ¦æ,½Ð°Ý°ÝÃD¥X¦b­þ¸Ì?
3)  ­Y­ì"NEDoc"¤å¥óªº¦s©ñ¦ì¸m¤ÎÀɦW¬°P:\PJ-Home\NEDoc,¦b°õ¦æ«á§Ú¤£·QÂл\­ìÀɦW,
       ¥u­n¥¦°õ¦æ«áÀx¦s¦b·s¸ê®Æ§¨¦WºÙ¤Î¸ô®|¬°P:\Shipping Doc\NE-Logistics\55767_TE_CI12-182¤¤,¤S¸Ó§ó§ï­þ­Ó³¡¥÷?

TOP

¦^´_ 5# PJChen

1)  ExcelªºÀɮצWºÙ­n¥Î¥H¤Uªº¤è¦¡¦sÀÉ,½Ð°Ý¸Ó«ç»ò­×§ïµ{¦¡? (¨Ò:§Y:CI12-182_PO#4500055767)
       ²£¥Í¸ê®Æ§¨«á,­n­ì¥»ªº"NEDoc"¥HÀx¦s®æI8_PO#S8¥t¦s¤@­Ó·sªºÀɮצWºÙ(§Y:CI12-182_PO#4500055767)¸m©ó¸ê®Æ§¨"55767_TE_CI12-182"¤¤
ANS1:³o¥u¬OÀɦW³W«h°ÝÃD¡A­ì¥»fs = Right(.[S8], 5) & "_" & .[Q8] & "_" & .[I8]ÀɦW·|¬O55767_TE_CI12-182
           fs = .[I8] & "-PO#" & "_" & .[S8]ÀɦW´N¬OCI12-182_PO#4500055767

2)  §Ú­Y·Q±NNEDoc¥´¶}«á(¦]¦³®ÉÁÙ­n­×§ï¤å¥ó)¦A°õ¦æ¤W­zªº¥¨¶°,½Ð°Ý­n¦p¦ó§ï? ¬O§_§ï¥H¤Uªºµ{¦¡:With Workbooks.Open("P:\PJ-Home\NEDoc.xlsx")
       §ï¦¨ With Workbooks("P:\PJ-Home\NEDoc.xlsx") ,¦ý¥¦¤£¯à°õ¦æ,½Ð°Ý°ÝÃD¥X¦b­þ¸Ì?
ANS2:Àɮפw¸g¶}±Òª¬ºA¥u»Ý¨Ï¥Îµøµ¡ªºCaption«ü©w¡A§ï¦¨With Windows("NEDoc.xlsx") §Y¥i¡C

3)  ­Y­ì"NEDoc"¤å¥óªº¦s©ñ¦ì¸m¤ÎÀɦW¬°P:\PJ-Home\NEDoc,¦b°õ¦æ«á§Ú¤£·QÂл\­ìÀɦW,
       ¥u­n¥¦°õ¦æ«áÀx¦s¦b·s¸ê®Æ§¨¦WºÙ¤Î¸ô®|¬°P:\Shipping Doc\NE-Logistics\55767_TE_CI12-182¤¤,¤S¸Ó§ó§ï­þ­Ó³¡¥÷?
ANS3:¦P¥Ø¿ý¤U¤£¥i¦³­«½ÆÀɦW¡A©Ò¥H³o³¡¤ÀµLªk¤F¸Ñ§AªºÅÞ¿è¡C
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 6# Hsieh

1)  ½Ð°Ý With Workbooks(":\PJ-Home\NEDoc.xlsx")»P With Windows("NEDoc.xlsx") ½Ð°Ý³o2­Óªº®t§O·N¸q¦b­þ¸Ì?

2)  ­ì"NEDoc"¤å¥óªº¦s©ñ¸ô®|¬°P:\PJ-Home\NEDoc(­ìÀɦW)
      ·s¸ê®Æ§¨¦WºÙ¤Î¸ô®|¬°P:\Shipping Doc\NE-Logistics\(+·sÀɦW)
¦b°õ¦æ¥¨¶°«á§Ú¤£·Q¦b±N¸ô®|¬°P:\PJ-HomeÂл\­ìÀɦW(²{¦æªº¥¨¶°·|°Ý§Ú¬O§_¦sÀÉ?¦]¬°§Ú¥H¬°¥¦¬O°Ý§Ú¦s©ñ¦b·s¸ô®|ªºÀɮק@Âл\,´N«ö¤F"¬O",µM«á­ì¥ý¦³¤½¦¡ªºÀÉ®×´N³QÂл\¤F...
       ·íµM¥¦°õ¦æ¥¨¶°«á¤]·|Àx¦s¦b·s¸ê®Æ§¨¦WºÙ¤Î¸ô®|¬°P:\Shipping Doc\NE-Logistics\55767_TE_CI12-182¤¤.

³q±`¥´¶}¤@­ÓÀÉ®×·í§A¦A¦¸¦sÀÉ®É,·íµM¥i¥HÂл\­ìÀÉ®×,¦ý¤]¥i¥Hª½±µ«ü¦V§Oªº¸ô®|¥t¦s·sÀÉ,§Ú­nªº«K¬O³oºØªº!
§Ú¤£§Æ±æ¥¦°õ¦æ¥¨¶°«á¦b­ì¨Óªº¸ô®|¤¤°Ý§Ú­n¤£­n¦sÀÉ,«Ü®e©öÅý§Ú¿ù¶Ã,¤@¤£¤p¤ß«K¦s¿ùÀɮפF.

TOP

¦^´_ 6# Hsieh
§Ú¸Õ¤F´X¦¸,¥u­nÀÉ®×¥´¶}ªº¸Ü, ,¥H¤U¤è¦¡³£¤£¯à°õ¦æ,½Ð°Ý°ÝÃD¥X¦b­þ¸Ì?
With Workbooks("P:\PJ-Home\NEDoc.xlsx")
With Windows("NEDoc.xlsx")
With Workbooks("NEDoc.xlsx")
   With ActiveWorkbook("P:\PJ-Home\NEDoc.xlsx")
   With ActiveWorkbook("NEDoc.xlsx")

TOP

¦^´_ 8# PJChen
³oÃö¥G¨ì§Aªº¶}Àɰʧ@
°²³]µ{¦¡½X¬O¦bTEST.xlsmÀɮפº
¦pªG¥HTEST.xlsmªºµ{¦¡½X¶}±ÒNEDoc.xlsx
NEDoc.xlsx»Pµ{¦¡½X©Ò¦bªºÀɮ׬O¦P¤@­Ó±M®×(Application)¤Uªº2­Óµøµ¡
¨º»ò¡AWith Windows("NEDoc.xlsx")À³¸Ó¥i¥H°õ¦æ
¦pªG¡ANEDoc.xlsx¬O¥H¤â°Ê¶}±Ò«á°µ­×§ï
¨º»ò­n¬Ý§AªºTEST.xlsm¶}±Ò«á¬O¨â­ÓEXCEL±M®×©Î¬O¦P¤@­Ó±M®×
¦pªG¦P¤@­Ó±M®×With Workbooks("NEDoc.xlsx")À³¥i¦¨¥ß
­Y«D¦P¤@­Ó±M®×¨º»òµ{¦¡½X¨ÃµLªk¸ó±M®×¨ú±oEXCELªº¬¡­¶Ã¯ª«¥ó
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 9# Hsieh

§Úªº¥¨¶°µ{¦¡³£©ñ¦bP:\PJ-Home\VBA Cluster.xlsm
­ì"NEDoc"¤å¥óªº¦s©ñ¦ì¸m¤ÎÀɦW¬°P:\PJ-Home\NEDoc.xlsx
°õ¦æ¥¨¶°«á=>·s¸ê®Æ§¨¦WºÙ¤Î¸ô®|¬°P:\Shipping Doc\NE-Logistics\55767_TE_CI12-182.xlsx
¬O¨â­Ó¤£¦PªºEXCELÀÉ,¦ý¥¨¶°¥þ³¡©ñ¦bVBA Cluster.xlsm,
³o¼ËÁٯण¯à¨Ì§Úªº»Ý¨D:
1)  NEDoc¦b¶}±Òª¬ºA¤U,¦A°õ¦æ¤W­zªº¥¨¶°
2)  °õ¦æ¥¨¶°«á¤£­nÂл\­ìÀɦW,¥u­nÀx¦s¦b·s¸ê®Æ§¨¦WºÙ¤Î¸ô®|¬°P:\Shipping Doc\NE-Logistics\·sÀɦW

TOP

        ÀR«ä¦Û¦b : ¥¬¬I¦p¼½ºØ¡A¥HÅw³ß¤ß´þ¼íºØ¤l¡A¤~·|µoªÞ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD