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

±N¦h­ÓÀɮצP¤@­ÓÄæ¦ì¸ê®Æ½Æ»s¶°¤¤¨ì1­ÓÀÉ®×

±N¦h­ÓÀɮצP¤@­ÓÄæ¦ì¸ê®Æ½Æ»s¶°¤¤¨ì1­ÓÀÉ®×

·Q§â¦P¤@­Ó¸ê®Æ§¨¸Ìªº20­ÓÀɮצP¼ËÄæ¦ìªº¸ê®Æ½Æ»s¶°¤¤¨ì1­ÓÀɪº¦P1­Ó¤u§@ªí¸Ì­±
¦p¹Ï1,¦P¤@­ÓÀɮק¨¦³ÀɦW¦M"01.02~19.20"µ¥20­ÓexcelÀɪºA~Iµ¥9­ÓÄæ¦ì¸Ìªº¸ê®Æ
½Æ»s¨ìÀɦW¬°"¶°¤¤"ªº¤u§@ªí"¶°¤¤"¸Ì(¦p¹Ï3)
½Æ»sªº¶¶§Ç»P¦ì¸m«h¬°¦b½Æ»s«e¥ý©óÀɦW¬°"¶°¤¤"ªº¤u§@ªí"¶¶§Ç"¸Ì¥ý¶ñ¦n(¦p¹Ï2)

¹Ï1.jpg (60.67 KB)

¹Ï1.jpg

¹Ï2.jpg (81.88 KB)

¹Ï2.jpg

¹Ï3.jpg (347.13 KB)

¹Ï3.jpg

20­ÓÀɮ׶°¤¤¦Ü1­ÓÀÉ.rar (831.29 KB)

¦^´_ 1# oak0723-1

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ

Sub ¶×¾ãÀÉ®×()
Dim Arr, fs, fc, f1, fn$, xC0, xC1, R%
Application.ScreenUpdating = False: Application.DisplayAlerts = False: Application.AskToUpdateLinks = False
Tm = Timer
Set fs = CreateObject("Scripting.FileSystemObject")
PH = ThisWorkbook.Path
Set f = fs.GetFolder(PH): Set fc = f.Files
For Each f1 In fc
    If InStr(f1.Name, "¶°¤¤") Then GoTo 99
    If InStr(f1.Name, "~") Then GoTo 99
    With Workbooks.Open(f1.Path)
        fn = Split(f1.Name, ".")(0)
        Arr = Sheets(1).Range("i6").CurrentRegion
        .Close
    End With
    With Sheets(1)
        R = .Range("c65536").End(3).Row + 1
        .Range("c" & R).NumberFormatLocal = "@"
        .Range("c" & R) = fn
        If xC0 = 0 Then
            xC0 = 1: xC1 = UBound(Arr, 2)
        Else
            xC0 = xC1 + 5: xC1 = xC0 + UBound(Arr, 2) - 1
        End If
        .Range("d" & R) = Replace(Cells(1, xC0).Address(0, 0), "1", "")
    End With
    Sheets(2).Cells(6, xC0).Resize(UBound(Arr), UBound(Arr, 2)) = Arr
99: Next
Application.ScreenUpdating = True: Application.DisplayAlerts = True: Application.AskToUpdateLinks = True
MsgBox "°õ¦æ§¹¦¨" & Timer - Tm & " ¬í"
End Sub

TOP

·P®¦.·P®¦
ÁÂÁÂ

TOP

­Y¿é¤J®æ¦¡§ï¦¨¥i¿é¤JÀɦW©M¤u§@ªí
¨Ì©Ò¿é¤JªºÀɦW©M¤u§@ªí°õ¦æ
­n«ç»ò¼g³o­ÓVBA

Image 013.jpg (76.94 KB)

Image 013.jpg

(1121015)20­ÓÀɮ׶°¤¤¦Ü1­ÓÀÉ.rar (835.41 KB)

TOP

¦^´_ 4# oak0723-1


    ÁÂÁ«e½úµoªí¦¹±¡¹Ò»P½d¨Ò
