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

[µo°Ý] EXCELE 2013 vba for windows 10 µLªk¥¿±`°õ¦æ

[µo°Ý] EXCELE 2013 vba for windows 10 µLªk¥¿±`°õ¦æ

½Ð°Ý¦U¦ì¸ê²`«e½ú
¤p§Ìªñ¤é¨Ï¥Îwindows 10Àô¹Ò¤U°õ¦æ¦¹½s½X
µo²{«ö¤F¤§«á¡A¼Æ¾Ú¥þ§ì¤£¨ì¡C
¦ý¦bwindows 7Àô¹Ò¤U½T¥i¥H¡C
¤£ª¾¹D¬Oºô¯¸¼gªk¦³§ï¤F¡AÁÙ¬O¥»¨­¨t²Î³y¦¨ªº
¦pªG¬O¨t²Î³y¦¨ªº¡A½Ð°Ý¦³¨S¦³§Oªº¤è¦¡¥i¥H¶i¦æ½Õ¾ã¡C¡C¡C
  1. Private Sub CommandButton4_Click()
  2.    Dim i As Integer, S As Integer, k As Integer, a As Object, ii, j, i1 As Integer, i2 As Integer, i3 As Integer
  3.   'DataObject ª«¥ó ¦b¶i¦æÂà´«°Ê§@®É¡A°µ¬°®æ¦¡¤Æ¤å¦r¸ê®Æªº¼È¦s°Ï°ì¡C¨ä¤]¥i¥H¼È¦s©MÀx¦s¦b DataObject ªº¤å¦r¤ù¬q¬ÛÃöªº®æ¦¡¡C
  4.     '«Å§i Dim D As New DataObject '¶·¦b¤u¨ã-> ³]©w¤Þ¥Î¶µ¥Ø¥[¤J ·s¼W¤Þ¥Î Microsoft Forms 2.0 Object Library ,©Î©ó±M®× ¥[¤J¤@ªí³æ§Y¥i

  5. 'ÃÒ¥æ©Ò«H¥Î¥æ©ö²Î­p------------------------------------
  6.   
  7.   'i3 = Mid(j, 1, 4)
  8.   'Sheets("¶q").Range("q1").Value = i3
  9.   'rul = "http://www.twse.com.tw/ch/trading/exchange/MI_MARGN/genpage/Report" & "/A112" & j & "MS.php?select2=MS&chk_date=" & 104 & "/" & 3 & "/" & 13
  10.   '"http://www.twse.com.tw/ch/trading/exchange/MI_MARGN/genpage/Report201503/A11220150313MS.php?select2=MS&chk_date=104/03/13"
  11.   'http://www.tpex.org.tw/web/stock/margin_trading/margin_balance/margin_bal_print.php?l=zh-tw&d=104/04/01&s=0,asc,1

  12.   'j = Sheets("¶q").Range("a1").Value
  13.   Application.DisplayStatusBar = True
  14.     Application.ScreenUpdating = False
  15.     Sheets("¶q").Range("Q1").Select
  16.     ActiveCell.FormulaR1C1 = "=TEXT(MID(RC[-16],1,LEN(RC[-16])),""YYY"")"
  17.    
  18.     Sheets("¶q").Range("R1").Select
  19.     ActiveCell.FormulaR1C1 = "=TEXT(MID(RC[-17],1,LEN(RC[-17])),""mm"")"
  20.    
  21.     Sheets("¶q").Range("S1").Select
  22.     ActiveCell.FormulaR1C1 = "=TEXT(MID(RC[-18],1,LEN(RC[-18])),""dd"")"
  23.    
  24.     Sheets("¶q").Range("T1").Select
  25.     ActiveCell.FormulaR1C1 = "=TEXT(MID(RC[-19],1,LEN(RC[-19])),""e"")"
  26.     j = Sheets("¶q").Range("q1").Value
  27.     j1 = Sheets("¶q").Range("r1").Value
  28.     j2 = Sheets("¶q").Range("s1").Value
  29.     j3 = Sheets("¶q").Range("t1").Value
  30.     'http://www.tpex.org.tw/web/stock/margin_trading/margin_balance/margin_bal_print.php?l=zh-tw&d=104/04/01&s=0,asc,1
  31.     'http://www.tpex.org.tw/web/stock/margin_trading/margin_balance/margin_bal_print.php?l=zh-tw&d=104/08/17&s=0,asc,1

  32.     ii = "http://www.tpex.org.tw/web/stock/margin_trading/margin_balance/margin_bal_print.php?l=zh-tw&d=" & j3 & "/" & j1 & "/" & j2 & "&s=0,asc,1"
  33.   Sheets("¶q").Range("o1").Value = i
  34.     With CreateObject("InternetExplorer.Application")
  35.         '.Visible = False
  36.         '.Navigate "http://www.twse.com.tw/ch/trading/exchange/MI_MARGN/genpage/Report201503/A11220150313MS.php?select2=MS&chk_date=104/03/13"
  37.         .Navigate ii
  38.         '¤W­±¬°ÃÒ¥æ©Ò¤T¤jªk¤H¶R½æª÷ÃB²Î­pªí³sµ²
  39.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
  40.         Application.Wait (Now + TimeValue("00:00:10"))
  41.         Ep15 .document.getElementsByTagName("table")(0).outerhtml
  42.         'Ãö³¬ºô­¶
  43.         .Quit
  44.         
  45.         
  46.     End With
  47.      Set a = CreateObject("InternetExplorer.Application")
  48.      Set a = Nothing
  49. Application.StatusBar = False

  50. End Sub
  51. Sub Ep15(S As String)
  52.      Dim d As New DataObject
  53.      Dim shape As Excel.shape
  54.     Application.DisplayStatusBar = True
  55.     Application.ScreenUpdating = False
  56.     'DataObject ª«¥ó ¦b¶i¦æÂà´«°Ê§@®É¡A°µ¬°®æ¦¡¤Æ¤å¦r¸ê®Æªº¼È¦s°Ï°ì¡C¨ä¤]¥i¥H¼È¦s©MÀx¦s¦b DataObject ªº¤å¦r¤ù¬q¬ÛÃöªº®æ¦¡¡C
  57.     '«Å§i Dim D As New DataObject '¶·¦b¤u¨ã-> ³]©w¤Þ¥Î¶µ¥Ø¥[¤J ·s¼W¤Þ¥Î Microsoft Forms 2.0 Object Library ,©Î©ó±M®× ¥[¤J¤@ªí³æ§Y¥i
  58.     With d
  59.         .SetText S
  60.         .PutInClipboard
  61.         With Sheets("¤W¥«Âd¤T¤jªk¤H").Select
  62.             Sheets("¤W¥«Âd¤T¤jªk¤H").Range("P10:AI12").Clear
  63.             Sheets("¤W¥«Âd¤T¤jªk¤H").Range("P10").Select
  64.             Sheets("¤W¥«Âd¤T¤jªk¤H").PasteSpecial Format:="Unicode ¤å¦r"
  65.             For Each shape In ActiveSheet.Shapes
  66.         shape.Delete
  67.         Next
  68.         xRow = Sheets("¤W¥«Âd¤T¤jªk¤H").Range("P65536").End(xlUp).Row
  69.              For i = 15 To xRow
  70.             If Sheets("¤W¥«Âd¤T¤jªk¤H").Range("P" & i).Value = "¦X­p(±i)" Then
  71.             
  72.              Sheets("¶q").Range("M5").Value = Sheets("¤W¥«Âd¤T¤jªk¤H").Range("V" & i).Value
  73.              Sheets("¶q").Range("N5").Value = Sheets("¤W¥«Âd¤T¤jªk¤H").Range("ad" & i).Value
  74.              End If
  75.            If Sheets("¤W¥«Âd¤T¤jªk¤H").Range("P" & i).Value = "¿Ä¸êª÷(¥a¤¸)" Then
  76.              Sheets("¶q").Range("O5").Value = Sheets("¤W¥«Âd¤T¤jªk¤H").Range("V" & i).Value
  77.              End If
  78.        Next
  79.         End With
  80.         
  81.     End With
  82.     Set d = Nothing
  83.          
  84.   

  85. Application.StatusBar = False
  86.     Sheets("¶q").Select
  87.     Sheets("¶q").Range("A1").Select
  88.     MsgBox "¤WÂd(«H)¤U¸ü§¹¦¨¡I"
  89. End Sub
½Æ»s¥N½X

测试¤@¤U¬O§_¬OCreateObject("InternetExplorer.Application")ªð¦^ªº­È为Nothing
¥@¬É¨º»ò¤j¡A¥i§Ú·Q¥h­þ¡H

TOP

¥»©«³Ì«á¥Ñ lp986749 ©ó 2015-9-14 11:49 ½s¿è

§Ú¤]µo¥Í¹L¥ÎEXCEL 2007 2003 ¼g¥¨¶°¦b WIN 7  WIN8 WIN8.1¤U¥Î OFFICE¢±¢¯¢°¢² ³£¥i°õ¦æ«Ü¶¶§Q ¦ý¦b WIN 10¡@¬Û¦P¢Ý¢Ô¢Ô¢×¢Ñ¢Ó2013 «o·í¾÷¤£¯à°õ¦æ¥¨¶° ¦Û¤v¤]«Ü§xÂZ  ¤£ª¾¬°¤°»ò­ì¦]?

TOP

¦^´_ 1# frantz

§A¦n:§Ú¹ïVBA¤£À´,¦ý§Ú¬Ý¨ì§AªºVBA¸Ì¦³«Ü¦h¦a¤è¨Ï¥Î¨ì¤¤µØ¥Á°ê¾ä,¤£ª¾§A¬O§_¦³µo²{¨ì¦bWIN10ªºÀô¹Ò¤U,EXCEL¤w¸gµLªk¨Ï¥Î"¤¤µØ¥Á°ê¾ä"¤F,§Ú·Q¬O§_¬O¦]¬°³o­Ó­ì¦]¡C¥H¤W¥u¬O§Ú­Ó¤Hªº²q·Q,¦p¦³¿ù»~,ÁٽЫü±Ð!!ÁÂÁÂ!!

TOP

½T¹êWIN10ªºÀô¹Ò¤U,EXCEL¤w¸gµLªk¨Ï¥Î"¤¤µØ¥Á°ê¾ä"¤F,EXCEL 2007 2003 ¼g¥¨¶°¦p
Format(Date - 30, "EMM") ¶·§ï¬° Format(Date - 30, "EEMM") ­ìÅã¥Ü 10408 «áªÌÅã¥Ü201508
¥¨¶°´N¥i°õ¦æ ¦]¬°WIN10 ¤é´Á®æ¦¡§ó§ï¦p2012/02/10

TOP

½T¹êWIN10ªºÀô¹Ò¤U,EXCEL¤w¸gµLªk¨Ï¥Î"¤¤µØ¥Á°ê¾ä"¤F,EXCEL 2007 2003 ¼g¥¨¶°¦p
Format(Date - 30, "EMM") ¶·§ï¬° Format(Date - 30, "EEMM") ­ìÅã¥Ü 10408 «áªÌÅã¥Ü201508
¥¨¶°´N¥i°õ¦æ ¦]¬°WIN10 ¤é´Á®æ¦¡§ó§ï¦p2012/02/10

TOP

        ÀR«ä¦Û¦b : §g¤l¦p¤ô¡AÀH¤è´N¶ê¡AµL³B¤£¦Û¦b¡C
ªð¦^¦Cªí ¤W¤@¥DÃD