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

¥æ©ö©ú²Ó¤U¸ü

¦^´_  GBKEE

yap ´N¬O³o­Ó
¥t¥~´£¨Ñ¤@¤p¬qµ{¦¡½X
¥i¥H§K¥h¤â°Ê¿é¤J¤é´Á
¤Ï¥¿¥L¤]¥u¦³¤@¤Ñªº¸ê®Æ¥i¥H ...
lalalada µoªí©ó 2012-8-1 13:56



¶W¹L¤T¤ÀÄÁ¤£¯à½s¿è...
§Úµo²{¤é´ÁÀH«K¥´§ì¨ìªºªF¦è³£¤@¼Ë!
©Ò¥H¤]¤£»Ý­n¯S§O«ü©w¤F@@

TOP

¹ê»Ú¤W¤£»Ý­n¿é¤J¤é´Á¤Þ¼Æ´N¥i¥H¸ü¤Jºô­¶~

¥t¥~·Q½Ð±Ð
.ResultRange(1).End(xlDown).Offset(2).CurrentRegion.Cut .ResultRange(1).End(xlToRight).Offset(, 1)
³o¨â¥y¬O¤°»ò·N«ä?

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-8-1 14:46 ½s¿è

¦^´_ 11# lalalada
©Ò¥H¬O¥u¥i¥H¹î¬Ý«e¤@¤é ©Î ·í¤éªº¸ê®Æ
¦^´_ 12# lalalada

