µù¥U
µn¿ý
º¶
½×¾Âª©³W
¸T¤î¦Cªí
»¡©ú
¦a¹Ï
¼Æ¦ì®Ñ»
¨p¤H®ø®§ (0)
¤½¦@®ø®§ (0)
½×¾Â¥ô°È (0)
¨t²Î®ø®§ (0)
¦n¤Í®ø®§ (0)
©«¤l®ø®§ (0)
³Â»¶®a±Ú°Q½×ª©ª©
»
VB »P VB.Net
» ½Ð°Ý¦p¦ó±NSQL¸ê®Æªí¤¤¬Y¤@Äæ¸ê®Æ½Æ»s¨ìEXCEL©O?
ªð¦^¦Cªí
¤U¤@¥DÃD
¤W¤@¥DÃD
µo©«
[µo°Ý]
½Ð°Ý¦p¦ó±NSQL¸ê®Æªí¤¤¬Y¤@Äæ¸ê®Æ½Æ»s¨ìEXCEL©O?
algoji3ji3
µoµu®ø®§
¥[¬°¦n¤Í
algoji3ji3
·í«eÂ÷½u
UID
23594
©«¤l
11
¥DÃD
5
ºëµØ
0
¿n¤À
16
ª÷¿ú
16
ÂI¦W
0
§@·~¨t²Î
win7
³nÅ骩¥»
sp3
¾\ŪÅv
10
¦b½u®É¶¡
24 ¤p®É
µù¥U®É¶¡
2016-2-9
³Ì«áµn¿ý
2020-2-21
¤p¾Ç¥Í
©«¤l
11
¥DÃD
5
ºëµØ
0
¿n¤À
16
ÂI¦W
0
§@·~¨t²Î
win7
³nÅ骩¥»
sp3
¾\ŪÅv
10
µù¥U®É¶¡
2016-2-9
³Ì«áµn¿ý
2020-2-21
1
#
¸õÂà¨ì
»
˧Ǭݩ«
¥´¦L
¦rÅé¤j¤p:
t
T
µoªí©ó 2018-10-30 19:55
|
¥u¬Ý¸Ó§@ªÌ
[µo°Ý]
½Ð°Ý¦p¦ó±NSQL¸ê®Æªí¤¤¬Y¤@Äæ¸ê®Æ½Æ»s¨ìEXCEL©O?
¥»©«³Ì«á¥Ñ algoji3ji3 ©ó 2018-10-30 19:57 ½s¿è
¦U¦ì¤j¤j½Ð°ÝCopyFromRecordset rs·|±N¾ãÓ¸ê®Æªí¿é¥X¨ìEXCEL¤W¡A¦ý¬O¦pªG§Ú¥un¨ä¤¤¤@¾ãÄæ¸ê®Æ¸Ó«ç»ò§ï©O?
Private Sub CommandButton2_Click()
'©w¸q³¡¥÷
Dim cn As ADODB.Connection 'Excel³s±µ
Dim rs As ADODB.Recordset '±o¨Óªºµ²ªG,·|©ñ¨ì¥ß³oÓrsªº¦a¤è.
Dim strDataSrcXlsPath As String '¼Æ¾Ú®wEXCEL¦ì¸m
Dim strQuery As String '¥Î§@®³¼Æ¾ÚªºSQL
Dim strStartlocation As String '©w¸q±o¨Ó¼Æ¾Ú,©ñ¨ì¨º¤@Ó¦ì¸m.
'I) ¥H¤U«Ø¥ß¼Æ¾Ú®w³s±µ
strDataSrcXlsPath = ThisWorkbook.Path & "\¸ê®Æ.xlsx" '©w¥ß¼Æ¾Ú®wEXCEL¦ì¸m
Set cn = New ADODB.Connection
With cn
'½T©w³s±µ©Ò¥ÎªºLibrary
.Provider = "Microsoft.ACE.OLEDB.12.0"
.CursorLocation = adUseClient
'§â³s±µ«ü¦V«ü©wExcel
'xtended properties¬°excelªºª©¥», ³q±`¬°12 ©Î 8,
'12¬°2007ExcelÀÉ(¦pXLSX,XLSM). 8¥i¬Û®e1997-2003µ¥excel(XLS)
.ConnectionString = "Data Source=" & strDataSrcXlsPath & _
";Extended Properties=Excel 12.0;"
'¶}©l³s±µ,³oÓ®ÉÔACE·|¥´¶}excel
.Open
End With
'II) ¥H¤U¹B¦æSQL©M¦^¶Ç¼Æ¾Ú
'®³¥X¤@¦æ¸ê®Æ,©Ò¥ÎSQL¬[ºc¤j¬ù¬°
'SELECT ... FROM ... WHERE
'
'SELECT ¤Wªº * ¥Î§@«ü©ú®³¥X©Ò¦³ªí¤WªºÄæ¦ì
'TOP 50 ¬O«ü©â¥XÀY50¦C¼Æ¾Ú
'
'FROM ±qDATASHEET¤u§@ªí©â¥X
'strQuery = "SELECT top 10 * FROM [Á`ªí$]"
strQuery = "SELECT ¯¸§O¤ÀªR,Ãþ§O,¤ë¥÷,¤é´Á,SUM(²£¥X¼Æ) AS Á`¼Æ " & _
"FROM [Á`ªí$] " & _
"where ¤é´Á between #2018/9/1# and #2018/9/30# " & _
"GROUP BY ¯¸§O¤ÀªR,Ãþ§O,¤ë¥÷,¤é´Á HAVING Ãþ§O='305AS'" & _
"ORDER BY ¯¸§O¤ÀªR,¤é´Á"
'strQuery = "SELECT ¤é´Á " & _
"FROM [Á`ªí$] " & _
"where ¤é´Á between #2018/9/1# and #2018/9/30# " 'HAVING Ãþ§O='305AS'"
Set rs = cn.Execute(strQuery) '°õ¦æSQL
'III) §â¼Æ¾Ú§Û¦Ü©Ò»Ýªº¦ì¸m
strStartlocation = "A1"
'III-1 §âÄæ¦ì¦WºÙ¤@Ó¤@¦a®³¥X
'rs.Fields.Count : Äæ¦ì¼Æ¼Æ
'rs.Fields(n).Name : Äæ¦W
Dim lngColCounter As Long
i = -1
For lngColCounter = 0 To rs.Fields.Count - 1
Range(strStartlocation).Offset(0, lngColCounter) = rs.Fields(lngColCounter).Name
Next lngColCounter
'III-2 §â¼Æ¾Ú®w¸ê®Æ¦Cªí®³¥X
ActiveSheet.Range(strStartlocation).Offset(1, 0).CopyFromRecordset rs
'IV) Ãö±¼/²M²z³s±µ
cn.Close
Set cn = Nothing
Set rs = Nothing
End Sub
½Æ»s¥N½X
«ü©wÄæ¶×¥X.rar
(66.69 KB)
½d¨Ò
¤U¸ü¦¸¼Æ: 0
2018-10-30 19:54
¼Ó¥D¼ö©«
½Ð°ÝVB6¦p¦ó³sµ²·sª©ªºACCESS©O?
VBA¥i¥H´ú¸Õºô¸ô¦³³s½u¶Ü?
½Ð°Ý±±¨îaccess¤ºªº¸ê®Æקï»P§R°£«ç»ò¼g©O
½Ð°ÝEXCELVBAÁÙ¥i¥H³s½uFTP¶Ü
¦¬ÂÃ
¤À¨É
milk30320
µoµu®ø®§
¥[¬°¦n¤Í
milk30320
·í«eÂ÷½u
UID
32653
©«¤l
6
¥DÃD
1
ºëµØ
0
¿n¤À
50
ª÷¿ú
50
ÂI¦W
0
§@·~¨t²Î
WIN10
³nÅ骩¥»
office2007
¾\ŪÅv
20
©Ê§O
¨k
¨Ó¦Û
·s¦Ë
¦b½u®É¶¡
24 ¤p®É
µù¥U®É¶¡
2018-10-15
³Ì«áµn¿ý
2019-2-18
¤¤¾Ç¥Í
©«¤l
6
¥DÃD
1
ºëµØ
0
¿n¤À
50
ÂI¦W
0
§@·~¨t²Î
WIN10
³nÅ骩¥»
office2007
¾\ŪÅv
20
©Ê§O
¨k
¨Ó¦Û
·s¦Ë
µù¥U®É¶¡
2018-10-15
³Ì«áµn¿ý
2019-2-18
2
#
µoªí©ó 2018-10-31 09:01
|
¥u¬Ý¸Ó§@ªÌ
¦^´_
1#
algoji3ji3
¥i¥H¦b¸ê®Æ®w»yªk¨ºÃäselect§Anªº¯S©wÄæ¦ì,¦b¥þ³¡¥á¨ìexcel¤W±!
As you sow, so shall you reap.
TOP
algoji3ji3
µoµu®ø®§
¥[¬°¦n¤Í
algoji3ji3
·í«eÂ÷½u
UID
23594
©«¤l
11
¥DÃD
5
ºëµØ
0
¿n¤À
16
ª÷¿ú
16
ÂI¦W
0
§@·~¨t²Î
win7
³nÅ骩¥»
sp3
¾\ŪÅv
10
¦b½u®É¶¡
24 ¤p®É
µù¥U®É¶¡
2016-2-9
³Ì«áµn¿ý
2020-2-21
¤p¾Ç¥Í
©«¤l
11
¥DÃD
5
ºëµØ
0
¿n¤À
16
ÂI¦W
0
§@·~¨t²Î
win7
³nÅ骩¥»
sp3
¾\ŪÅv
10
µù¥U®É¶¡
2016-2-9
³Ì«áµn¿ý
2020-2-21
3
#
µoªí©ó 2018-11-1 19:49
|
¥u¬Ý¸Ó§@ªÌ
¤j¤j§Ú´«Ó°Ýªk~
¤U±³o¬q«ü¥O¨Æ±N¾ãӼƾڮw©ñ¦b"A1"ªº¦ì¸m
ActiveSheet.Range(strStartlocation).Offset(1, 0).CopyFromRecordset rs
¨º¦pªG§Ún¤À§O±N¼Æ¾Ú®w¤¤ªº¦U"¾ãÄæ"¤À§O©â¥X©ñ¨ì¦U§O«ü©w¦ì¸m©O?
TOP
ÀR«ä¦Û¦b :
¤â¤ß¦V¤U¬O§U¤H¡A¤â¤ß¦V¤W¬O¨D¤H¡F§U¤H§Ö¼Ö¡A¨D¤HµhW¡C
ªð¦^¦Cªí
¤U¤@¥DÃD
¤W¤@¥DÃD
EXCEL±MÄÝ°Q½×°Ï
Excel£¸¯ë°Ï
Excelµ{¦¡°Ï
¶i¶¥À³¥Î±M°Ï
OFFICE ¨t¦C
Word
PowerPoint
Access
Office¤£¤À°Ï
µ{¦¡»y¨¥
VB »P VB.Net
C »P C#
Java »P J#
µ{¦¡³]p¤£¤À°Ï
¸ê®Æ®w
ORACLE
My SQL
MS SQL
ºô¶³]p
ASP »P ASP.NET
PHP
PHP+MySQL ¤Jªù¹ê§@
JavaScript
FLASH / ActionScript
HTM/ HTML/ CSS
ºô¶³]p¤£¤À°Ï
¹q¸£»P§@·~¨t²Î
¹q¸£¦UºØµwÅé°Q½×
¤@¯ë¹q¸£³nÅé°Q½×
½×¾Â¨Æ°È
ºÞ²z¤½§i
§ë¶D¤Ï¬M
·s¤â´ú¸Õ
·R ¡E ¥Í¬¡
¤½¯q§G§iÄæ
¥Í¬¡»P·P°Ê
[¦¬Âæ¹¥DÃD]
[Ãöª`¦¹¥DÃDªº·s¦^´_]
[³q¹L QQ¡BMSN ¤À¨Éµ¹ªB¤Í]
¥Ó½Ð¤Í±¡Ãì±µ
Facebook¯»µ·