½Ð°Ý¬°¤°»ò°õVBA®É¡A°O¾ÐÅé·|¶V¨Ó¶V¤j¡AÅܦ¨³t«×ÅܺC

ashin1981

½Ð°Ý§Ú¦³¼g¤@­Óµ{¦¡¬O¤U¸üªÑ²¼ªºÀ禬¡B¨C¤éªÑ»ù¡B¿Ä¸ê¿Ä²²¡A¨C¤é¤j½L«ü¼Æµ¥¡AÃþ¦ü«ö¤@­Ó«ö¶s¡A´N·|¥hºô¯¸¤U¸ü¨ìsheet¡A¤§«á¦b°µ¤@¨Ç³B²z¡A
¨ä¤¤¦³¤@¨Ç¬O¥ÎCSVÀÉŪ¨ú¸ê®Æ®w¤è¦¡¥h°µ³B²z¡A¦³±Nrs.closeÃö³¬¡A¤@¨Ç¬O¥Îexecelªºweb¥\¯à¥h¤U¸ü«á³B²z¡A
¥u¬O¤£ª¾¹D¬°¤°»ò·í§Ú¨C«ö¤@¦¸¤U¸ü«ö¯Ã¡A¤U¸ü¨ì¦P¤@­Ósheetªº¦ì¸m¡A¤]¨S¦³¦b¤U¸ü¨ì·sªº¦ì¸m¡A·Ó¹D²z¤£·|¼W¥[excelÀɮתº¤j¤p¡A¦Ó§Ú¥Î"¤u§@ºÞ²z­û"¬Ýexcel.exe°O¾ÐÅé³£·|ºCºCÅܤj¡AµM«á°õ¦æexcel´N·|¶V¨Ó¶Vlag¡A¥Î·Æ¹«ºu½ü´N¥i¥H·Pı¨ìÅܺC¡A
§Ú¥»¨Ó¥H¬°¬O"¥þ°ìÅܼÆ"ªºÃö«Y¡A¦³§â¤j³¡¤Àªº¥þ°ìÅܼƧ令sub¸ÌªºÅܼơAÅý¥L¦bsubµ{§Çµ²§ô¡AÅܼƴNÄÀ©ñ±¼¤F¡A¦ý«á¨Ó¦n¹³¤]¤£¬O³o­Ó­ì¦]¡A
½Ð°Ýª©¥D©Î¨ä¥L¤j¤j¡Aª¾¹D¬O¤°»ò­ì¦]¶Ü¡C
¨C­ÓÅܼƳ̫á¤]³£·|¤U³o­Ó«ü¥O Set WebShtWan = Nothing,Åý°Ñ¦ÒÄÀ©ñ°O¾ÐÅé¡A¦n¹³¤]¨S¥Î¡C
¹q¸£¨t²Î¬O¥Îwin 7,excel2010¡A°O¾ÐÅé4G¡C

ashin1981

¦^´_ 1# ashin1981


    ¸É¥R»¡©ú:1.¥u­n§ÚÃö³¬excelµ{¦¡¦A¥´¶}¡A¥Î¤u§@ºÞ²z­ûÆ[¹îexcel.exe°O¾ÐÅé¡A´N·|«ì´_¨ì­ì¥»ªº¤j¤p¡C
                     2. §Ú³o¥x¹q¸£®Ä¯à¤£¬O«Ü¦n¡A¤w¸g¬O¦Ü¤Ö3¡B4¦~¦³¤F§a¡ACPU¤]¬O¥Îintel celeron E3400 2.6G¡C

c_c_lai