«á¾ÇÂǦ¹©«½m²ßVBA,¾Ç²ß¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò

°õ¦æµ²ªG:




Option Explicit
Sub TEST()
Dim Arr, i%, Ph$, xS As Worksheet, xB As Workbook, K%
Application.ScreenUpdating = False
Set xB = ThisWorkbook: Ph = xB.Path & "\"
Arr = Range([¶¶§Ç!E2], [¶¶§Ç!C65536].End(3))
Sheets("¶°¤¤").Cells.Clear
For i = 1 To UBound(Arr)
   On Error Resume Next
   Set xS = Workbooks(Arr(i, 1) & ".xlsx").Sheets(Arr(i, 2))
   If Err.Number <> 0 Then
      Set xS = Workbooks.Open(Ph & Arr(i, 1) & ".xlsx").Sheets(Arr(i, 2))
      K = 1
   End If
   On Error GoTo 0
   If xS Is Nothing Then
      MsgBox Arr(i, 1) & " ¬¡­¶Ã¯, " & Arr(i, 2) & " ¤u§@ªí¤£¦s¦b!µ²§ô°õ¦æ"
      Exit Sub
   End If
   xS.[A:I].Copy xB.Sheets("¶°¤¤").Cells(1, Arr(i, 3))
   If K = 1 Then xS.Parent.Close 0: K = 0
   Set xS = Nothing
Next
Set xB = Nothing: Erase Arr
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

Sub test2()
Set CN = CreateObject("adodb.connection"): V = Application.Version
If V >= 12 Then V = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;"
If V < 12 Then V = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;"
Set s = Sheets("¶¶§Ç"): Set s0 = Sheets("¶°¤¤"): s0.Cells.ClearContents
AR = Array("select * from [sheet1$A:I]", "select * from [¤u§@ªí1$A:I]")
For i = 2 To s.Cells(Rows.Count, 2).End(3).Row
If Dir(ThisWorkbook.Path & "\" & s.Cells(i, "C") & ".xlsx") <> "" Then
    CN.Open V & "Data Source=" & ThisWorkbook.Path & "\" & s.Cells(i, "C") & ".xlsx"
    On Error Resume Next
    Set rs = CN.Execute("select * from [" & s.Cells(i, "D") & "$A:I]")
    If CN.Errors.Count <> 0 Then: CN.Errors.Clear: Set rs = CN.Execute(AR(0))
    If CN.Errors.Count <> 0 Then: CN.Errors.Clear: Set rs = CN.Execute(AR(1))
    On Error GoTo 0
    s0.Range(s.Cells(i, "E") & 2).CopyFromRecordset rs
    s0.Columns(s.Cells(i, "E").Value).NumberFormatLocal = "h:mm:ss;@"
    s0.Range(s.Cells(i, "E") & 6).Resize(1, 9) = Split("A,B,C,D,E,F,G,H,I", ",")
    CN.Close
End If
Next
End Sub

TOP

¦^´_ 6# singo1232001


    ÁÂÁ«e½úµoªí³o¤£¥²¶}±ÒexcelÀÉ®×´N¥i½Æ»s¸ê®Æªº¤è¦¡
½Ð°Ý³o¨Ç»yªk¦p¦ó¤Jªù¾Ç°_?
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 6# singo1232001


   «D±`·PÁÂ~~

TOP

¦^´_ 5# Andy2483


    ·P®¦·P®¦
«D±`·P®¦~~

TOP

¦^´_ 7# Andy2483


youtube  SQL SERVER  ¦w¸Ë
youtube  SSMS ¦w¸Ë
youtube  OR  bilibili ©|Öº¨¦ SQL±Ðµ{
   
GPT4
ADODB.Connection

¥t¥~¥iÂX¥R¾Ç²ß
chrome »·ºÝ¥\¯à
vmware1.6

TOP

        ÀR«ä¦Û¦b : ¦a¤WºØ¤Fµæ¡A´N¤£©öªø¯ó¡F¤ß¤¤¦³µ½¡A´N¤£©ö¥Í´c¡C
ªð¦^¦Cªí ¤W¤@¥DÃD