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

½Ð°Ý¦p¦ó±N¦sÀɦpªí®æ¤@¼Ë

½Ð°Ý¦p¦ó±N¦sÀɦpªí®æ¤@¼Ë

½Ð°Ý¦p¦ó±N¦sÀɦpªí®æ¤@¼Ë
  1. Sub Trans()
  2.     Dim myDir As String, myRng As Range
  3.     Dim i As Integer
  4.     myDir = ThisWorkbook.Path '«ü©w¸ô®|¬°¤u§@ªí©Ò¦b¥Ø¿ý
  5.     ChDrive myDir
  6.     ChDir myDir
  7.     Set myRng = Range("A1").CurrentRegion '©w¸q­n§ì¨úªº½d³ò
  8.    
  9.     Dim MyTxt As String

  10.     Dim filename As String
  11.     '©w¸qÀɦW¬°®É¶¡
  12.     filename = Format(Now(), "yyyymmddhhmmss") & ".txt"
  13.     '¶}±ÒÀÉ®×¼g¤J¡A­Y¸ô®|¤¤¥¼¦³¸ÓÀÉ®×·|create¤@­Ó
  14.     Open filename For Output As #1
  15.     '²Ä¤@¦C¤£§ì¶i¨Ó¡A¤@ª½¨ú¨ì³Ì«á¤@¦C
  16.     For i = 1 To myRng.Rows.Count
  17.         MyTxt = myRng.Cells(i, 1).Text + myRng.Cells(i, 2).Text + myRng.Cells(i, 3).Text + myRng.Cells(i, 4).Text + myRng.Cells(i, 5).Text
  18.         Print #1, MyTxt
  19.         
  20.     Next
  21.     Close #1
  22.     MsgBox ("¦sÀɦ¨¥\! ")
  23. End Sub
½Æ»s¥N½X
¦p¤U¹Ï¤@¼Ë¡AÁÂÁÂ

2014-09-19_203402.jpg (37.84 KB)

2014-09-19_203402.jpg

testtxt.rar (14.34 KB)

½Ð°Ý¦p¦ó±N¦sÀɦpªí®æ¤@¼Ë¦p¤U¹Ï¤@¼Ë¡AÁÂÁÂ
wufonna µoªí©ó 2014-9-19 20:40
  1. Sub Trans()
  2.     Dim myRng As Range
  3.     Dim i%, j%
  4.     Dim MyTxt$, filename$
  5.     Dim aLen()
  6.    
  7.     aLen = Array(8, 16, 16, 16, 16, 16, 16)
  8.     Set myRng = Range("A1").CurrentRegion '©w¸q­n§ì¨úªº½d³ò
  9.    
  10.     '©w¸qÀɦW¬°®É¶¡
  11.     filename = ThisWorkbook.Path & "\" & Format(Now(), "yyyymmddhhmmss") & ".txt"
  12.     '¶}±ÒÀÉ®×¼g¤J¡A­Y¸ô®|¤¤¥¼¦³¸ÓÀÉ®×·|create¤@­Ó
  13.     Open filename For Output As #1
  14.     '²Ä¤@¦C¤£§ì¶i¨Ó¡A¤@ª½¨ú¨ì³Ì«á¤@¦C
  15.     With myRng
  16.     For i = 1 To .Rows.Count
  17.       MyTxt = ""
  18.       For j = 1 To 7
  19.         MyTxt = MyTxt & Left(.Cells(i, j) & Space(16), aLen(j - 1) - Len(.Cells(i, j)) * -(i = 1))
  20.       Next
  21.         Print #1, MyTxt
  22.     Next
  23.     End With
  24.     Close #1
  25.     MsgBox ("¦sÀɦ¨¥\! ")
  26. End Sub
½Æ»s¥N½X
testtxt-a.zip (14.25 KB)

TOP

