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

§PÂ_¦¡

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-11-18 16:50 ½s¿è

¦^´_ 15# 518587
°Ñ¦Ò¤@¤U,¿ï¤â©m¦W,¦~ÄÖ,¨­°ª,Åé­«.¥i¦Û¤v¼g¼g¬Ý


  1. Option Explicit
  2. Sub Ex()
  3. Dim n As Integer, myrng As Range, mySh As Worksheet, myAr
  4. '±qºô­¶Â^¨ú¸ê®Æ
  5. For n = 1 To 2
  6. With Sheets("Â^¨ú¸ê®Æ")
  7.     If .QueryTables.Count >= 1 Then .QueryTables("Novak-Djokovic_1").Delete  '¤Ó¦hªºQueryTable ª«¥ó·|¦û¥Î¸ê·½
  8.     .UsedRange.ClearContents
  9.     Set myrng = Sheets("¿ï¤âºô§}").Cells(n + 1, 1)
  10.     Set mySh = Sheets("¸ê®Æ·J¾ã")
  11.     With .QueryTables.Add(Connection:="URL;" & myrng.Text, Destination:=.Range("$A$1"))
  12.         .Name = "Novak-Djokovic_1"
  13.         .FieldNames = True
  14.         .RowNumbers = False
  15.         .FillAdjacentFormulas = False
  16.         .PreserveFormatting = True
  17.         .RefreshOnFileOpen = False
  18.         .BackgroundQuery = True
  19.         .RefreshStyle = xlInsertDeleteCells
  20.         .SavePassword = False
  21.         .SaveData = True
  22.         .AdjustColumnWidth = True
  23.         .RefreshPeriod = 0
  24.         .WebSelectionType = xlEntirePage
  25.         .WebFormatting = xlWebFormattingNone
  26.         .WebPreFormattedTextToColumns = True
  27.         .WebConsecutiveDelimitersAsOne = True
  28.         .WebSingleBlockTextImport = False
  29.         .WebDisableDateRecognition = False
  30.         .WebDisableRedirections = False
  31.         .Refresh BackgroundQuery:=False
  32.     End With
  33.     Set myrng = .Cells.Find("W-L", Lookat:=xlWhole)  '´M§ä"W-L"
  34.     If Not myrng Is Nothing Then
  35.         With .Cells(myrng.Row, "A")
  36.             Select Case myrng.Column
  37.              Case 3  '"W-L" ¦bCÄæ
  38.                 myAr = Array("", "", "", .Range("B3"), .Range("D2"), .Range("B4"), .Range("F2"), "", "", .Range("B6"), .Range("D5"), .Range("B7"))
  39.                 '³æ¥´±Æ¦W(W-L):.Range("D2")  -> 2014/3/6 ³o"(¬O3³Ó6±Ñ)"¦b¦ó³B?
  40.             Case 4   '"W-L" ¦bDÄæ
  41.                 myAr = Array(.Range("B3"), .Range("D2"), .Range("F6"), .Range("B5"), .Range("D4"), .Range("B6"), .Range("F4"), .Range("B8"), .Range("F7"), .Range("B10"), .Range("D9"), .Range("B11"))
  42.                 'Âù¥´±Æ¦W(W-L): .Range("D9") ->2014/6/27 ³o"(¬O6³Ó27±Ñ)"¦b¦ó³B?
  43.             End Select
  44.         End With
  45.         With Sheets("¸ê®Æ·J¾ã").Cells(n + 1, "I").Resize(, UBound(myAr) + 1)
  46.             .Value = myAr
  47.             .Cells(IIf(myrng.Column = 4, 11, 5)).NumberFormatLocal = "yyyy/m/d"
  48.             
  49.         End With
  50.     End If
  51.     End With
  52.    Next n
  53.    ActiveWorkbook.Save
  54.    MsgBox "³B²z§¹²¦!"
  55. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 17# 518587



¦p¹Ï¥Ü ¬Û¹ïÀ³©ó"S D"ªºRange
  1.   Case 4   '"W-L" ¦bDÄæ
  2.                 myAr = Array(.Range("B3"), .Range("D2"), .Range("F6"), .Range("B5"), .Range("D4"), .Range("B6"), .Range("F4"), .Range("B8"), .Range("F7"), .Range("B10"), .Range("D9"), .Range("B11"))
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-11-20 12:57 ½s¿è

¦^´_ 19# 518587
©Ò§ì¨úRangeªº­È¬O§_¥¿½T?
  1.   With .Cells(myrng.Row, "A")'<-¥H³o¦ì¸m¬°°ò·ÇÂIªºRange,-> .Range("B3"), .Range("D2"), .Range("B4"),...  ½Ð¦b¬Ý¤@¦¸#18ªº¹Ï¤ù,³o¼Ë¤F¸Ñ½X?
  2.             Select Case myrng.Column
  3.              Case 3  '"W-L" ¦bCÄæ
  4.                 myAr = Array("", "", "", .Range("B3"), .Range("D2"), .Range("B4"), .Range("F2"), "", "", .Range("B6"), .Range("D5"), .Range("B7"))
  5.                 '³æ¥´±Æ¦W(W-L):.Range("D2")  -> 2014/3/6 ³o"(¬O3³Ó6±Ñ)"¦b¦ó³B?
  6.             Case 4   '"W-L" ¦bDÄæ
  7.                 myAr = Array(.Range("B3"), .Range("D2"), .Range("F6"), .Range("B5"), .Range("D4"), .Range("B6"), .Range("F4"), .Range("B8"), .Range("F7"), .Range("B10"), .Range("D9"), .Range("B11"))
  8.                 'Âù¥´±Æ¦W(W-L): .Range("D9") ->2014/6/27 ³o"(¬O6³Ó27±Ñ)"¦b¦ó³B?
  9.             End Select
  10.        End With
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : §Ú­Ì³Ì¤jªº¼Ä¤H¤£¬O§O¤H¡D¥i¯à¬O¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD