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

[µo°Ý] EXCEL VBA ³s±µACCESS­pºâ¤º®e

[µo°Ý] EXCEL VBA ³s±µACCESS­pºâ¤º®e

½Ð±Ð¤@¤U¦U¦ì°ª¤â
¦pªþ¥ó¤è¦¡¡A¥Ø«e§Ú¥u·|¤U³æ¤@­Ó±ø¥ó¶i¦æ·j´M
1.¦pªG·Q­n´M§ä³ø¤u¤é´Á°Ï¶¡¡A©Î¬O©Ò¦³¸ê®Æ¦P®É¤U¸ü¡A±ø¥ó­n¦p¦ó¤U
2.¤U¸ü®É¯à¦P®É­pºâ¦p¤u§@ªí¤G¡A¹³¬O¨Ï¥Îsumifs©Î¬Ovlookup

¦A½Ð¦U¦ì°ª¤âÀ°À°¦£

test12.rar (654.58 KB)

¦^´_ 1# wsx1130

1.¦pªG·Q­n´M§ä³ø¤u¤é´Á°Ï¶¡¡A©Î¬O©Ò¦³¸ê®Æ¦P®É¤U¸ü¡A±ø¥ó­n¦p¦ó¤U
>>½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
Sub test()
Dim conn As New ADODB.Connection
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=D:\database-test.mdb"
Sheets(3).Range("a1").CopyFromRecordset conn.Execute("select * from [DailyReport43600]")
conn.Close
End Sub

TOP

¦^´_  wsx1130

1.¦pªG·Q­n´M§ä³ø¤u¤é´Á°Ï¶¡¡A©Î¬O©Ò¦³¸ê®Æ¦P®É¤U¸ü¡A±ø¥ó­n¦p¦ó¤U
>>½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁ ...
samwang µoªí©ó 2021-12-7 22:01


·PÁÂsamªº¥N½X¡A¤w¸g¥i¥H¥þ³¡¤U¸ü¤U¨Ó¤F

TOP

¦^´_ 1# wsx1130

2.¤U¸ü®É¯à¦P®É­pºâ¦p¤u§@ªí¤G¡A¹³¬O¨Ï¥Îsumifs©Î¬Ovlookup
>> ¬õ¦â®Øªº¶µ¥Ø¬O«ç»ò¨Óªº?? ¬O©T©wªº»ò? ½Ð½T»{¡AÁÂÁÂ
2.JPG

TOP

¦^´_ 1# wsx1130

2.¤U¸ü®É¯à¦P®É­pºâ¦p¤u§@ªí¤G¡A¹³¬O¨Ï¥Îsumifs©Î¬Ovlookup
>> ¬õ¦â®Øªº¶µ¥Ø¬O«ç»ò¨Óªº?? ¬O©T©wªº»ò? ½Ð½T»{¡AÁÂÁÂ
>> ®É¶¡³£¬O§ì¤u³æ¸¹½Xªº²Ä1µ§¶Ü??
¦pªG®É¶¡¬O­n¥H¤u³æ¸¹½X+»sµ{²ºÙ¬°¥D¡A¬O­n¨ú­þ¤@µ§®É¶¡
Ex: 17256+«~ºÞ²G¤â¤u¥]¸Ë ¦³2µ§®É¶¡(°²³]¤£¤@¼Ë)¡A­n¨ú­þ¤@µ§
11.JPG

TOP

¥»©«³Ì«á¥Ñ samwang ©ó 2021-12-8 11:33 ½s¿è

¦^´_ 1# wsx1130

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
1. ¤u³æ¸¹½X/»s¦¨Â²ºÙ: ±q¸ê®Æ®w¨ú°ß¤@­ÈµM«á¦Û°Ê¶×¥X
2. 2021/11/02 16:06_1080_2021/11/02 16:13
    ¤u³æ+»sµ{: ²Ä¤@µ§¶}©l®É¶¡ _ ²Ö¥[¼Æ¶q _ ³Ì«á§¹¦¨(max)®É¶¡

Sub test()
Dim conn As New ADODB.Connection
Dim Arr, xD, Brr(), xD1, C%, n%, m%, i&, SD, ED, cnt
Set xD = CreateObject("Scripting.Dictionary")
Set xD1 = CreateObject("Scripting.Dictionary")
With Sheets(3)
    .[a1].CurrentRegion.Offset(1) = ""
'    .[a1:i1] = Array("ID", "³ø¤u§Ç¸¹", "¤u¸¹", "¯Z§O¥N½X", "³ø¤u¤é´Á", "¤u³æ¸¹½X", "§@·~§Ç¸¹", "»sµ{²ºÙ", "§@·~²Ó¶µ")
'    .[j1:q1] = Array("¶}©l®É¶¡", "§¹¦¨®É¶¡", "®É¼Æ", "¼Æ¶q", "¤£¨}¼Æ¶q", "¤u³æ¸¹½X", "³Æµù", "­ì¦]¥N½X", "ª¬ºA")
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=D:\database-test.mdb"
    .Range("a2").CopyFromRecordset conn.Execute("select * from [DailyReport43600]")
    conn.Close
    Arr = .[a1].CurrentRegion
End With
ReDim Brr(1 To UBound(Arr), 1 To UBound(Arr))
For i = 2 To UBound(Arr): xD(Arr(i, 8) & "") = "": Next
With Sheets(4)
     .[a1].CurrentRegion = ""
    .Range("b1").Resize(1, xD.Count) = xD.keys
    .[a1] = "¤u³æ¸¹½X/»sµ{²ºÙ"
    For i = 2 To UBound(Arr)
        C = Application.WorksheetFunction.Match(Arr(i, 8), .Range(.[b1], .Cells(1, xD.Count + 1)), 0) + 1
        If xD1.Exists(Arr(i, 6) & "") Then
            m = xD1(Arr(i, 6) & "")
            If Brr(m, C) <> "" Then
                SD = Split(Brr(m, C), "_")(0)
                cnt = Split(Brr(m, C), "_")(1)
                ED = Split(Brr(m, C), "_")(2)
                If Arr(i, 11) > ED Then ED = Arr(i, 11)
            End If
            Brr(m, C) = SD & "_" & cnt + Arr(i, 13) & "_" & ED
        Else
            n = n + 1: xD1(Arr(i, 6) & "") = n
            Brr(n, 1) = Arr(i, 6)
            Brr(n, C) = Arr(i, 10) & "_" & Arr(i, 13) & "_" & Arr(i, 11)
        End If
    Next
    .Range("a2").Resize(n, xD.Count + 1) = Brr
End With
End Sub
33.JPG

TOP

¦^´_  wsx1130

2.¤U¸ü®É¯à¦P®É­pºâ¦p¤u§@ªí¤G¡A¹³¬O¨Ï¥Îsumifs©Î¬Ovlookup
>> ¬õ¦â®Øªº¶µ¥Ø¬O«ç»ò¨Óªº ...
samwang µoªí©ó 2021-12-8 09:41


¤£¦n·N«äSam¨º»ò±ß¤~¦^±z¡A¬õ¦â®Øªº³¡¤À¬O©T©w¡Aªº½T±z­pºâªº¬O§Ú·Q­nªº¤è¦¡¡A§Ú¦A¬ã¨s¸Õ¸Õ¬Ý
«D±`ªº·PÁ±z

TOP

¦^´_ 6# samwang
Sam¤j¤j¡A½Ð°Ý¤@¤U¦pªG»Ý­nsheet4»sµ{²ºÙ¦ì¸m¬O©T©w¶µ¥Ø(¤£¬O©Ò¦³ªº¡A¤â°Ê¿é¤J´X­Ó¯S©w»sµ{)¡A¯à¥Î³o¼Ëªº¤è¦¡­pºâ¶Ü

TOP

¦^´_  samwang
Sam¤j¤j¡A½Ð°Ý¤@¤U¦pªG»Ý­nsheet4»sµ{²ºÙ¦ì¸m¬O©T©w¶µ¥Ø(¤£¬O©Ò¦³ªº¡A¤â°Ê¿é¤J´X­Ó¯S©w»s ...
wsx1130 µoªí©ó 2021-12-18 00:00


·íµM¥i¥H¦p¦P¥ý«e½Í¨ìªº5#°ÝÃD¡A±ø¥ó­n¥ý­q¦n¡AÁÂÁÂ

®É¶¡³£¬O§ì¤u³æ¸¹½Xªº²Ä1µ§¶Ü??
¦pªG®É¶¡¬O­n¥H¤u³æ¸¹½X+»sµ{²ºÙ¬°¥D¡A¬O­n¨ú­þ¤@µ§®É¶¡
Ex: 17256+«~ºÞ²G¤â¤u¥]¸Ë ¦³2µ§®É¶¡(°²³]¤£¤@¼Ë)¡A­n¨ú­þ¤@µ§

TOP

¦^´_ 9# samwang

Sam¤j¤j¡A§ì¨ú²Ä¤@µ§ªº®É¶¡
ex: 177256 «~ºÞ²GQA¶K¼Ð¡B¸Ë½c   2021/11/02 16:06

TOP

        ÀR«ä¦Û¦b : ¯¸¦b¥b¸ô¡A¤ñ¨«¨ì¥Ø¼Ð§ó¨¯­W¡C
ªð¦^¦Cªí ¤W¤@¥DÃD