¦^´_ 2# luhpro


    ÁÂÁ luhpro ¤j¤j ¡Aµ{¦¡¥i°õ¦æ ^_^
  ½Ñ°Ý ¤j¤j          MyTxt = MyTxt & Left(.Cells(i, j) & Space(16), aLen(j - 1) - Len(.Cells(i, j)) * -(i = 1))
¤¤ªº  *-(i = 1) ªº§@¥Î
ÁÂÁÂ

TOP

¥»©«³Ì«á¥Ñ bobomi ©ó 2014-9-20 19:10 ½s¿è

a =  -(i = 1)  
·í i = 1 ®É,         a = 1
·í i = ¨ä¥L­È ®É, a = 0

b = Len(.Cells(i, j)) * a  ->  ·í i = 1 ®É,  b = Len(.Cells(i, j)) * 1 = Len(.Cells(i, j))
b = Len(.Cells(i, j)) * a  ->  ·í i = ¨ä¥L­È ®É,  b =  Len(.Cells(i, j)) * 0 = 0

·N§Y¥Î¼Æ¾Ç¦¡¨ú¥N IF or IIF

IF i = 1 then
   b = Len(.Cells(i, j))
else
  b = 0
end if

TOP

¥»©«³Ì«á¥Ñ wufonna ©ó 2014-9-20 22:27 ½s¿è

¦^´_ 4# bobomi


    ½Ð°Ý¤j¤j
a =  -(i = 1)  
·í i = 1 ®É,         a = 1
·í i = ¨ä¥L­È ®É, a = 0

·í=1
debug
i=1 ¬O True
i ¤£¬O 1
i = 1 ¬O flase

¦Ó
i=1  -(i = 1)  ¬O 1
i ¤£¬O 1  -(i = 1) ¬O 0
ÁÂÁÂ

·N§Y¥Î¼Æ¾Ç¦¡¨ú¥N IF or IIF

IF i = 1 then
   b = Len(.Cells(i, j))
else
  b = 0
end if

­ì¨Ó¬O³o¼Ë
¨S¬Ý²M·¡
ÁÂÁÂ ¤j¤j

TOP

¥»©«³Ì«á¥Ñ bobomi ©ó 2014-9-20 22:55 ½s¿è

vba ªº True  ¨ä¹ê´N¬O = -1
vba ªº False  ¨ä¹ê´N¬O = 0

-(i = 1)  = - True = - (-1) = 1

TOP

¦^´_  luhpro
    ÁÂÁ luhpro ¤j¤j ¡Aµ{¦¡¥i°õ¦æ ^_^
  ½Ñ°Ý ¤j¤j          MyTxt = MyTxt & Left( ...  , aLen(j - 1) - Len(.Cells(i, j)) * -(i = 1))
wufonna µoªí©ó 2014-9-20 19:00

¤§©Ò¥H»Ý­n¦h³o¤@¬q¬O¦]¬°¦b Excel VBA ¤¤, ¤¤¤å(Âù¦ì¤¸¦r²Õ - ¥þ§Î¦r) ¦b¬Y¨Ç¨ç¼Æªº­pºâ¤W¥u¦û¤@­Ó¦rªº¼e«×
Len("¦~«×")=2
¦ý¦b¤å¦rÀɤ¤»P¨ä¥e¾Ú¬Û¦PªÅ¶¡ªº­^¼Æ¦r(³æ¦ì¤¸¦r²Õ - ¥b§Î¦r) :
Len("2014") «o¬O =4
©Ò¥H·íÅã¥Ü¤¤¤å¦r®É,
¬°¤F­n¨Ï¨ä»P­^¤å¦rªº¦ì¸m¯à°÷¹ï»ô,
©¹©¹»Ý­n¦A´î±¼»P¤¤¤å¦r¼Æ¬Û¦Pªº¥b§Î¦rªÅ®æ.

¦Ó³o¤]©¹©¹·|¨Ï±o¨Ï¥Î¥þ§Î¦rªº¤å¥ó¦b°µ©w¦ì³B²z®É«Ü¤£¤è«K,
¨Ò¦p¤@­Ó¤å¥ó :
¤ýOO  ¨k  ¤uµ{®v ¥x¥_¥«OO°ÏOOOO¸ô¤G¬q000«Ñ00¸¹2¼Ó¤§¤@ (02)1234-5678
ĬOO  ¤k               ·s¦Ë¥«OO°ÏOO¸ô00¸¹                                         (05)000-0000
¦b¦a§}ªº¤¤¤å¦r¼Æ¤£©w,
­Y¤S¦³¨ÇÄæ¦ì¥i¯à¬O§¹¥þªÅ¥Õªº±¡§Î¤U, (³o´N¤£¾A¦X¥HªÅ¥Õ¨Ó°µÄæ¦ì°Ï¹j)
¦p¤W¤è¨Ò¤l­Y·Q­n©w¦ì«á­±ªº¹q¸Ü´N­n¸g¹L¸û½ÆÂøªº­pºâ³B²z¤F.

¦b Mid »P Left µ¥¨ç¼Æ¤]¬O¦³³o¼Ëªº±¡§Î,
¤Ý¸Þªº¬O "Àx¦s®æ¨ç¼Æ" ¤¤¦³¸s LenB, LeftB »P MIDB µ¥¥i¥Hª½±µ³B²zÂù¦ì¤¸¦r²Õªº¦r¼Æ©w¦ì³B²z, ( LenB("¦~«×")=4 )
Excel VBA ³oºØ§ó»Ý­n³oÃþ³B²zªº»y¨¥(Ū¨ú¤å¦rÀɤ§Ãþªº...),
§Ú§ä¤F«Ü¤[³£§ä¤£¨ìÃþ¦üªº¨ç¼Æ¥i¥Î,
¤]¤£ª¾¹D¨ä¥L¤H³£¬O«ç¼Ë³B²zÃþ¦ü°ÝÃDªº.

