ExcelVBA§ìAccess¸ê®Æ³t«×Àu¤Æ½Ð¯q
- ©«¤l
- 1
- ¥DÃD
- 1
- ºëµØ
- 0
- ¿n¤À
- 2
- ÂI¦W
- 0
- §@·~¨t²Î
- windows 10
- ³nÅ骩¥»
- office 2007
- ¾\ŪÅv
- 10
- µù¥U®É¶¡
- 2017-1-11
- ³Ì«áµn¿ý
- 2021-10-20
|
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ªkt²ü
¦ý¼g§¹µ{¦¡«á¡A«oµo²{§ìªº¤ñ§ÚExcel¥ÎVlookupÁÙºC¡A¥u¤ñ¹ï«e1500µ§´Nn15¬í
¬O§_§ÚVBAþ¸Ì¼gªº®Ä²v¤Ó§C©O¡A³Â·Ð°ª¤â§U§Ú«üÂI°g¬z!!- Sub CreateQueryRS()
- Application.Calculation = xlCalculationManual
- Application.ScreenUpdating = False
- Application.DisplayStatusBar = False
- Application.EnableEvents = False
- Dim cnADO As Object
- Dim rsADO As Object
- Dim strPath As String
- Dim strSQL As String
- Dim j As String
- Dim i As Long
- Set cnADO = CreateObject("ADODB.Connection")
- Set rsADO = CreateObject("ADODB.RecordSet")
- strPath = ThisWorkbook.Path & "\Database11.accdb"
- Range("ªí®æ1[¤W´Á´Á¥½ª÷ÃB]").ClearContents
- 'ThisWorkbook.Sheets("11009¶i¾P¦s©ú²Óªí").Cells(2, 3).Value = ThisWorkbook.Sheets("11009¶i¾P¦s©ú²Óªí").Range("ªí®æ1[®Æ«~½s¸¹]").Rows.Count
- On Error GoTo ErrMsg
-
- cnADO.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strPath
- For i = 0 To 1500
- ' For i = 0 To ThisWorkbook.Sheets("11009¶i¾P¦s©ú²Óªí").Range("ªí®æ1[®Æ«~½s¸¹]").Rows.Count - 1
-
- j = ActiveSheet.Cells(4 + i, 3).Value
- strSQL = "SELECT SUM(¥æ©ö¼Æ¶q) FROM B1¶i³f¸ê®Æ WHERE ®Æ«~½s¸¹= '" & j & "' AND Ãþ§O='¶i³f'"
- rsADO.Open strSQL, cnADO, 1, 3
- 'Range("A410").CopyFromRecordset rsADO
- ActiveSheet.Cells(4 + i, 1).CopyFromRecordset rsADO
- rsADO.Close
- Next i
- cnADO.Close
- Set rsADO = Nothing
- Set cnADO = Nothing
- Application.Calculation = xlCalculationAutomatic
- Application.ScreenUpdating = True
- Application.DisplayStatusBar = True
- Application.EnableEvents = True
- Exit Sub
- ErrMsg:
- MsgBox Err.Description, , "¿ù»~³ø§i"
- End Sub
½Æ»s¥N½X |
|
|
|
|
|
|
- ©«¤l
- 976
- ¥DÃD
- 7
- ºëµØ
- 0
- ¿n¤À
- 1018
- ÂI¦W
- 0
- §@·~¨t²Î
- Win10
- ³nÅ骩¥»
- Office 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2013-4-19
- ³Ì«áµn¿ý
- 2024-10-30
|
¦^´_ 1# a5345534
ì½Ì§Ú¨S¦³Åv¤W¶Ç>> ¤W¶ÇÀ³¸Ó³£¥i¥H¤W¶ÇÀÉ®×
n§ïµ½³t«×¡A«Øij¥Î°}¦C+¦r¨å |
|
|
|
|
|
|