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

[µo°Ý] ¥Îexcel¥´¶}µ§°O¥»

¦^´_ 29# GBKEE
G¤j,
µ{¦¡°õ¦æOK,¤£¹L·Q½Ð°Ý,µ{¦¡¤¤³£¨S¦³¬Ý¨ì[A2]ªºÀx¦s®æ,¬°¤°»òÀx¦s®É·|¦Û°Ê¥[¤W[A2]ªºÀx¦s®æ¦WºÙ?

TOP

¦^´_ 31# PJChen
Rng(2).Offset(1)  ´N¬O[A2]

Offset ÄÝ©Ê
expression.Offset(RowOffset, ColumnOffset)
expression     ¥²¿ï¡C¸Ó¹Bºâ¦¡¶Ç¦^ Range ª«¥ó¡C
RowOffset      ¿ï¾Ü©Êªº Variant¡C¥Î¦C¼Æªí¥Üªº°Ï°ì¦ì²¾ (¥¿­È¡B­t¼Æ©Î¹s (0))¡C¥¿­Èªí¥Ü¦V¤U¦ì²¾¡A­t­Èªí¥Ü¦V¤W¦ì²¾¡C¹w³]­È¬° 0¡C
ColumnOffset      ¿ï¾Ü©Êªº Variant¡C¥ÎÄæ¼Æªí¥Üªº°Ï°ì¦ì²¾ (¾ã¼Æ¡B­t¼Æ©Î 0 (¹s))¡C¥¿­Èªí¥Ü¥k¦ì²¾¡A­t­Èªí¥Ü¥ª¡C¹w³]­È¬° 0¡C

TOP

¦^´_ 32# GBKEE

·PÁÂG¤j»¡©ú.

TOP

¦^´_ 32# GBKEE
¦A½Ð±ÐG¤j,
¬°¤°»òEXCELªº¥¨¶°¤¤,¦³®É¥i¥Hª½±µ¨Ï¥ÎÀx¦s®æ¦ì¸m·í¦¨ÀɮצWºÙ,¦p: Set Rng(2) = .Sheets("booking").[A1]
¦³®É­Ô¤S¥²¶·¨Ï¥ÎOffset,¦p:   Rng(2) = Rng(2) & "_" & Rng(2).Offset(1)
­n¦p¦óª¾¹D¦ó®É¸Ó¥Î¤°»ò?

TOP

¦^´_ 32# GBKEE
G¤j,

¤§«eªºµ{¦¡°õ¦æ宊µM¥X²{¤@­Ó¹ï¸Ü®Ø,¨Ã¥B¥¨¶°ªº³o¤@¦æ³Q¼Ðµù¶À¦â©³:    A.WriteLine (E.Text)         

       
   VBA TxT.zip (25.68 KB)

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-5-22 15:15 ½s¿è

¦^´_ 34# PJChen
¦p A=A1      A.Cells(1,1)  =>A1     ,A.Offset(0,0)=>A1  ¥iÀH­Ó¤H³ß¦n
¦^´_ 35# PJChen
¦³¿ù»~¬O¦]  B27:  RAttn:? Mr. Peter Holfelder ¤¤ ? ¬° ¤£¥i¨£¦r¤¸ 160
µ{¦¡¤w­×¥¿ ²Å¦X ¦L¦C­¶ªºªº®æ¦¡¶×¤J¤å¦rÀɤ¤
  1. Sub try()
  2. Windows("Shipping for Holfelder.xlsx").Activate
  3. 'Creat a TXT
  4. Sheets("Booking").Select
  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. With Workbooks("Shipping for Holfelder.xlsx") '½Ð§ï¦¨­n½Æ»s¬¡­¶Ã¯ªº¦WºÙ(¤w¸g¥´¶})
  13. 'With Workbooks.Open("P:\Shipping for Holfelder.xlsx") '½Ð§ï¦¨­n½Æ»s¬¡­¶Ã¯ªº¦WºÙ(©|¥¼¥´¶})
  14. Set Rng(1) = .Sheets("Booking").[B1:B40] 'Rng(1) ¤u§@ªí­n½Æ»sªº½d³ò
  15. Set Rng(2) = .Sheets("Booking").[A1] 'Rng(2) ¦sÀɦWºÙªºÀx¦s®æ
  16. Rng(2) = Rng(2) & "_" & Rng(2).Offset(1) 'Rng(2) ¦sÀÉÀɦW¶·¦h¥[[A2]ªºÀx¦s®æ¦WºÙ
  17. 'RowOffset ¿ï¾Ü©Êªº Variant¡C¥Î¦C¼Æªí¥Üªº°Ï°ì¦ì²¾ (¥¿­È¡B­t¼Æ©Î¹s (0))¡C¥¿­Èªí¥Ü¦V¤U¦ì²¾¡A­t­Èªí¥Ü¦V¤W¦ì²¾¡C¹w³]­È¬° 0¡C
  18. 'ColumnOffset ¿ï¾Ü©Êªº Variant¡C¥ÎÄæ¼Æªí¥Üªº°Ï¹Bºâ¦¡°ì¦ì²¾ (¾ã¼Æ¡B­t¼Æ©Î 0 (¹s))¡C¥¿­Èªí¥Ü¥k¦ì²¾¡A­t­Èªí¥Ü¥ª¡C¹w³]­È¬° 0¡C
  19. End With
  20. Set Fs = CreateObject("Scripting.FileSystemObject") 'FileSystemObject ª«¥ó ´£¨Ñ¹ï¹q¸£Àɮרt²Îªº¦s¨ú¡C
  21. Set A = Fs.CreateTextFile("P:\TXT\" & Rng(2) & ".txt", True) '«Ø¥ß¤å¦rÀÉ®×
  22. 'CreateTextFile ¤èªk «Ø¥ß¤@­Ó«ü©wªºÀɦW¨Ã¥B¶Ç¦^¤@­Ó¥Î©ó¸ÓÀÉ®×Ū¼gªº TextStream ª«¥ó¡C
  23. '¦pªG¥i³QÂл\¨ä­È¬° True¡A¨ä¬° False ®ÉµLªkÂл\
  24. Debug.Print Rng(1).Cells(27)
  25. '*** Debug.Print :§Y®É¹Bºâµøµ¡ ¥i¨£¨ì RAttn:? Mr. Peter Holfelder ¤¤ ? ¬° ¤£¥i¨£¦r¤¸ 160" ******
  26. Rng(1).Replace ChrW(160), ""   '**** ®ø°£¤£¥i¦r¤¸ 160

  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. 'Workbooks.Open ("P:\TXT\" & Rng(2) & ".txt") '³o·|¥HEXCEL¦Û°Ê¥´¶}TXTÃþ«¬¤å¥ó
  39. Shell "Cmd /c start P:\TXT\" & Rng(2) & ".txt" '¦Û°Ê¥´¶}TXTÀÉ
  40. Application.ScreenUpdating = True
  41. End Sub
½Æ»s¥N½X

TOP

¦^´_ 36# GBKEE
G¤j,

­ì¨ÓÁÙ·|¦³³oºØ°ÝÃD,·PÁ§AªºÀ°¦£,°õ¦æ¨S°ÝÃD¤F.

TOP

        ÀR«ä¦Û¦b : ¹D¼w¬O´£ª@¦Û§Úªº©ú¿O¡A¤£¸Ó¬O¨þ¥¸§O¤HªºÃ@¤l¡C
ªð¦^¦Cªí ¤W¤@¥DÃD