TOP

¦^´_ 7# luhpro
¦^´_ 6# bobomi

ÁÂÁÂ bobomi  ¤j luhpro ¤j
°Ñ¦Ò¯¸¤Wªº¸ê®Æ http://forum.twbts.com/thread-31-1-1.html  ¥Î StrConv ¬O§_¨º­n§ï¶iªº ÁÂÁÂ
  1. Sub Test()
  2. GetDividend ("2002")
  3. '¨ú±oºô­¶ªí®æ®æ¦¡¤Æ¬°¤å¦rªí®æ
  4. End Sub
  5. Private Sub GetDividend(ByVal ss As String) '¨úªÑ§Qºô­¶
  6. Set ie = CreateObject("internetexplorer.application") '¨Ï¥Î¦¹¤è¦¡¥i¥H§K°£ "³]©w¤Þ¥Î¶µ¥Ø"

  7. Dim hh As String, T As Date, i, ii, S As Object
  8. Dim filename As String, varVar As String

  9. T = Time
  10. hh = "http://dj.mybank.com.tw/z/zc/zcc/zcc_" & ss & ".asp.htm"
  11. With ie
  12. .Navigate hh
  13. Do While .readyState <> 4 'µ¥«Ýºô­¶¤U¸ü§¹²¦
  14. DoEvents
  15. If Time >= T + #12:00:03 AM# Then 'µ¥«Ý8¬í 3¬í¤Ó¤Ö·|»~¿ù§ï8§®
  16. DoEvents
  17. Application.SendKeys "~" 'ªÑ²¼¥N¸¹¿ù»~,ºô­¶·|¦³°T®§,¶·«ö½T©w,¤~¥iÄ~Äò¤U­±ªÑ²¼¥N¸¹

  18. Exit Do
  19. End If
  20. Loop
  21. ''***¤£¬Oµ¥«Ý8¬í 3¬í¤Ó¤Ö·|»~¿ù§ï8§® ***
  22. Do
  23. Set S = .Document.getElementsByTagName("table")(3) '
  24. Loop Until Not S Is Nothing
  25. '*** ³Ó¤@ ¨S®»¨ì ????
  26. '*** ¦]µ{¦¡¹B¦æ¤Ó§Ö , S Is Nothing, S ©|¥¼«ü©w¬°.Document.getElementsByTagName("table")(4)
  27. '*** µ{¦¡¤w¸g°õ¦æ¤U¤@¦æ, With ¤u§@ªí2 ªºµ{¦¡½X
  28. filename = ss & "_" & Format(Now(), "yyyymmddhhmmss") & ".txt"
  29. Open filename For Output As #1
  30. For i = 0 To S.Rows.Length - 1 '¼g¤J¸ê®Æ
  31. varVar = ""
  32. For ii = 0 To S.Rows(i).Cells.Length - 1 ' S.Rows(i).Cells.Length - 1 ¤~¬O¥¿½T
  33. varVar = varVar & Left(S.Rows(i).Cells(ii).innerText & Space(16), 12 - (LenB(StrConv(S.Rows(i).Cells(ii).innerText, vbFromUnicode)) - Len(S.Rows(i).Cells(ii).innerText)))
  34. 'http://forum.twbts.com/thread-31-1-1.html Len / LenB - ¦r¦êªø«×§PÂ_¤è¦¡ªº±´°Q
  35. DoEvents
  36. Next
  37. Print #1, varVar

  38. Next
  39. Close #1

  40. ' MsgBox ("¦sÀɦ¨¥\! ")
  41. End With
  42. End Sub
½Æ»s¥N½X

TOP

