[µo°Ý] «ü©wD3Àx¦s®æ,°õ¦æ±Æ§Ç

PJChen

½Ð±Ð¤j¤j,

§Ú¦b¤u§@ªísorting.xlsmªºD3Àx¦s®æ¿é¤J¤@­Ó¤u§@ªí¦WºÙ(¦]¬°»Ý±Æ§Çªº¤u§@ªí¦³«Ü¦h,·Q¥Î°ÊºA¤èªk),¥H«ü©wD3¤u§@ªí¦WºÙ°õ¦æ±Æ§Çªº¥¨¶°µ{¦¡,¦ý¤@ª½µLªk¹B§@,
¤W¶Çªþ¥ó,¥i§_«üÂI¿ù»~!
±Æ§Ç.zip (98.38 KB)

¤U¸ü¦¸¼Æ: 15

2012-10-24 11:32

GBKEE

¦^´_ 18# PJChen
16#  °}¦C¯Á¤Þ¶W¥X½d³ò

1. With Workbooks(Workbooks("¶Kñ¦W.xlsm").Worksheets("EX").Range("D3").Value)  '
   
2.    .Activate  '  ¥[³o¸Ì...

½Æ»s¥N½X

17# ¾ãµÛ¤u§@ªí¥X²{copy«áªºµê½u°{°Ê

1. Application.CutCopyMode = False  'µ{¦¡µ²§ô«e¥[¤W

½Æ»s¥N½X

18# ¨C°õ¦æ¥¨¶°¤@¦¸,¦s¦¨TXTªºÀɦWArctic_USA´N·|­«½Æ¤@¦¸!

1. Set Rng(2) = .Sheets("Booking").[A1] 'Rng(2) ¦sÀɦWºÙªºÀx¦s®æ
   
2. 'Rng(2) = Rng(2) & "_" & Rng(2).Offset(1) & "_" & Rng(2).Offset(2) 'Rng(2) ¦sÀÉÀɦW¶·¦h¥[[A2]ªºÀx¦s®æ¦WºÙ
   
3. Save_Name = Rng(2) & "_" & Rng(2).Offset(1) & "_" & Rng(2).Offset(2)'§ï³o¼Ë¼g
   
4. '
   
5. '
   
6. Set A = Fs.CreateTextFile("P:\TXT\" & Save_Name & ".txt", True) '«Ø¥ß¤å¦rÀÉ®×
   
7. '
   
8. '
   
9. A.Close
   
10. Shell "Cmd /c start P:\TXT\" & Save_Name & ".txt" '¦Û°Ê¥´¶}TXTÀÉ

½Æ»s¥N½X

PJChen

¦^´_ 9# GBKEE
G¤j¦ü¥G¤£¦b®a, ¤£ª¾¦³¤H¥i¥HÀ°§U§Ú¶Ü?
§Ú¦A¦¸­×§ï¤Fµ{¦¡,µo²{¤F¥t¤@­Ó°ÝÃD:("¶Kñ¦W.xlsm").Worksheets("EX").Range("D3")ªº«ü©wÀÉ®×­Y¥¼Ãö³¬,«h¨C°õ¦æ¥¨¶°¤@¦¸,¦s¦¨TXTªºÀɦWArctic_USA´N·|­«½Æ¤@¦¸!

1. Sub txt()
   
2. 
   
3. Workbooks(Workbooks("¶Kñ¦W.xlsm").Worksheets("EX").Range("D3").Value).Activate '¥ý²¾¨ì³o¬¡­¶Ã¯
   
4. 'Creat a TXT
   
5. With Sheets("Booking").Activate
   
6. Cells.Select
   
7. Selection.Copy
   
8. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
   
9. :=False, Transpose:=False
   
10. Dim Rng(1 To 2) As Range, Fs As Object, A As Object, E As Range
    
11. Dim S As Variant, xS As Variant
    
12. Application.ScreenUpdating = False
    
13. End With
    
14. 
    
15. With Workbooks(Workbooks("¶Kñ¦W.xlsm").Worksheets("EX").Range("D3").Value)  '§ï³o¸Ì...
    
16. Sheets("Booking").Select  '³o¥y¦³µL,µ²ªG³£¬Û¦P
    
17. Set Rng(1) = .Sheets("Booking").[B1:B45] 'Rng(1) ¤u§@ªí­n½Æ»sªº½d³ò
    
18. Set Rng(2) = .Sheets("Booking").[A1] 'Rng(2) ¦sÀɦWºÙªºÀx¦s®æ
    
19. Rng(2) = Rng(2) & "_" & Rng(2).Offset(1) & "_" & Rng(2).Offset(2) 'Rng(2) ¦sÀÉÀɦW¶·¦h¥[[A2]ªºÀx¦s®æ¦WºÙ
    
20. End With
    
21. 
    
22. Set Fs = CreateObject("Scripting.FileSystemObject") 'FileSystemObject ª«¥ó ´£¨Ñ¹ï¹q¸£Àɮרt²Îªº¦s¨ú¡C
    
23. Set A = Fs.CreateTextFile("P:\TXT\" & Rng(2) & ".txt", True) '«Ø¥ß¤å¦rÀÉ®×
    
24. 
    
25. Debug.Print Rng(1).Cells(27)  'Debug.Print :§Y®É¹Bºâµøµ¡ ¥i¨£¨ì RAttn:? Mr. Peter Holfelder ¤¤ ? ¬° ¤£¥i¨£¦r¤¸ 160"
    
26. Rng(1).Replace ChrW(160), ""   '®ø°£¤£¥i¦r¤¸ 160
    
27. 
    
28. For Each E In Rng(1) '¨Ì§Ç³B¸Ì½Æ»s½d³òªºÀx¦s®æ
    
29. S = Split(E, Chr(10))
    
30. If UBound(S) > -1 Then '***¦³´«¦æ ªº¤å¦r
    
31. For Each xS In S
    
32. A.WriteLine (xS) 'Àx¦s®æ¼g¤J¤å¦rÀÉ
    
33. Next
    
34. Else
    
35. A.WriteLine (E.Text) 'Àx¦s®æ¼g¤J¤å¦rÀÉ
    
36. End If
    
37. Next
    
38. A.Close
    
39. 
    
40. Shell "Cmd /c start P:\TXT\" & Rng(2) & ".txt" '¦Û°Ê¥´¶}TXTÀÉ
    
41. Application.ScreenUpdating = True
    
42. 
    
43. End Sub

½Æ»s¥N½X

PJChen

¦^´_ 9# GBKEE

¤j¤j,

§Ú¸ÕµÛ­×§ï¤F¤@³¡¥÷µ{¦¡,µ{¦¡ªº¥Î·N¦b¥H"¶Kñ¦W.xlsm"ªº"EX" sheetªº«ü©wÀx¦s®æD3¤ºªºÀɦW,°õ¦æ"TXT"ªº¥¨¶°,²{¦b¥¦¥i¥H°õ¦æ¥¨¶°,¦ý¥X²{¤F­Ó¿ù»~,¥i§_À°¦£«üÂI? ..¥ýÁÂÁ¤F.
µ{¦¡°õ¦æ®É,À³¸Ó±N Shipping for ACE.xlsxªº"Booking" sheetªº¸ê®Æcopy ¨ìTXT¤¤,²{¦b¥¦ÁöµM°µ¨ì¤F,¥i¬O³s¦P"¶Kñ¦W.xlsm"ªº"EX" sheet¤]¦³copyªº°Ê§@(¾ãµÛ¤u§@ªí¥X²{copy«áªºµê½u°{°Ê),§Ú¸Ó¦p¦ó­×§ï,Åý¥¦¤£­n°õ¦æ¤£¥¿½Tªºcopy°Ê§@?

¥H¤U¬O­×§ï§¹«áªºµ{¦¡:

1. Sub txt()
   
2. With Workbooks(Workbooks("¶Kñ¦W.xlsm").Worksheets("EX").Range("D3").Value)  '§ï³o¸Ì...
   
3. 'Creat a TXT
   
4. With .Sheets("Booking")
   
5. Cells.Select
   
6. Selection.Copy
   
7. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
   
8. :=False, Transpose:=False
   
9. Dim Rng(1 To 2) As Range, Fs As Object, A As Object, E As Range
   
10. Dim S As Variant, xS As Variant
    
11. Application.ScreenUpdating = False
    
12. End With
    
13. End With
    
14. 
    
15. With Workbooks(Workbooks("¶Kñ¦W.xlsm").Worksheets("EX").Range("D3").Value)  '§ï³o¸Ì...
    
16. Set Rng(1) = .Sheets("Booking").[B1:B45] 'Rng(1) ¤u§@ªí­n½Æ»sªº½d³ò
    
17. Set Rng(2) = .Sheets("Booking").[A1] 'Rng(2) ¦sÀɦWºÙªºÀx¦s®æ
    
18. Rng(2) = Rng(2) & "_" & Rng(2).Offset(1) & "_" & Rng(2).Offset(2) 'Rng(2) ¦sÀÉÀɦW¶·¦h¥[[A2]ªºÀx¦s®æ¦WºÙ
    
19. End With
    
20. 
    
21. Set Fs = CreateObject("Scripting.FileSystemObject") 'FileSystemObject ª«¥ó ´£¨Ñ¹ï¹q¸£Àɮרt²Îªº¦s¨ú¡C
    
22. Set A = Fs.CreateTextFile("P:\TXT\" & Rng(2) & ".txt", True) '«Ø¥ß¤å¦rÀÉ®×
    
23. 
    
24. Debug.Print Rng(1).Cells(27)  'Debug.Print :§Y®É¹Bºâµøµ¡ ¥i¨£¨ì RAttn:? Mr. Peter Holfelder ¤¤ ? ¬° ¤£¥i¨£¦r¤¸ 160"
    
25. Rng(1).Replace ChrW(160), ""   '®ø°£¤£¥i¦r¤¸ 160
    
26. 
    
27. For Each E In Rng(1) '¨Ì§Ç³B¸Ì½Æ»s½d³òªºÀx¦s®æ
    
28. S = Split(E, Chr(10))
    
29. If UBound(S) > -1 Then '***¦³´«¦æ ªº¤å¦r
    
30. For Each xS In S
    
31. A.WriteLine (xS) 'Àx¦s®æ¼g¤J¤å¦rÀÉ
    
32. Next
    
33. Else
    
34. A.WriteLine (E.Text) 'Àx¦s®æ¼g¤J¤å¦rÀÉ
    
35. End If
    
36. Next
    
37. A.Close
    
38. 
    
39. Shell "Cmd /c start P:\TXT\" & Rng(2) & ".txt" '¦Û°Ê¥´¶}TXTÀÉ
    
40. Application.ScreenUpdating = True
    
41. 
    
42. End Sub

½Æ»s¥N½X

PJChen

¦^´_ 9# GBKEE

¤j¤j,

¤W¦¸µ{¦¡½T»{¥i°õ¦æ«á,§Ú±N¥¦À³¥Î¨ì¨ä¥L"TXT"¥¨¶°¤¤,¥X²{"°}¦C¯Á¤Þ¶W¥X½d³ò"ªº¹ï¸Ü®Ø,§Ú±N±N®×¤W¶Ç,¥i§_À°¦£¬Ý¬Ý?
¶Kñ¦W__«ü©wÀx¦s®æ¤ºªºÀɦW¬°¹ï¶H.zip (92.11 KB)

¤U¸ü¦¸¼Æ: 3

2012-11-8 18:07

c_c_lai

¦^´_  c_c_lai


Hi,

§ÚµLªk²z¸Ñ
        .Height = 150
        .Width = 150
        .Cut

...
PJChen µoªí©ó 2012-11-5 20:49

³]©w°ª«×¡B¼e«×¡B¨Ã±N¦¹·s¥[¤J¹Ï¤ù¤©¥H°Å¤U (Cut)  («ü 02.gif) ¡A
¨Ñ±µ¤U¨Ó¤§¤U¹F¶K¤W°Ê§@ (PasteSpecial)¡C

PJChen

¦^´_ 13# c_c_lai


Hi,

§ÚµLªk²z¸Ñ
        .Height = 150
        .Width = 150
        .Cut

ªº·N«ä,¥i¥HÀ°¦£¸ÑÄÀ¶Ü?

c_c_lai

¦^´_ 11# GBKEE
§Ú§âµ{¦¡½X¥[¤J´ú¸Õ OK ¤F¡A ÁÂÁ±zªº«ü¾É¡I

1. Sub copySigned2()    ' GBKEE
   
2.     Dim Rng(1 To 3) As Range, xi As Integer, Wb As Workbook
   
3.    
   
4.     Set Wb = Workbooks.Open(ThisWorkbook.Path & "\" & Workbooks("¶Kñ¦W.xlsm").Worksheets("EX").Range("D3").Value)
   
5.    
   
6.     '  Workbooks("¶Kñ¦W.xlsm").Sheets("Signed").Pictures("Picture 1").Copy
   
7.     With Workbooks("¶Kñ¦W.xlsm").Worksheets("EX").Pictures.Insert(ThisWorkbook.Path & "\" & "02.GIF")
   
8.         .Height = 150
   
9.         .Width = 150
   
10.         .Cut
    
11.     End With
    
12.     '  With Workbooks(Workbooks("¶Kñ¦W.xlsm").Worksheets("EX").Range("D3").Value)  '  §ï³o¸Ì...
    
13.     With Wb
    
14.         Set Rng(1) = .Sheets("PKG").[r:r].Find("B. C. MART COMPANY LTD.", LOOKAT:=xlPart).Offset(2, -2)
    
15.         Set Rng(2) = .Sheets("INV").[Q:Q].Find("B. C. MART COMPANY LTD.").Offset(2, -2)
    
16.         Set Rng(3) = .Sheets("SCD").[B:B].Find("Signature:").Offset(1, 1)
    
17.         For xi = 1 To 3
    
18.             Rng(xi).Parent.Activate
    
19.             Rng(xi).Activate
    
20.             ActiveSheet.PasteSpecial
    
21.         Next
    
22.     End With
    
23. End Sub

½Æ»s¥N½X

PJChen

¦^´_ 9# GBKEE
ÁÂÁ¤j¤j,

³o¼Ë°õ¦æOK.

GBKEE

¦^´_ 10# c_c_lai

1. Option Explicit
   
2. Sub Ex()
   
3.     With Sheet1.Pictures.Insert("D:\EX1.GIF")
   
4.         .Height = 150
   
5.         .Width = 150
   
6.         .Cut
   
7.     End With
   
8.     Sheet2.Range("B10").PasteSpecial
   
9.    ' Sheet3.Range("C10").PasteSpecial
   
10.    ' Sheet4.Range("D10").PasteSpecial
    
11. End Sub

½Æ»s¥N½X