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

[µo°Ý] ACCESSºô­¶¤W§ì¨ú¸ê®Æ

[µo°Ý] ACCESSºô­¶¤W§ì¨ú¸ê®Æ

¥H¤Uªºµ{¦¡½X¬O¦bª©¤W¬Ý¨ìªº
½Ð°Ý¦U¦ì¤j¤j¦³¥i¯àª½±µ§â¸ê®Æ¦s¤JACCESS¶Ü
·PÁÂ

Sub ²©ö©ú²Ó¤U¸ü()
    Dim ªÑ²¼¥N¸¹ As String, ¤é´Á As Variant, N, i As Integer, A, T As Date
    Do While Not IsDate(¤é´Á)
        ¤é´Á = InputBox("¿é¤J¬d¸ß¤é´Á", "¤é´Á", Date)
        If ¤é´Á = "" Then End
    Loop
    Do While ªÑ²¼¥N¸¹ = ""
        ªÑ²¼¥N¸¹ = InputBox("ªÑ²¼¥N¸¹", "¿é¤J¬d¸ß¤§ªÑ²¼¥N¸¹", "1101")
        If ¤é´Á = "" Then End
    Loop
    ¤é´Á = Format(¤é´Á, "yyyymmdd")
    T = Time
    With ActiveSheet
        For Each N In .Names
            N.Delete
        Next
        .Cells.Clear
        Application.StatusBar = False
        On Error GoTo A_Wait
        i = 1
        Do
            .Cells(Rows.Count, 1).End(xlUp).Offset(1).Select
            With .QueryTables.Add(Connection:="URL;http://bsr.twse.com.tw/bshtm/bshtm_report_Messages.aspx?strDate=" & ¤é´Á & "&StartNumber=" & ªÑ²¼¥N¸¹ & "&FocusIndex=" & i, Destination:=Selection)
                .Name = ¤é´Á & "_" & ªÑ²¼¥N¸¹ & "_" & i
                .WebSelectionType = xlSpecifiedTables
                .WebFormatting = xlWebFormattingNone
                .WebTables = "6"
                .WebPreFormattedTextToColumns = True
                .WebConsecutiveDelimitersAsOne = True
                .WebSingleBlockTextImport = False
                .WebDisableDateRecognition = False
                .WebDisableRedirections = False
              ''''''µLªk¬d¸ß®Éµy«Ý  ¨ì  A_Wait: '''''
                .Refresh BackgroundQuery:=False
                If Application.CountA(.ResultRange) = 0 Then GoTo Out
                i = i + 1
            End With
            A = CreateObject("WScript.Shell").popup("½Ðµ¥«á¤U¸ü..." & Chr(10) & Chr(10) & "** ½Ð¤Å«ö¤U  [½T©w] **", 4, ¤é´Á & "_" & ªÑ²¼¥N¸¹ & "  ²Ä" & i & "­¶", 16 * 3 + 0)
            Application.ScreenUpdating = True
        Loop
Out:
        .UsedRange.Columns.AutoFit
        .[A1].Select
        A = CreateObject("WScript.Shell").popup("¦@¤U¸ü" & i & "­¶", 5, ¤é´Á & "_" & ªÑ²¼¥N¸¹, 48 + 0)
        Application.StatusBar = ªÑ²¼¥N¸¹ & " ¦@¤U¸ü " & i & "­¶ ¶O®É " & Format(Time - T, "HH:MM:SS")
    End With
    End
A_Wait:
    Application.StatusBar = "µLªk¬d¸ßµ¥­Ô5¬íÄÁ"
    Application.Wait Now + TimeValue("00:00:05")
    Err.Clear
    Application.StatusBar = False
    Resume    '­«ªð¬d¸ß
End Sub
jayer

·PÁÂHSIEN6001
¤£¦n·N«ä¡A§Ú¥u·Qª¾¹D­ÓªÑ¥L¦b¬Y¤@¬q®É¶¡¤º¦b­Ó¨é°Óªº¶i¥Xª¬ªp
¥Ø«e¤]¤£ª¾¹D¦³¨S¦³¥Î
¤£¹L¬Û«H¦pªG·|¤ÀªRÀ³¸Ó¥Î¦³ÂI¥Î§a
¥i¥H³Â·Ð±z±Hµ¹§Ú¶Ü
§Ú¤£¯à¤U¸ü
·PÁÂ
mail:[email protected]
jayer

TOP

¥Ø«e¬Ý®Ñ§ä¨ì¤@­Óµ{§Ç
µ{¦¡½X¦p¤U¬O¥i¥HºCºCªº±N¸ê¤JÂà¶i¸ê®Æ®w¸Ì³á
½Ð°Ý¦³¤j¤j¦³§ó¦nªº¤èªk¶Ü


Public Sub °õ¦æSQL±Ô­z()

    Dim cnnDB As New ADODB.Connection '«Ø¥ß¸ê®Æ®w³sµ²ª«¥ó
   
    Set cnnDB = CurrentProject.Connection '¨ú±o¸ê®Æ®w³sµ²
   
    cnnDB.Execute " INSERT INTO ª¿²Îªº½Æ¥» " _
        & "SELECT * " _
        & "FROM [sheet2];"
           
    '°õ¦æSQL±Ô­z
   
    cnnDB.Close 'Ãö³¬¸ê®Æ®w³sµ²
   
    Set cnnDB = Nothing '­«³]¸ê®Æ®w³sµ²
   
End Sub
jayer

TOP

        ÀR«ä¦Û¦b : ¬°¦Û¤v§äÂǤfªº¤H¥Ã»·¤£·|¶i¨B¡C
ªð¦^¦Cªí ¤W¤@¥DÃD