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

¥Î°j°é§ì¸ê®Æ¶V¶]¶VºC¡A¸Ó¦p¦óÄÀ©ñ°O¾ÐÅé?

¥Î°j°é§ì¸ê®Æ¶V¶]¶VºC¡A¸Ó¦p¦óÄÀ©ñ°O¾ÐÅé?

¦U¦ì«e½ú¤j®a¦n,

§Ú¦³­Óµ{¦¡¨Ï¥Î°j°é¤Îquerytable.addªº¤è¦¡§ì¨ú¥~³¡¸ê®Æ(§ì§¹·|°õ¦æquerytable.delete)
¨Ã¦b¶×¤J¸ê®Æ«á¥Îworksheets.copyªº¤è¦¡¥t¦s¡A
¦¹®Éµ{¦¡·|¦Û°Ê·s¼W¤@­Ó¬¡­¶Ã¯¡A«Ý¦sÀÉ«á¦A±N·s¼Wªº¬¡­¶Ã¯Ãö³¬

¦ý§Úµo²{¦p¦¹¤@¨Ó¡A¦]¬°¨C¦¸copy¤u§@ªí®É³£·|·s¼W¤@­Ó¬¡­¶Ã¯
¦pªG§Úªº°j°é¤@¦@­n§ì¦n´X¦Ê¦¸ªº¸ê®Æ
¨º´N·|¼W¥[¦n´X¦Ê­Ó¬¡­¶Ã¯
ÁöµM¤§«á·|Ãö³¬¡A¦ý¤£¾å±o°O¾ÐÅ馳¨S¦³ÄÀ©ñ±¼¡A¦]¬°¬¡­¶Ã¯ªº½s¸¹·|¤@ª½¼W¥[¤W¥h
¦Óµ{¦¡¶]ªº³t«×¤]¶V¨Ó¶VºC....@@"

¥i¥H½Ð¦U¦ì«e½úÀ°¦£¸Ñµª¤@¤U¶Ü? ÁÂÁÂ

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-6-21 05:18 ½s¿è

¦^´_ 1# sasho
  1. Option Explicit
  2. Sub Ex()
  3.     Dim i As Integer, rng As Range
  4.     With ActiveSheet
  5.         If .QueryTables.Count = 0 Then .QueryTables.Add "URL;", .[a1]
  6.         For i = 3 To 17 Step 2
  7.             '°j°é¤¤£¸ª½ªº querytable.add ÀÉ®×·|­D°_¨Ó,¾É­Pµ{¦¡ªº³t«×¶V¨Ó¶VºC
  8.             With .QueryTables(1)
  9.                 .Connection = "URL;http://forum.twbts.com/thread-635-1-1.html"
  10.                 .WebSelectionType = xlSpecifiedTables
  11.                 .WebFormatting = xlWebFormattingNone
  12.                 .WebTables = i & ""
  13.                 .WebPreFormattedTextToColumns = True
  14.                 .WebConsecutiveDelimitersAsOne = True
  15.                 .WebSingleBlockTextImport = False
  16.                 .WebDisableDateRecognition = False
  17.                 .WebDisableRedirections = False
  18.                 .Refresh BackgroundQuery:=False
  19.                 Wb_Save .ResultRange, i
  20.             End With
  21.         Next
  22.    End With
  23. End Sub
  24. Private Sub Wb_Save(Rng As Range, i As Integer)  '°Æµ{¦¡:·s¼W¬¡­¶Ã¯,¦sÀÉ
  25.     With Workbooks.Add(1)
  26.         Rng.Copy .Sheets(1).[a1]
  27.         .Close True, "d:\test_" & i & ".xls"
  28.         'Ãö³¬¦sÀÉ
  29.     End With
  30. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2014-6-7 07:06 ½s¿è
¦U¦ì«e½ú¤j®a¦n,

§Ú¦³­Óµ{¦¡¨Ï¥Î°j°é¤Îquerytable.addªº¤è¦¡§ì¨ú¥~³¡¸ê®Æ(§ì§¹·|°õ¦æquerytable.delete) ...
sasho µoªí©ó 2014-6-7 01:53

¨Ì§A´y­z§@·~¹Lµ{¡A¶Õ¥²·|·U¨Ó·UºC¡AEXCEL ¥»¨­¦b°õ¦æ®É·|¥t¥~²£¥Í¤@¤u§@¯¸¼È¦sÀÉ¡A
¥¦·|ÀH®É°ÊºA¦a±N§A©Ò¦³²§°Êª¬ºA¬ö¿ý©ó¦¹Àɮפº¡A¥¦©Ó¾á¤F©Ò¦³Åç¹ï¡BÅÜ°Ê¡B§R°£¡B
·s¼W¥H¤Î°O¾ÐÅ骺¯Ó¥Î¡A©óÀɮפ£Â_¼W¤j®É¡A¨ä­t²ü¥ç¸òÀH¤§¼W¤j¡C
¸Ñ¨M¤èªk¥i±N¶·¤£Â_¼W¥[ªºÀɮצs©ó¥t¤@¤u§@ÀÉ¡A¤£¥Î®É«hÀH®É±N¥¦Ãö³¬¡A¦p¦¹§@·~
¦A¨Óµû¦ô¦¨®Ä¡C´N¦p¤W GBKEE ª©¤j©Ò®i¥Üªºµ{¦¡½X¤@¼Ë¡A³B²z§¹²¦§YÀH¤â±N¥¦Ãö³¬¡C

TOP

¥»©«³Ì«á¥Ñ sasho ©ó 2014-6-16 18:16 ½s¿è

¦^´_ 2# GBKEE

·PÁÂG¤jªº«üÂI¡A¦ý¥i¯à§Úªí¹Fªº¤£¬O«Ü²M·¡¡A©Ò¥HÅý¤j®a»~·|§Úªº°ÝÃD¤F
¸òG¤j©Ò¥Ü½dªº¤@¼Ë¡Aµ{¦¡¦A¶×¤Jºô­¶¸ê®Æ«á³£·|¥t¦s¦¨¤@­ÓCSVÀɮרÃÃö³¬¡A
¦P®É¡A±N©Ò·s¼Wªºquerytable delete±¼¡Aµ{¦¡½X¦p¤U¡G
  1. Sub ¨ú±o¸ê®Æ(strURL As String, Table As String)

  2. Dim xlSheet As Excel.Worksheet
  3. Set xlSheet = Sheets("Temp")

  4. Do
  5.     Application.DisplayAlerts = False
  6.      With xlSheet.QueryTables.Add("URL;" & strURL, xlSheet.Cells(1, 1))
  7.          .WebFormatting = xlWebFormattingNone
  8.          .WebSelectionType = xlSpecifiedTables
  9.          .WebTables = Table
  10.          .BackgroundQuery = False

  11.          On Error Resume Next
  12.          Do
  13.              Err.Clear
  14.              .Refresh 0
  15.             
  16.              If Err.Number Then
  17.                  Application.Wait Now + TimeValue("00:00:01")
  18.              End If

  19.          Loop Until Err.Number = 0
  20.          
  21.          .Delete
  22.          'If Err.Number <> 0 Then Err.Clear: MsgBox Err.Number    '³Q§K¸ê®Æ§ì¨ú¤£¦¨¥\,¦ÓÅã¥Ü°T®§
  23.          
  24.          On Error GoTo 0
  25.      End With

  26.      If Err.Number = 0 Then
  27.          Application.DisplayAlerts = True
  28.          Exit Sub
  29.      End If
  30. Loop

  31. Àx¦sCSV DownloadDate,id
  32. End Sub

  33. Sub Àx¦sCSV(SaveDate As String, CSVname As String)
  34.    
  35. Dim TestObj As Object
  36. Dim CSVfile As String, CSVfolder As String
  37. Dim TestFolder As Boolean
  38.    
  39.     FilePath = "D:\TSE\"
  40.     CSVfolder = FilePath & SaveDate & "\"
  41.     CSVfile = CSVfolder & CSVname & "_" & SaveDate & ".csv"
  42.     'Debug.Print CSVfile
  43.    
  44.     Set TestObj = CreateObject("Scripting.FileSystemObject")
  45.     TestFolder = TestObj.FolderExists(CSVfolder)
  46.     If TestFolder = False Then TestObj.CreateFolder (CSVfolder)

  47.     On Error Resume Next
  48.     Kill CSVfile
  49.     On Error GoTo 0

  50.     Worksheets(Tempname).Copy
  51.     Application.DisplayAlerts = False
  52.     With ActiveWorkbook
  53.         .SaveAs FileName:=CSVfile, FileFormat:=xlCSV
  54.         .Close 0
  55.     End With
  56.     Application.DisplayAlerts = True
  57.    
  58. End Sub
½Æ»s¥N½X
§Úªº°ÝÃD¬O¦b©ó¡A¥Ñ©ó§Ú¥Î°j°é¤@¦@­n§ì´X¦Êµ§ªº¸ê®Æ
§Ú±qÀx¦s¤U¨ÓªºÀɮ׳̫áÅܰʮɶ¡Æ[¹î¡Aµo²{¤@¶}©l¥i¯à¤@¬íÄÁ¥i¥H§ì¤­¨ì¤»­ÓÀÉ®×
¦ý¨ì«á­±¡A¥i¯à¤@¬íÄÁ´N¥u¯à§ì¨â­ÓÀɮסAÀɮפj¤p³£®t¤£¦h
¤@ª½¤£ª¾¹D¬°¤°»ò³t«×·|¶V¨Ó¶VºC¡A¨C§ì§¹¤@µ§ÀÉ®×´N°õ¦æ¤@¦¸ doevents
©Ò¥HÀ³¸Ó¤£¦Ü©ó³y¦¨CPU¸ê·½³Q¦Y¥ú
°ß¤@¯à·Q¨ìªº´N¬O¡A¨C¦¸¥t¦s¤@­ÓÀɮסA´N·|·s¶}¤@­Ó¬¡­¶Ã¯
¤ñ¤è»¡¡A¬¡­¶Ã¯1¡B¬¡­¶Ã¯2¡B¬¡­¶Ã¯3....¨ì³Ì«áªº¬¡­¶Ã¯999
ÁöµM§â¸ê®Æ½Æ»s¹L¥h«á¡B¥t¦s·sÀÉ«á¸Ó¬¡­¶Ã¯´N·|Ãö³¬¡A¦ýEXCEL¦n¹³ÁÙ¬O·|¥e¥Î¨ì°O¾ÐÅé¸ê·½   
¬O¤£¬O¦]¬°³o¼Ë¤~¾É­P³t«×¶V¨Ó¶VºC©O?

ÁÙ¬O»¡¡A¬O¦]¬°§Ú½Æ»sªº¤è¦¡¤£¥¿½T¤~·|¾É­P°O¾ÐÅé¼W¥[ªº©O?
G¤j¸ò§Úªº®t§O¦b©ó¡A¶×¤J¸ê®Æ«áG¤j¬Oworkbook.addµM«á½Æ»s¶×¤Jªºrange
¦Ó§Ú«h¬Oª½±µ±N¾ã­Óworksheet copy
¤£¾å±o³o¨âªÌªº¤è¦¡¦³®t¶Ü

ÁÂÁ¦U¦ì«e½ú«üÂI m(_ _)m

TOP

¦^´_ 4# sasho
§Ú·|¥ý§â©I¥s
  1. Àx¦sCSV DownloadDate,id
½Æ»s¥N½X
§ï¬°
  1. t = timer
  2. Àx¦sCSV DownloadDate,id
  3. debug.print timer-t
½Æ»s¥N½X
¥ýÆ[¹î½T»{ ®É¶¡Åܪøªº¦a¤è ¬O¦b ¦sÀɳo³¡¤À¡C

TOP

¦^´_ 5# stillfish00

®É¶¡Åܪøªº³¡¤À¡AÀ³¸Ó¬O¦b¶×¤Jºô­¶¸ê®Æ©Òªáªº®É¶¡
¤@¶}©l¶×¤J«Ü¶¶¡A¦ý¶V¨ì«á­±´N¶V¥d¡A¤£ª¾¹D¬O¤°»ò­ì¦]

¦P®É§Ú¦³Æ[¹îEXCEL¦b¤u§@ºÞ²z­û¤¤¥e¥Îªº°O¾ÐÅé®e¶q
±qµ{¦¡°õ¦æ«e¨ìµ{¦¡°õ¦æ«á¡A¾ã­Ó°O¾ÐÅé¼W¥[¤F¤£¤Ö
¤@¼Ë¤]¬O¤£ª¾¹D­ì¦]

Àµ½Ð¦U¦ì¤j¤j¸Ñ±Ï¤@¤U

TOP

¦^´_ 7# GBKEE
Hi ª©¤j¡A
§Ú¤£¾å±o¬O¤£¬OEXCELª©¥»®t²§¡A
§Ú¦bExcel2010¤¤¡A§R°£QueryTableª«¥ó´N·|ª½±µ§R°£Names¤¤ªº¶µ¥Ø¤F¡C

TOP

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2014-6-17 14:09 ½s¿è

¦^´_ 9# GBKEE
QueryTables.Delete ¥u·|§R°£³s½u©M©w¸qªº¦WºÙ¡A¤£·|§R°£¨ú±oªº¸ê®Æ

¦Ó¼Ó¥D¥Î°j°é°õ¦æ¦hµ§®É¸ê®Æ·|¤@ª½©¹¥k²Ö¿n¼W¥[
³o¤]³\¥i¥H§ïRefreshStyle¬°xlOverwriteCells¨Ó¨¾¤î(¹w³]¬OxlInsertDeleteCells)
¹³³o¼Ë
  1. Sub Ex()
  2.     Dim i As Integer, Rng As Range
  3.     With ActiveSheet
  4.         For i = 3 To 7 Step 2
  5.             With .QueryTables.Add("URL;http://forum.twbts.com/thread-635-1-1.html", .Cells(1, 1))
  6.                 .WebSelectionType = xlSpecifiedTables
  7.                 .WebFormatting = xlWebFormattingNone
  8.                 .WebTables = i & ""
  9.                 .WebPreFormattedTextToColumns = True
  10.                 .WebConsecutiveDelimitersAsOne = True
  11.                 .WebSingleBlockTextImport = False
  12.                 .WebDisableDateRecognition = False
  13.                 .WebDisableRedirections = False
  14.                
  15.                 .RefreshStyle = xlOverwriteCells
  16.                 .Refresh BackgroundQuery:=False
  17.                 Wb_Save .ResultRange, i
  18.                
  19.                 .Delete
  20.             End With
  21.         Next
  22.    End With
  23. End Sub
½Æ»s¥N½X

TOP

¦^´_ 8# stillfish00

§Ú¨Ï¥Îªº¬OEXCEL 2010¡A°õ¦æQueryTables.delete¨Ã¤£·|±N¶×¤J¸ê®Æ§R°£¡A¥u·|§â³s½u§R°£¦Ó¤w

¥t¥~¡A§Ú¦b¥t¦s¶×¤J¸ê®Æ«á¡A¤]·|¦b°õ¦æworksheets.cells.delete¡A©Ò¥H¸ê®Æ¬O¤£·|¦V¥k²Ö¿nªº

·PÁÂ

TOP

¦^´_ 9# sasho

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

TOP

        ÀR«ä¦Û¦b : ¤ß¤¤±`¦sµ½¸Ñ¡B¥]®e¡B·P«ä¡Bª¾¨¬¡B±¤ºÖ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD