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

[µo°Ý] ¨Ì¦¸°O¿ý¦UªÑ´Þ§Q²v

[µo°Ý] ¨Ì¦¸°O¿ý¦UªÑ´Þ§Q²v

½Ð°Ýª©¤W°ª¤â¡G¦b¤p§ÌªºªþÀɤ¤¡A¥Ñ¡¨±`¥Î¥N½X¡¨ªº¤u§@ªíÂùÀ»¥N½X§Y¥i©ó¡¨¦ô»ù¡¨ªº¤u§@ªíC5¨ú±o¸ÓªÑ´Þ§Q²v¡A­Y¤p§Ì·Q¥Ñ¡¨±`¥Î¥N½X¡¨ªº¤u§@ªíÂùÀ»A2¥N½X©ó¡¨¦ô»ù¡¨ªº¤u§@ªí¨ú±oA2ªº´Þ§Q²v¬ö¿ý©ó¡¨±`¥Î¥N½X¡¨¤u§@ªí¤¤ªºC2¡A±µµÛÂùÀ»A3¥N½X©ó¡¨¦ô»ù¡¨ªº¤u§@ªí¨ú±oA3ªº´Þ§Q²v¬ö¿ý©ó¡¨±`¥Î¥N½X¡¨¤u§@ªí¤¤ªºC3¡A¨Ì¦¸ª½¨ìA27¡A½Ð°Ý³o¥¨¶°©ÎVBA¸Ó¦p¦ó¼g?ÁÂÁ¡C

´Þ§Q²v.rar (11.13 KB)

¦~ÄÖ¤£¤p,¦ý§Ú«Ü·Q¾Ç

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-8-9 14:10 ½s¿è

¦^´_ 1# bhsm
¸Õ¸Õ¬Ý

[¦ô»ù]¤u§@ªí¼Ò²Õ
  1. Private Sub Worksheet_Calculate()
  2.     Sheet3.Rng = [c5].Text 'SheetS("±`¥Î¥N½X")
  3. End Sub
½Æ»s¥N½X
[±`¥Î¥N½X]¤u§@ªí¼Ò²Õ
  1. Public Rng As Range
  2. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  3.     If Target.Column = 1 And Target.Row >= 2 And Target <> "" Then
  4.         Cancel = True
  5.         Sheets("¦ô»ù").[c1].Value = Target.Value
  6.         Set Rng = Target.Cells(1, "C")
  7.     End If
  8. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 2# GBKEE

·PÁÂGBKEE¤j«ü¾É.¨Ì±zªº¤è¦¡¤w¥iÂùÀ»"±`¥Î¥N½X"ªºA2~A27,¨Ã©óC2~C27¨ú±o¸ê®Æ.·Q¶i¤@¨B½Ð±Ð:¦pªG­nÅý"±`¥Î¥N½X"ªºA2~A27¦Û°Ê°õ¦æ,½Ð°Ý¥¨¶°­n¦p¦ó¼g?¤p§Ì¹Á¸Õ¥Î¿ý»s¥¨¶°ªº¤è¦¡,¥ÑÂùÀ»A2¶}©l.¨ú±oC2¸ê®Æ.¦AÂùÀ»A3.¨ú±oC3¸ê®Æ----ª½¨ìÂùÀ»A27.¨ú±oC27¸ê®Æ«á°±¤î¿ý»s¥¨¶°.¦ý°õ¦æ¥¨¶°®É.´å¼Ð«oª½±µ¶]¨ìA27.½Ð°Ý³o¥¨¶°¸Ó¦p¦ó¼g?ÁÂÁÂ

´Þ§Q²v1.rar (11.06 KB)

¦~ÄÖ¤£¤p,¦ý§Ú«Ü·Q¾Ç

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-8-10 05:55 ½s¿è

¦^´_ 3# bhsm


   
¥u¯d£¸±i[±`¥Î¥N½X] ¤u§@ªí´N¥i¥H
  1. Dim Sh As Worksheet
  2. Sub ¥Dµ{¦¡()
  3.     Dim Rng As Range, AR(1 To 2) As String, Web_Table(1 To 2), i As Integer, R As Range
  4.     AR(1) = "URL;https://tw.stock.yahoo.com/q/q?s=xxxx"
  5.     AR(2) = "URL;https://tw.stock.yahoo.com/d/s/company_xxxx.html"
  6.     Web_Table(1) = "7"
  7.     Web_Table(2) = "8"
  8.     Set Sh = Sheets("±`¥Î¥N½X")
  9.     Set Rng = Sh.[a2:a27]
  10.     Rng.Interior.ColorIndex = xlNone
  11.     Rng.Offset(, 1).Resize(, 4) = ""
  12.    
  13.     Web¬d¸ß§R°£
  14.     Web¬d¸ß»s©w
  15.     On Error GoTo L2
  16.    
  17.     For Each R In Rng
  18.         For i = 1 To 2
  19.             With Sh.QueryTables("_" & i)
  20.                 .Connection = Replace(AR(i), "xxxx", Trim(R))
  21.                 .WebSelectionType = xlSpecifiedTables
  22.                 .WebFormatting = xlWebFormattingNone
  23.                 .WebTables = Web_Table(i)
  24.                 .WebPreFormattedTextToColumns = True
  25.                 .WebConsecutiveDelimitersAsOne = True
  26.                 .WebSingleBlockTextImport = False
  27.                 .WebDisableDateRecognition = True
  28.                 .WebDisableRedirections = False
  29.                 .Refresh BackgroundQuery:=False 'ªÑ¸¹¿ù»~®É·|¦³¿ù»~
  30.                If i = 1 Then
  31.                     R.Cells(1, 2) = Mid(.ResultRange.Cells(3, 1), Len(Trim(R)) + 1)
  32.                     R.Cells(1, 3) = .ResultRange.Cells(3, 3)
  33.                     R.Cells(1, 3).NumberFormatLocal = "#0.00"
  34.                Else
  35.                     R.Cells(1, 4) = .ResultRange(3, 4)
  36.                End If
  37.             End With
  38.         Next
  39.             R.Cells(1, 5) = Val(R.Cells(1, 4)) / R.Cells(1, 3)
  40.             R.Cells(1, 5).NumberFormatLocal = "0.00%"
  41. L1:
  42.     Next
  43.    
  44.     Web¬d¸ß§R°£
  45.     Exit Sub
  46. L2:
  47.     Err.Clear
  48.     R.Interior.Color = vbYellow
  49.     GoTo L1
  50. End Sub
  51. Private Sub Web¬d¸ß»s©w()
  52.     Dim i As Integer
  53.     For i = 1 To 2
  54.         With Sh.Range("AA" & IIf(i = 1, 1, 50))
  55.                 With Sh.QueryTables.Add("URL;about:Tabs", .Cells) 'ªÅªººô§}
  56.                     .Name = "_" & i
  57.                     .Refresh BackgroundQuery:=False
  58.                 End With
  59.             End With
  60.         Next
  61. End Sub
  62. Private Sub Web¬d¸ß§R°£()
  63.     Dim Q As QueryTable
  64.     For Each Q In Sh.QueryTables
  65.         Q.ResultRange.Clear
  66.         Q.Delete
  67.     Next
  68. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 4# GBKEE

·PÁÂGBKEE¤j¡A¤p§Ì¦w¸Ë¤W¥X²{¤@¨Ç°ÝÃD·Q¦A½Ð±Ð±z¡A¸Ô±¡½Ð¬ÝTEST.rar¤ºªº»¡©ú¡AÁÂÁ¡C

TEST.rar (710.13 KB)

¦~ÄÖ¤£¤p,¦ý§Ú«Ü·Q¾Ç

TOP

¦^´_ 5# bhsm
1¡B±ÒÀÉ®×(¥X²{¿ù»~°T®§¡A­ì¦]¦p¦ó¡A¯uªº«Ü·Qª¾¹D

¦]¬°·í±`¥Î¥N½X¤u§@ªí ©|¥¼¦³ÂùÀ»A2ªº°Ê§@, ±`¥Î¥N½X¼Ò²Õªº Public Rng As Range ªº RngÅܼÆÁÙ¥¼«ü©wRange
©Ò¥H°õ¦æ¦ô»ù¼Ò²ÕªºWorksheet_Calculateµ{§Ç®É¦³¿ù»~, ¨S¦³³]©wª«¥óÅܼƩΠWith °Ï¶ôÅÜ¼Æ (¿ù»~ 91)
¥i­×§ï¦p¤U
  1. Private Sub Worksheet_Calculate()
  2.     Stop    'Worksheet_Calculateµ{§Ç: ³o¤u§@ªí¤º¤½¦¡ªº­È¦³§ïÅÜ·|°õ¦æ¤@¦¸
  3.     If Not Sheet3.Rng Is Nothing Then
  4.         Sheet3.Rng = [c5].Text 'SheetS("±`¥Î¥N½X")
  5.     End If
  6. End Sub
½Æ»s¥N½X
´Þ§Q²v1-2.xls 2003Àô¹Ò°õ¦æ«Ü¥¿±`°Ú
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 6# GBKEE

«D±`·PÁÂGBKEE¤jªº¦³°Ý¥²µª¡A¥Ø«e´Þ§Q²v1-1.xls¤w¥i¥¿±`¹B§@¡A¦ý´Þ§Q²v1-2.xls¤´µM¥X²{ªþÀɪº°T®§
¦~ÄÖ¤£¤p,¦ý§Ú«Ü·Q¾Ç

TOP

¦^´_ 7# bhsm
³o¬O§A©Ò»¡ªº¿ù»~¤º®e
  1. ªí³æ¤v¸gÅã¥Ü¡F¤£¯à¦A¥H±j¨î¦^À³ªº¤è¦¡Åã¥Ü¦¹ªí³æ (¿ù»~ 400)
  2. ±z¤£¯à¥Î Show ¤èªk¡A¥H±j¨î¦^À³ªº¤è¦¡Åã¥Ü¤@­Ó¥i¨£ªºªí³æ¡C³o¶µ¿ù»~ªº­ì¦]¤Î¸Ñ¨M¤èªk¦p¤U¡G
  3. ±z¸Õ¹Ï¦b¥i¨£ªºªí³æ¤W¡A¨Ï¥Î±N style ¤Þ¼Æ³]©w¦¨ 1 - vbModal ªº Show¡C
  4. ½Ð¦b¥H±j¨î¦^À³ªº¤è¦¡Åã¥Üªí³æ¤§«e¡A¨Ï¥Î Unload ³¯­z¦¡©Î Hide ¤èªk¡C
½Æ»s¥N½X
½Ð°Ý§A°õ¦æSheet3.¥Dµ{¦¡®ÉÁÙ°õ¦æ¦³¨ä¥Lªºµ{¦¡¶Ü?
³o¬O§Aªº´Þ§Q²v1-2.xls,¨S¦³¿ù»~ 400ªºµo¥Í

Ex.rar (11.54 KB)
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 8# GBKEE
·PÁÂGBKEE¤jªº¦^µª.§Ú­«·s¶}¾÷¤£¶}¥ô¦óÀÉ®×.¤]¤£°õ¦æ¥ô¦óµ{¦¡.ª½±µ°õ¦æ±z©Òªþªº´Þ§Q²v1-2.xls.¨ÌÂÂ¥X²{400ªº¿ù»~°T®§.³oÀ³¸Ó¤£¬O±zªºµ{¦¡°ÝÃD.§Ú·QÀ³¸Ó¬O§Úªº¹q¸£¦³°ÝÃD.©¯¦n¦³²Ä¤@­Ó¤è¦¡¥i¸Ñ¨M.«D±`ÁÂÁ±z
¦~ÄÖ¤£¤p,¦ý§Ú«Ü·Q¾Ç

TOP

¦^´_ 9# bhsm
·|¬O§AIEªºÃö«Y¶Ü?(¸Õ¸Õ IE­«¦w¸Ë)
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ¡i®É¶¡¦pÆp¥Û¡j®É¶¡¹ï¤@­Ó¦³´¼¼zªº¤H¦Ó¨¥¡A´N¦pÆp¥Û¯ë¬Ã¶Q¡F¦ý¹ï·M¤H¨Ó»¡¡A«o¹³¬O¤@§âªd¤g¡A¤@ÂI»ù­È¤]¨S¦³¡C
ªð¦^¦Cªí ¤W¤@¥DÃD