.ResultRange-> QueryTablet  ¬d¸ß¶Ç°e¨Óªº¸ê®Æ½d³ò  
.ResultRange(1).->.ResultRange.Cells(1)
vba ªº»¡©ú¤Î½d¨Ò
ResultRange ÄÝ©Ê ½Ð°Ñ¾\®M¥Î¦Ü½d¨Ò¯S©w¶Ç¦^ Range ª«¥ó¡A¸Óª«¥ó¥Nªí«ü©w¬d¸ßªí©ÒÂл\ªº¤u§@ªí°Ï°ì¡C°ßŪ¡C
½d¨Ò
¥»½d¨Ò¹ï²Ä¤@±i¬d¸ßªí¤¤²Ä¤@Ä檺¸ê®Æ¶i¦æ¥[Á`¡A¨Ã¦b¸ê®Æ½d³ò¤U¤èÅã¥Ü²Ä¤@Äæ¸ê®ÆªºÁ`©M¡C
Set c1 = Sheets("sheet1").QueryTables(1).ResultRange.Columns(1)
c1.Name = "Column1"
c1.End(xlDown).Offset(2, 0).Formula = "=sum(Column1)"

TOP

¦^´_ 10# lalalada

°Ñ¦Ò!°Ñ¦Ò!~~¦³»~½ÐªÈ¥¿§Ú!

§Úªº²z¸Ñ¬O
.RefreshStyle = xlOverwriteCells        '= xlOverwriteCells 'Âл\­ìÄæ¦ì   = xlInsertDeleteCells '´¡¤J·s¸ê®Æ,­ì¸ê®Æ¥k²¾


©Ò¥H§ì§¹¥þ­¶­±¤§«áªºÀ³¥Î
Option Explicit
Sub Ex()
    Dim QueryTable_Name As String
    With ActiveSheet
        With .QueryTables.Add(Connection:="URL;http://******", Destination:=Range("A1"))
            QueryTable_Name = .Name
            .RefreshStyle = xlOverwriteCells        '= xlOverwriteCells 'Âл\­ìÄæ¦ì   = xlInsertDeleteCells '´¡¤J·s¸ê®Æ,­ì¸ê®Æ¥k²¾
            .Refresh BackgroundQuery:=False
        End With
        .Names(QueryTable_Name).Delete   '§R°£:¬d¸ß¦b¤u§@ªí©Ò©w¸qªº½d³ò¦WºÙ
    End With

'³o¸Ì¥t¥~°Å¶K¥X¥h,ÅýÀɮצU¦Û¿W¥ß
Columns("¸ê®Æ½d³ò").Select
Selection.Cut      '°Å¤U¸ê®Æªº½d³ò
Workbooks.Add   '·s¼W¸ê®Æªí
Range("A1").Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs

End Sub

TOP

¦^´_ 10# lalalada
   
  .WebTables = "4,""table2"""

­×§ï9#¦p¤U
  1. Option Explicit
  2. Sub ­ÓªÑ¥æ©ö©ú²Ó¤U¸ü()
  3.     Dim ªÑ²¼¥N¸¹ As String, N As Name, i As Integer, T As Date, A
  4.     Do While ªÑ²¼¥N¸¹ = ""
  5.         ªÑ²¼¥N¸¹ = InputBox("ªÑ²¼¥N¸¹", "¿é¤J¬d¸ß¤§ªÑ²¼¥N¸¹", "1101")
  6.         If ªÑ²¼¥N¸¹ = "" Then End
  7.     Loop
  8.     T = Time
  9.     With ActiveSheet
  10.         .Cells.Clear
  11.         DoEvents
  12.         Application.ScreenUpdating = False
  13.         Application.StatusBar = False
  14.         With .QueryTables.Add(Connection:="URL;http://bsr.twse.com.tw/bshtm/bsContent.aspx?StartNumber=" & ªÑ²¼¥N¸¹ & "&FocusIndex=1", Destination:=Range("A1"))
  15.             .WebFormatting = xlWebFormattingNone
  16.             .WebTables = "4,""table2"""
  17.             .Refresh BackgroundQuery:=False
  18.             If Application.CountA(.ResultRange) = 0 Then
  19.                 MsgBox "ªÑ²¼¥N¸¹:" & ªÑ²¼¥N¸¹ & " ¿ù»~ !!!"
  20.                 [a1].Select
  21.                 End
  22.             End If
  23.             ActiveSheet.Names(.Name).Delete
  24.         End With
  25.         i = 2
  26.         Do
  27.             .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Select
  28.              With .QueryTables.Add(Connection:="URL;http://bsr.twse.com.tw/bshtm/bsContent.aspx?StartNumber=" & ªÑ²¼¥N¸¹ & "&FocusIndex=" & i, Destination:=Selection)
  29.                 .WebFormatting = xlWebFormattingNone
  30.                 .WebTables = "table2"
  31.                 On Error Resume Next
  32.                 Do
  33.                      Err.Clear
  34.                     .Refresh BackgroundQuery:=False
  35.                 Loop Until Err.Number = 0
  36.                 On Error GoTo 0
  37.                 If Application.CountA(.ResultRange) = 0 Then GoTo OUT
  38.                 .ResultRange(1).EntireRow.Delete
  39.                 ActiveSheet.Names(.Name).Delete
  40.                 i = i + 1
  41.             End With
  42.         Loop
  43. OUT:
  44.         .[a1].Select
  45.         Application.ScreenUpdating = True
  46.         .Columns.AutoFit
  47.         A = CreateObject("WScript.Shell").popup("¦@¤U¸ü " & i & " ­¶¶O®É  " & Format(Time - T, "hh:mm¤ÀSS¬í"), 5, "_" & ªÑ²¼¥N¸¹, 48 + 0)
  48.         Application.StatusBar = "ªÑ²¼¥N¸¹ [" & ªÑ²¼¥N¸¹ & "] ¦@¤U¸ü " & i & "­¶ ¶O®É " & Format(Time - T, "HH:MM:SS")
  49.         For Each N In .Names
  50.             N.Delete
  51.         Next
  52.      End With
  53. End Sub
½Æ»s¥N½X

TOP

¦^´_ 13# GBKEE
¦^´_ 14# HSIEN6001

     ·PÁ¨â¦ìªº»¡©ú!!

TOP

½Ð°Ý¦U¦ì¤j¤j¡G
¬°¤°»ò§Ú¶K¤W¥H«á¡A¥¦¤@ª½»¡§ä¤£¨ì¥¨¶°¡H
¦³¨S¦³ÀÉ®×¥i¥HÅý§Ú¬Ý¤@¤U...
·PÁÂ !
shall

TOP

¥»©«³Ì«á¥Ñ sd-jason ©ó 2012-8-1 22:56 ½s¿è

¦U¦ì¤j¤j¡G
§Ú­è­è¸Õ§¹¥i¥HÅo¡I
¥u¬O³o­Ó¦n¹³¨S¦³¿ìªk¹³¤W¦¸¤À¨Éªº¡A
¦Û°Ê§â©Ò¦³ªºªÑ²¼§ì¤U¨Ó¡A
¦Ó»Ý­n¤@­Ó¤@­Ó¥h§ì¡A
½Ð°Ý³o­n­×§ï­þ¸Ì....
{:3_57:}
shall

TOP

¦^´_ 18# sd-jason


¼g­Ó°j°é´N¥i¥HÅo..
°ò¥»¤W´N¬O
for x = 1 to 10
code=cells(x,1) 'cell¬O¦s©ñªÑ²¼¥N½XªºÀx¦s®æ ¤@¶}©l¥ý¥´¦n
'§âcode¥N¤J­ìµ{¦¡ªº"ªÑ²¼¥N½X"
next

TOP

¦^´_ 10# lalalada


    ½Ð°Ý­n¥[¨ì­þ¸Ì¡H³o¬qµ{¦¡....·|¤ñ¸û§Ö¶Ü¡H

TOP

        ÀR«ä¦Û¦b : ¤@¥y·Å·xªº¸Ü¡A´N¹³©¹§O¤H¨­¤WÅx­»¤ô¡A¦Û¤v·|ªg¨ì¨â¤Tºw¡C
ªð¦^¦Cªí ¤W¤@¥DÃD