GBKEE

¦^´_ 2# white5168
­n¤À¨É°O±o±M®×½Ð¤£­n¤£¤WÂê
SHEET1ªºµ{¦¡½X

1. Private Sub ¤j½L¦¨¥æ¸ê°T_Click()
   
2.     Dim xlTheYear As String, xlTheMonth As String, xlTheFile As String
   
3.     xlTheYear = Format(Range("C1"), "0000")  '­×§ï¦r¦ê®æ¦¡
   
4.     xlTheMonth = Format(Range("C2"), "00")     '­×§ï¦r¦ê®æ¦¡
   
5.     UsedRange.Offset(4).Clear
   
6.     xlTheFile = "http://www.twse.com.tw/ch/trading/exchange/FMTQIK/FMTQIK2.php?STK_NO=&myear=" & xlTheYear & "&mmon=" & xlTheMonth & "&type=csv"
   
7.     With Workbooks.Open(xlTheFile)
   
8.         .Sheets(1).UsedRange.Offset(2).Copy [a5]
   
9.         .Close 0
   
10.     End With
    
11. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 4# white5168
¸Õ¸Õ¬Ý

    ¤j½L¨C¤ë¨C¤Ñ¾ú¥v..rar (7.64 KB)

¤U¸ü¦¸¼Æ: 122

2012-6-24 08:10

GBKEE

¦^´_ 7# usana642
1.½Æ»sµ{¦¡½X¨ì ¤@¯ë¼ÒèÊ,©Î ThisWorkbook¼ÒèÊ    2.¦b¤u§@ªí¤W ´¡¤J§Ö¨ú¹Ï®×,   3.±N¹Ï®×ªº¥¨¶°«ü©w¦¹µ{§Ç
©ó¤u§@ªí ªº C1 :  ¿é¤J¦è¤¸¦~¥÷   C2 :  ¿é¤J¤ë¥÷    «ö¤U §Ö¨ú¹Ï®× ´N¥i¥H

1. Sub ¤j½L¦¨¥æ¸ê°T()
   
2.     Dim xlTheYear As String, xlTheMonth As String, xlTheFile As String
   
3.     xlTheYear = Format(Range("C1"), "0000")  '­×§ï¦r¦ê®æ¦¡
   
4.     xlTheMonth = Format(Range("C2"), "00")     '­×§ï¦r¦ê®æ¦¡
   
5.     UsedRange.Offset(4).Clear
   
6.     xlTheFile = "http://www.twse.com.tw/ch/trading/exchange/FMTQIK/FMTQIK2.php?STK_NO=&myear=" & xlTheYear & "&mmon=" & xlTheMonth & "&type=csv"
   
7.     With Workbooks.Open(xlTheFile)
   
8.         .Sheets(1).UsedRange.Offset(2).Copy [a5]
   
9.         .Close 0
   
10.     End With
    
11. End Sub

½Æ»s¥N½X

¤U¸ü (33.48 KB)

2012-6-25 20:32

GBKEE

¦^´_ 9# turbine

1. Option Explicit
   
2. Private Sub ¤j½L¦¨¥æ¸ê°T()
   
3.     Dim xlTheYear As String, xlTheMonth As String, xlTheFile As String
   
4.     Dim Sh As Worksheet
   
5.     xlTheYear = Format(Range("C1"), "0000")  '­×§ï¦r¦ê®æ¦¡
   
6.     xlTheMonth = Format(Range("C2"), "00")   '­×§ï¦r¦ê®æ¦¡
   
7.     Set Sh = ThisWorkbook.Sheets.Add         '·s¼W¤u§@ªí
   
8.     Sh.Name = xlTheYear & "_" & xlTheMonth   '·s¼W¤u§@ªí©R¦W
   
9.     xlTheFile = "http://www.twse.com.tw/ch/trading/exchange/FMTQIK/FMTQIK2.php?STK_NO=&myear=" & xlTheYear & "&mmon=" & xlTheMonth & "&type=csv"
   
10.     With Workbooks.Open(xlTheFile)
    
11.         .Sheets(1).UsedRange.Copy Sh.[a1]
    
12.         .Close 0
    
13.     End With
    
14.     Sh.Cells.EntireColumn.AutoFit            '½Õ¾ãÄæ¼e
    
15.     Sh.Columns("A:A").ColumnWidth = 28.56
    
16. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 12# usana642
§@¥Î¤¤ªº¤u§@ªí  C1 ¦³¿é¤J ¦~«×¶Ü? C2 ¦³¿é¤J ¤ë¥÷¶Ü?
ªþ¤W§Aªº Àɮ׬ݬÝ

GBKEE

¦^´_ 14# usana642
¥¿±`°Ú¨S§A»¡ªº°ÝÃD,·|¬O§AIE¦³°ÝÃD¶Ü?
¦­¤W§ÚªºIE ¤]¦³°ÝÃD,¬Ý³oºô­¶­×§ï¤F.
http://tw.knowledge.yahoo.com/question/question?qid=1509082302506

GBKEE

¦^´_ 16# usana642
§Úªº¹q¸£¥i¥H°õ¦æµ{¦¡,¤U¸ü¸ê®Æ³£OK,§Úªº°ÝÃD¬O·í¤Ñªº¼Æ­È³£©ñ¦bAÄæ¦ì

³o¤£¹ïªº §Ú¤U¸ü§¹¦¨ªº¬O§A©Ò­nªº¦³¤ÀÄ檺
100¦~08¤ë¥«³õ¦¨¥æ¸ê°T(¤¸,ªÑ)                                       
¤é´Á        ¦¨¥æªÑ¼Æ        ¦¨¥æª÷ÃB        ¦¨¥æµ§¼Æ        µo¦æ¶q¥[ÅvªÑ»ù«ü¼Æ        º¦¶^ÂI¼Æ
100/08/01        4,802,873,133        134,792,140,188        1,045,023        8,701.38        57.2
100/08/02        4,460,257,070        128,697,790,897        983,452        8,584.72        -116.66
100/08/03        5,242,190,864        147,151,600,336        1,158,419        8,456.86        -127.86
100/08/04        5,107,533,580        141,508,869,343        1,125,390        8,317.27        -139.59
100/08/05        5,994,797,600        162,619,243,835        1,209,399        7,853.13        -464.14
100/08/08        6,146,181,315        168,324,584,613        1,289,476        7,552.80        -300.33
100/08/09        7,587,824,872        202,710,492,435        1,544,718        7,493.12        -59.68
100/08/10        6,265,027,298        179,518,452,401        1,423,133        7,736.32        243.2
100/08/11        5,424,476,208        153,879,609,456        1,275,107        7,719.09        -17.23

GBKEE

¦^´_ 20# usana642
¨º¬O¶×¤J¥~³¡¸ê®Æ, ³o¬O¶}±ÒExcel  csv   ¤å¦rÀÉ,¦³¨Ç¤£¦Pªº
§A¸ÕµÛ¶}±Ò¤@ Excel  csv ¤å¦rÀÉ,¦p¦³§A»¡¸ê®Æ¬Ò¦bAÄæ,¨º´N¥i¯à¬OEXCELªº°ÝÃD.

GBKEE

¦^´_ 23# usana642

1. Option Explicit
   
2. ¥Î 2000ª© ¥H¤W¸Õ¸Õ¬Ý
   
3. Sub Ex()
   
4.      Columns(8).Replace "-", ""
   
5.      '©Î¬O
   
6.      Range("H:H").Replace "-", "0"
   
7. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 26# usana642
¸Õ¸Õ¬Ý

1. Option Explicit
   
2. Sub Ex()
   
3.     With ActiveSheet
   
4.         .Cells.Clear
   
5.         With .QueryTables.Add("URL;http://www.taifex.com.tw/chinese/3/3_2_2.asp", ActiveSheet.[A1])
   
6.             .WebFormatting = xlWebFormattingNone
   
7.             .Refresh BackgroundQuery:=False
   
8.             ActiveSheet.Names(.Name).Delete
   
9.         End With
   
10.         .Range("E:G,I:L,N:Q").Delete                                    '§R°£¦h¾lªºÄæ
    
11.         .Range("1:6,8:8").Delete                                        '§R°£¦h¾lªº¦C
    
12.         .Range("B1").End(xlDown).Offset(1).Resize(2).EntireRow.Delete   '§R°£¦h¾lªº¦C
    
13.         .Range("A:A").Insert                                            '´¡¤J¤@Äæ
    
14.         .[B1].Resize(, 12) = Array("«´¬ù", "¤ë¥÷", "¼i¬ù»ù", "¶R½æÅv", "¦¨¥æ»ù", "¥¼¥­­Ü¶q", "CALL", "=C2", "call-oi", "put-oi", "call-oi$", "put-oi$")
    
15.         '**  "=C2" ¥i­×§ï¬° ¥¿½Tªº°Ñ·Ó  ***
    
16.         With .Range("b2", .[b2].End(xlDown))
    
17.             .Offset(, -1) = "=rc4 +rc8 + rc9"
    
18.             .Columns(5).Replace "-", ""
    
19.             .Columns(7) = "=IF(rc[-3]=""Call"",1,0)"    'R1C1ªí¥Üªk : ¤u§@ªí¤Wß}¤J¤½¦¡
    
20.             .Columns(8) = "=IF(rc[-6]=r1c9,1,8)"
    
21.             .Columns(9) = "=IF(rc[-2]=1,rc[-3],0)"
    
22.             .Columns(10) = "=IF(rc[-3]=0,rc[-4],0)"
    
23.             .Columns(11) = "=if(rc[-1]=0,rc[-6]*rc[-5],"""")"
    
24.             .Columns(12) = "=if(rc[-2]<>0,rc[-7]*rc[-6],"""")"
    
25.         End With
    
26.         .UsedRange.Value = .UsedRange.Value             '®ø°£¤½¦¡
    
27.         .Columns.AutoFit
    
28.     End With
    
29. End Sub

½Æ»s¥N½X