½Ð°Ý ¤j¤j
³oºô­¶¦³­Ó´«¦æ
§Ú¦h¥[¤F
varVar = Replace(varVar, Chr(10), " ") '¨ú¥N´«¦æ¬°ªÅ®æ
¤å¦rÀɤºÁÙ¦³1 ­Ó¶Â®æ
½Ð°Ý¤j¤j¬O¨º¸Ì¦³¿ù
ÁÂÁÂ
  1. Sub Test()
  2. GetDividend ("2002")
  3. '¨ú±oºô­¶ªí®æ®æ¦¡¤Æ¬°¤å¦rªí®æ
  4. End Sub
  5. Private Sub GetDividend(ByVal ss As String) '¨úªÑ§Qºô­¶
  6. Set ie = CreateObject("internetexplorer.application") '¨Ï¥Î¦¹¤è¦¡¥i¥H§K°£ "³]©w¤Þ¥Î¶µ¥Ø"

  7. Dim hh As String, T As Date, i, ii, S As Object
  8. Dim filename As String, varVar As String

  9. T = Time
  10. ' hh = "http://dj.mybank.com.tw/z/zc/zcc/zcc_" & ss & ".asp.htm"
  11. hh = "http://yamstock.megatime.com.tw/asp/stockinfo/GetReport.asp?select_table=html\Finain_Full\&stockid=" & ss & "&name1=D5&index1=5"

  12. With ie
  13. .Navigate hh
  14. Do While .readyState <> 4 'µ¥«Ýºô­¶¤U¸ü§¹²¦
  15. DoEvents
  16. If Time >= T + #12:00:03 AM# Then 'µ¥«Ý8¬í 3¬í¤Ó¤Ö·|»~¿ù§ï8§®
  17. DoEvents
  18. Application.SendKeys "~" 'ªÑ²¼¥N¸¹¿ù»~,ºô­¶·|¦³°T®§,¶·«ö½T©w,¤~¥iÄ~Äò¤U­±ªÑ²¼¥N¸¹


  19. Exit Do
  20. End If
  21. Loop
  22. ''***¤£¬Oµ¥«Ý8¬í 3¬í¤Ó¤Ö·|»~¿ù§ï8§® ***
  23. Do
  24. Set S = .Document.getElementsByTagName("table")(4) '
  25. Loop Until Not S Is Nothing
  26. '*** ³Ó¤@ ¨S®»¨ì ????
  27. '*** ¦]µ{¦¡¹B¦æ¤Ó§Ö , S Is Nothing, S ©|¥¼«ü©w¬°.Document.getElementsByTagName("table")(4)
  28. '*** µ{¦¡¤w¸g°õ¦æ¤U¤@¦æ, With ¤u§@ªí2 ªºµ{¦¡½X
  29. filename = ss & "_" & Format(Now(), "yyyymmddhhmmss") & ".txt"
  30. Open filename For Output As #1
  31. For i = 0 To S.Rows.Length - 1 '¼g¤J¸ê®Æ
  32. varVar = ""
  33. For ii = 0 To S.Rows(i).Cells.Length - 1 ' S.Rows(i).Cells.Length - 1 ¤~¬O¥¿½T
  34. varVar = varVar & Left(S.Rows(i).Cells(ii).innerText & Space(14), 14 - (LenB(StrConv(S.Rows(i).Cells(ii).innerText, vbFromUnicode)) - Len(S.Rows(i).Cells(ii).innerText)))
  35. 'http://forum.twbts.com/thread-31-1-1.html Len / LenB - ¦r¦êªø«×§PÂ_¤è¦¡ªº±´°Q
  36. varVar = Replace(varVar, Chr(10), " ")
  37. DoEvents
  38. Next
  39. Print #1, varVar

  40. Next
  41. Close #1

  42. ' MsgBox ("¦sÀɦ¨¥\! ")
  43. End With
  44. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2014-9-23 00:00 ½s¿è
½Ð°Ý ¤j¤j
³oºô­¶¦³­Ó´«¦æ
§Ú¦h¥[¤F
varVar = Replace(varVar, Chr(10), " ") '¨ú¥N´«¦æ¬°ªÅ®æ
¤å¦rÀɤºÁÙ¦³1 ­Ó¶Â®æ...
wufonna µoªí©ó 2014-9-22 18:34

§A¬O«ü¼ÐÃD¦C¤¤ ¦~«×»P©u§O¶¡¦³¨â­ÓªÅ®æ¶Ü?
¨ä¹ê³o­Ó§A¥i¥H¥Î¤â°Ê°Å¶K¤@¤U´N¥i¥Hµo²{:
1.ºô­¶¤¤½Æ»s¸Óªí®æ¤º®e
2.¶K¨ì Excel
3. ¦A½Æ»sExcel¤Wªºªí®æ
4. ¶K¨ì°O¨Æ¥»
¥Î¤è¦VÁä²¾°Ê´å¼Ð,
§A·|µo²{¦~«×¨º¦C¤¤¦U¦~«×¶¡´å¼Ð¦³®É·|¤@¦¸¸õ«Ü¦h®æ,
§Ú§PÂ_¨º¬O HT (Tab«öÁä) Chr(9) ¦r¤¸,
§A¸Õ¸Õ¬Ý¥ÎªÅ¦r¤¸¨ú¥NÀ³¸Ó´N¤£·|µo¥Í¤F.

TOP

        ÀR«ä¦Û¦b : ÁÀ¨¥¹³¤@¦·²±¶}ªºÂAªá¡A¥~ªí¬üÄR¡A¥Í©Rµu¼È¡C
ªð¦^¦Cªí ¤W¤@¥DÃD