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

ExcelVBA§ìAccess¸ê®Æ³t«×Àu¤Æ½Ð¯q

ExcelVBA§ìAccess¸ê®Æ³t«×Àu¤Æ½Ð¯q

Access¡A8,500µ§¸ê®Æ
EXCEL¡A116,000µ§¸ê®Æ

­ì½Ì§Ú¨S¦³Åv­­¤W¶Ç»P¤À¨Éºô§}....

ÅÞ¿è¬O¥ý¨ìEXCEL§ì¨ú®Æ¸¹¸ê®Æ«á¡A¨ìAccess¸ê®Æ®w§ì¨ú¦P®Æ¸¹ªº¼Æ¶q¸ê°T¡A¥N¤JExcel

¥Ø¼Ð¬O³z¹LAccess¸ê®Æ®w·f°tExcel VBAªº¼Ò¦¡ÅýÁs¸~ªºExcel¨ç¼Æ¯à°÷§Ö¤@ÂI¡A§_«h­ì¥»ªº¸ê®Æ¤Ó¤j¡A¤½¥q¹q¸£µLªk­t²ü
¦ý¼g§¹µ{¦¡«á¡A«oµo²{§ìªº¤ñ§ÚExcel¥ÎVlookupÁÙºC¡A¥u¤ñ¹ï«e1500µ§´N­n15¬í

¬O§_§ÚVBA­þ¸Ì¼gªº®Ä²v¤Ó§C©O¡A³Â·Ð°ª¤â§U§Ú«üÂI°g¬z!!
  1. Sub CreateQueryRS()
  2. Application.Calculation = xlCalculationManual
  3. Application.ScreenUpdating = False
  4. Application.DisplayStatusBar = False
  5. Application.EnableEvents = False
  6.     Dim cnADO As Object
  7.     Dim rsADO As Object
  8.     Dim strPath As String
  9.     Dim strSQL As String
  10.     Dim j As String
  11.     Dim i As Long
  12.     Set cnADO = CreateObject("ADODB.Connection")
  13.     Set rsADO = CreateObject("ADODB.RecordSet")
  14.     strPath = ThisWorkbook.Path & "\Database11.accdb"
  15.     Range("ªí®æ1[¤W´Á´Á¥½ª÷ÃB]").ClearContents

  16.     'ThisWorkbook.Sheets("11009¶i¾P¦s©ú²Óªí").Cells(2, 3).Value = ThisWorkbook.Sheets("11009¶i¾P¦s©ú²Óªí").Range("ªí®æ1[®Æ«~½s¸¹]").Rows.Count


  17.     On Error GoTo ErrMsg
  18.    
  19.     cnADO.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strPath

  20.     For i = 0 To 1500
  21. '    For i = 0 To ThisWorkbook.Sheets("11009¶i¾P¦s©ú²Óªí").Range("ªí®æ1[®Æ«~½s¸¹]").Rows.Count - 1
  22.    
  23.         j = ActiveSheet.Cells(4 + i, 3).Value
  24.         strSQL = "SELECT SUM(¥æ©ö¼Æ¶q) FROM B1¶i³f¸ê®Æ WHERE ®Æ«~½s¸¹= '" & j & "' AND Ãþ§O='¶i³f'"
  25.         rsADO.Open strSQL, cnADO, 1, 3
  26.         'Range("A410").CopyFromRecordset rsADO
  27.         ActiveSheet.Cells(4 + i, 1).CopyFromRecordset rsADO
  28.         rsADO.Close

  29.     Next i

  30.     cnADO.Close
  31.     Set rsADO = Nothing
  32.     Set cnADO = Nothing
  33. Application.Calculation = xlCalculationAutomatic
  34. Application.ScreenUpdating = True
  35. Application.DisplayStatusBar = True
  36. Application.EnableEvents = True
  37.     Exit Sub
  38. ErrMsg:
  39.     MsgBox Err.Description, , "¿ù»~³ø§i"
  40. End Sub
½Æ»s¥N½X

        ÀR«ä¦Û¦b : ¡i»X½ªªº¦Û¥Ñ¡j¤H±`¦b¤°»ò³£¥i¥H¦Û¥Ñ¦Û¦bªº®É­Ô¡A«o³Q³oºØÀH¤ß©Ò±ýªº¦Û¥Ñ»X½ª¡AµêÂY®É¥ú¦Ó²@µLıª¾¡C
ªð¦^¦Cªí ¤W¤@¥DÃD