¦^´_ 2# ashin1981
¦¹ÃþÄÀºÃ°ÝÃD¡A³Ì¦n½Ð¤W¶Ç°ÝÃDÀɮסA
¥H¤è«K¦U¦ì¤j¤j´ú¸Õ»PÀˬd§Aµ{¦¡½X
¼gªk­þ¸Ì¦³¥X¤J¡C

stillfish00

¦^´_ 1# ashin1981

¤@¨Ç¬O¥Îexecelªºweb¥\¯à¥h¤U¸ü«á³B²z

¥Î§¹ªºWeb³s½u¦³§R°£¶Ü? (¸ê®Æ>³s½u)

ashin1981

¥H¤U¬O§Ú¤U¸üªºµ{¦¡¡C
===========¨Ï¥Îweb¤U¸ü¦Üsheet¤W====================
Sub ¤U¸ü¨C¤éªÑ»ùWeb_1()

       Dim WbSht¿Ä¸ê¿Ä¨÷ As Worksheet
       Dim WebShtDailyVal As Worksheet
       Dim TwseURL_daily$
''     Dim Workday As Integer
''     Dim Response
       WorkDay = Application.Evaluate("WEEKDAY(TODAY())-1")
''
''     If Workday = 6 And flag = 0 Then
''     m = m - 1
''     m = "0" & m
''     End If
Set WbSht¿Ä¸ê¿Ä¨÷ = Sheets("¿Ä¸ê¿Ä¨÷")
Set WebShtDailyVal = Sheets("¨C¤éªÑ»ù")

Select Case Month(Now)
   Case 1, 2
      startYear = Year(Now) - 1
      startMon = 10
   Case 3, 4
      startYear = Year(Now) - 1
      startMon = 11
   Case Else
     startYear = Year(Now)
     startMon = Month(Now) - 4
   End Select



'''If Month(Now) = 2 Then
'''
'''   startYear = Year(Now) - 1
'''   startMon = 10
'''ElseIf Month(Now) = 1 Then
'''    startYear = Year(Now) - 1
'''     startMon = 10
'''ElseIf Month(Now) = 3 Then
'''    startYear = Year(Now) - 1
'''     startMon = 11
'''Else
'''   startYear = Year(Now)
'''   startMon = Month(Now) - 3
'''End If
                   ''http://www.twse.com.tw/ch/trading/exchange/STOCK_DAY/genpage/Report201506/201506_F3_1_8_2379.php?STK_NO=2379&myear=2015&mmon=06
                   ''http://www.twse.com.tw/ch/trading/exchange/STOCK_DAY/genpage/Report201507/201507_F3_1_8_2379.php?STK_NO=2379&myear=2015&mmon=07
TwseURL_daily = "URL;http://www.twse.com.tw/ch/trading/exchange/STOCK_DAY/genpage/Report" & y & m & "/" & y & m & "_F3_1_8_" & uRng3.Text & ".php?STK_NO=" & uRng3.Text & "&myeay=" & y & "&mmon=" & m
''¿Ä¸ê¿Ä¨÷URL = "URL;http://easyfun.concords.com.tw/z/zc/zcn/zcn.djhtm?a=" & uRng3.Text & "&c=2016-1-1&d=2016-2-23"
''¿Ä¸ê¿Ä¨÷URL = "URL;http://easyfun.concords.com.tw/z/zc/zcn/zcn.djhtm?a=" & uRng3.Text & "&c=" & Year(Now) & "-" & Month(Now) - 1 & "-" & Day(Now) - 20 & "&d=" & Year(Now) & "-" & Month(Now) & "-" & Day(Now)
  ¿Ä¸ê¿Ä¨÷URL = "URL;http://easyfun.concords.com.tw/z/zc/zcn/zcn.djhtm?a=" & uRng3.Text & "&c=" & startYear & "-" & startMon & "-" & 1 & "&d=" & Year(Now) & "-" & Month(Now) & "-" & Day(Now)
                 ''http://easyfun.concords.com.tw/z/zc/zcn/zcn.djhtm?a=2379&c=2016-1-24&d=2016-2-24
                 ''http://easyfun.concords.com.tw/z/zc/zcn/zcn.djhtm?a=1101&c=2016-1-23&d=2016-2-23
''TwseURL_daily = "URL;http://www.twse.com.tw/ch/trading/exchange/STOCK_DAY/genpage/Report" & y & "07" & "/" & y & "07" & "_F3_1_8_" & uRng3.Text & ".php?STK_NO=" & uRng3.Text & "&myeay=" & y & "&mmon=" & m
''TwseURL_daily = "URL;http://www.twse.com.tw/ch/trading/exchange/STOCK_DAY/genpage/Report" & y & m "/" & y & m "_F3_1_8_" & uRng3.Text ".php?STK_NO=" &uRng3.Text "&myeay=" &y "&mmon=" &m
Application.EnableCancelKey = xlErrorHandler

WebShtDailyVal.Cells.Clear

If flag = False Then
    WbSht¿Ä¸ê¿Ä¨÷.Cells.Clear
End If

ErrNo = 0
On Error GoTo 101

    With WebShtDailyVal.QueryTables.Add(Connection:= _
         TwseURL_daily, Destination:=WebShtDailyVal.[A1])
        ''.Name = "201507_F3_1_8_2379.php?STK_NO=2379&myear=2015&mmon=07_2"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "8"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
   
   



''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''¿Ä¸ê¿Ä¨÷¤U¸ü¶}©l'''''''''''''''''''''''''''''''''''''''
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    If flag = False Then
      
          With WbSht¿Ä¸ê¿Ä¨÷.QueryTables.Add(Connection:= _
           ¿Ä¸ê¿Ä¨÷URL, Destination:=WbSht¿Ä¸ê¿Ä¨÷.[B4])
            .Name = "zcn.djhtm?a=1101&c=2016-1-23&d=2016-2-23"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlWebFormattingNone
            .WebTables = "2"
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
        
    End If
''Exit Sub
   
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''¿Ä¸ê¿Ä¨÷¤U¸üµ²§ô'''''''''''''''''''''''''''''''''''''''
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

101: ErrNo = Err.Number

If ErrNo = 1004 Then

    Exit Sub
    m = m - 1
    If m = 0 And d = "01" Or WorkDay = 6 Or WorkDay = 0 Then
         m = 12
         y = y - 1
    Else
        m = "0" & m
    End If
    Call ¤U¸ü¨C¤éªÑ»ùWeb_1
   
End If




If flag = 1 Then
Call ¸ü¤J¨C¤éªÑ»ù©M¤j½L«ü¼Æ©M¦¨¥æ¶q©M¤T¤jªk¤H©M¿Ä¸ê¨÷
End If

Set WbSht¿Ä¸ê¿Ä¨÷ = Nothing
Set WebShtDailyVal = Nothing

End Sub

ashin1981

¥H¤U¬O¥ÎŪ¨úCSVÀɤ覡¤U¸ü¦Üsheet¡A
¥Ø«e½T©w¬O¥Îweb¤è¦¡·|Åý°O¾ÐÅéÅܤj¡C
===========¨Ï¥ÎCSVŪÀɤU¸ü¦Üsheet¤W====================

Sub ¤U¸ü¨C¤ëÀ禬_CSV()

year1 = 2008
month1 = "01"
Row = 6
col = 8

Dim F As Boolean
Set WebSht1 = Sheets("¾ú¦~À禬")
Set rs = CreateObject("ADODB.Recordset")
s1 = uRng3.Text


Do While year1 <= Year(Date)

month1 = "01"
strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbookPath & "\¨C¦~À禬\" & year1 & "\;" _
& "Extended Properties=""text;HDR=Yes;FMT=Delimited"";"

For I2 = 1 To 12 Step 1

''strSQL = "SELECT * FROM 20150301.csv WHERE ÃÒ¨é¥N¸¹ LIKE '%" & s1 & "%'"

    s = ThisWorkbookPath & "\¨C¦~À禬\" & year1 & "\" & year1 & month1 & ".csv"
    F = CreateObject("Scripting.FileSystemObject").FileExists(s)
    If F = True Then
        strSQL = "SELECT * FROM " & year1 & month1 & ".csv WHERE ¤½¥q LIKE '%" & s1 & "%'"
    Else
         month1 = month1 + 1
         If month1 < 10 Then
            month1 = "0" & month1
         End If
        
         GoTo a:
    End If
        
rs.Open strSQL, strcon, 3, 3

If rs.BOF And rs.EOF Then
   
   rs.Close
   month1 = month1 + 1
   
If I2 = 12 Then
   year1 = year1 + 1
   ''count = count + 1
End If

If month1 < 10 Then
month1 = "0" & month1
End If
   GoTo a:
   

End If

''rs.MoveFirst

Do
  

   Row = (year1 - 2008) * (6 + AddSmallRow) + 1
   col = month1 + 7 + 3 + AddCol
   On Error Resume Next
   ''On Error GoTo Handler
   uRng3(Row, col) = rs("¥»¦~«×¥»¤ë")
   uRng3(Row + 1, col) = (uRng3(Row, col) - rs("¤W¦~«×¥»¤ë")) / rs("¤W¦~«×¥»¤ë")
   
  
   rs.MoveNext
Loop Until rs.EOF


month1 = month1 + 1


If month1 < 10 Then
month1 = "0" & month1
End If

rs.Close

a: Next I2



year1 = year1 + 1
''count = count + 1
''rs.Close

Loop

Set rs = Nothing 'ÄÀ©ñª«¥óÅܼÆ
Set WebSht1 = Nothing

Handler: Exit Sub

End Sub

stillfish00

¦^´_ 5# ashin1981
«Øij¦³¥Î¨ì QueryTables ªº¦a¤è
³£¦b .Refresh BackgroundQuery:=False
ªº¤U¤è¦h¤@¦æ .Delete  ¨Ó§R°£³s½u

ashin1981

ÁÂÁÂS¤j

¥[.DELETE±¡ªp¦³¤ñ¸û¦n¤@ ÂI¡A¥t­è­è°õ¦æŪ¨úCSVÀɤ覡¡A¤@°õ¦æ°O¾ÐÅé¤]·|Åܤj¡A¤ñweb¤è¦¡°O¾ÐÅé·|Åܤj¸û¦h¡A¬O¦³­þ¸Ìº|±¼¶Ü¡C

ashin1981

s¤j !

¬Ý°_¨Ó¥[¤W.delete¥H«á¦n«Ü¦h¡A°õ¦æ¤[¤F·Æ¹«ºu½ü¤]¤£·|ÅܫܺC¡A³Ñ¤U§Ú¦Û¤v¦b¸Ñ¨M¡A¦³°ÝÃD¦A¤W¨Ó°ÝÁÂÁ¡C

Qektyyrwp

¦^´_ 7# stillfish00

½Ð°Ý§Ú¦³¤@¬qµ{¦¡¦p¤U
     Range("ªí®æ_¨Ó¦Û_mes14g_ªº¬d¸ß1013518725055[[#Headers],[LOT_ID]]").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

delete­n«ç»ò¼g¶i¥h¡A«ô°U¤j¤j¸Ñ´b