Board logo

¼ÐÃD: °õ¦æ¶¥¬q¿ù»~13 «¬ºA¤£²Å¦X [¥´¦L¥»­¶]

§@ªÌ: joey0415    ®É¶¡: 2010-6-25 19:16     ¼ÐÃD: °õ¦æ¶¥¬q¿ù»~13 «¬ºA¤£²Å¦X

¥»©«³Ì«á¥Ñ joey0415 ©ó 2010-6-25 19:21 ½s¿è

¤p§Ì±N¿ý»sªº¥N½X(¤w¦¨¥\¥i¥Î)

§ï¦¨¤ñ¸û¦n¬Ýªº¥N½X(¥¢±Ñ)

¥i¥HÀ°§Ú¬Ý¬Ý¬O­þùØ¿ù»~¶Ü¡H

¤p§Ì§ïÅÜsql»y¥yªº¼gªk¡A±`±`sql»y¥y«Üµuªº®É­Ô¡A¨âºØ¤èªk³£¦æ¡A¤@¦ýsqlªº»y¥yÅܪø®É¡A´N·|¦³¤U­±ªº¿ù»~
  1. Sub day150_1()
  2. '
  3. ' day150_1 Macro
  4.     Sheets("Sheet1").Select
  5.     Range("A1").Select
  6.     With ActiveSheet.QueryTables.Add(Connection:= _
  7.         "ODBC;DRIVER=SQL Server;SERVER=.;UID=sa;PWD=abcd1234;APP=2007 Microsoft Office system;WSID=CS5111;DATABASE=stock" _
  8.         , Destination:=Range("A1"))
  9.         .CommandText = Array( _
  10.         "select a.*,b.*,c.*,d.*" & Chr(13) & "" & Chr(10) & " from dbo.Stock a" & Chr(13) & "" & Chr(10) & "left join dbo.CreditExchange b on a.sdate=b.sdate and a.stockid=b.stockid" & Chr(13) & "" & Chr(10) & " left join dbo.NLDL c on a.sdate=c.sdate and a.stockid=c.stockid " & Chr(13) & "" & Chr(10) & " left join dbo." _
  11.         , _
  12.         "CompTrade d on a.sdate=d.sdate and a.stockid=d.stockid" & Chr(13) & "" & Chr(10) & " where   a.stockid>1000 and a.stockid<2000  and a.sdate>='2009-11-09'  order by a.stockid  ,a.sdate" & Chr(13) & "" & Chr(10) & "" _
  13.         )
  14.         .Name = "day150_1"
  15.         .FieldNames = True
  16.         .RowNumbers = False
  17.         .FillAdjacentFormulas = False
  18.         .PreserveFormatting = True
  19.         .RefreshOnFileOpen = False
  20.         .BackgroundQuery = True
  21.         .RefreshStyle = xlInsertDeleteCells
  22.         .SavePassword = True
  23.         .SaveData = True
  24.         .AdjustColumnWidth = True
  25.         .RefreshPeriod = 0
  26.         .PreserveColumnInfo = True
  27.         .Refresh BackgroundQuery:=False
  28.     End With
  29. End Sub
½Æ»s¥N½X
¿ù»~¥N½X¡G


´N¬O³o¥y¦³°ÝÃD
  .CommandText = Array(ssql)
  1. Sub day150_11()
  2. '
  3. ' day150_1 Macro
  4. Dim ssql As String
  5. ssql = ""
  6. ssql = ssql & "select a.*,b.*,c.*,d.* from dbo.Stock a" & Chr(13)
  7. ssql = ssql & "left join dbo.CreditExchange b on a.sdate=b.sdate and a.stockid=b.stockid" & Chr(13)
  8. ssql = ssql & "left join dbo.NLDL c on a.sdate=c.sdate and a.stockid=c.stockid" & Chr(13)
  9. ssql = ssql & "left join dbo.CompTrade d on a.sdate=d.sdate and a.stockid=d.stockid" & Chr(13)
  10. ssql = ssql & "where  a.stockid>1000 and a.stockid<2000 and a.sdate>='2009-11-09'" & Chr(13)
  11. ssql = ssql & "order by a.stockid ,a.sdate" & Chr(13)


  12.     Sheets("Sheet1").Select
  13.     Range("A1").Select
  14.     With ActiveSheet.QueryTables.Add(Connection:= _
  15.         "ODBC;DRIVER=SQL Server;SERVER=.;UID=sa;PWD=abcd1234;APP=2007 Microsoft Office system;WSID=CS5111;DATABASE=stock" _
  16.         , Destination:=Range("A1"))
  17.     .CommandText = Array(ssql)
  18.         .Name = "day150_1"
  19.         .FieldNames = True
  20.         .RowNumbers = False
  21.         .FillAdjacentFormulas = False
  22.         .PreserveFormatting = True
  23.         .RefreshOnFileOpen = False
  24.         .BackgroundQuery = True
  25.         .RefreshStyle = xlInsertDeleteCells
  26.         .SavePassword = True
  27.         .SaveData = True
  28.         .AdjustColumnWidth = True
  29.         .RefreshPeriod = 0
  30.         .PreserveColumnInfo = True
  31.         .Refresh BackgroundQuery:=False
  32.     End With
  33. End Sub
½Æ»s¥N½X

§@ªÌ: Min    ®É¶¡: 2010-6-27 21:46

±zªºsql¦r¦ê¥Î¿ù¤F! ¤Ö¤F&

¿ù»~
ssql = ssql "select a.*,b.*,c.*,d.* from dbo.Stock a" & Chr(13)
¥¿½T
ssql = ssql & "select a.*,b.*,c.*,d.* from dbo.Stock a" & Chr(13)

¥þ³¡³£¦³¤Ö³á~
§@ªÌ: joey0415    ®É¶¡: 2010-6-28 17:49

±zªºsql¦r¦ê¥Î¿ù¤F! ¤Ö¤F&

¿ù»~
ssql = ssql "select a.*,b.*,c.*,d.* from dbo.Stock a" & Chr(13)
¥¿ ...
Min µoªí©ó 2010-6-27 21:46



¤p§Ì¶K»yªk®É¡A¨ä¹ê¬O¦³&¡A¦ý¬O³Ì«á¨Ã¨S¦³¥X²{¡A½Ðmin¤j¤jÀ°§Ú¬Ý¤@¤U¡A¶]¥X¨ÓªºÏ¿ªG¦p¤U¹Ï


    [attach]1503[/attach]
§@ªÌ: Min    ®É¶¡: 2010-6-29 06:51

¤p§Ì¶K»yªk®É¡A¨ä¹ê¬O¦³&¡A¦ý¬O³Ì«á¨Ã¨S¦³¥X²{¡A½Ðmin¤j¤jÀ°§Ú¬Ý¤@¤U¡A¶]¥X¨ÓªºÏ¿ªG¦p¤U¹Ï
joey0415 µoªí©ó 2010/6/28 05:49 PM


³Ò¾r±NÀÉ®×PO¤W¨Ó~
§@ªÌ: joey0415    ®É¶¡: 2010-6-29 09:57

¦^´_ 4# Min


³o­ÓÀÉ®×¥»¨Ó«Ü¤j¡A§Ú§R¤F«Ü¦hÄæ¡A¥u«O¯d«e­±´XÄæ»Pªø«×¡A½ÐÀ°§Ú¬Ý¬Ý

ÁÂÁª©¥D


    [attach]1507[/attach]
§@ªÌ: Min    ®É¶¡: 2010-6-29 11:24

ssql = ""
ssql = ssql & vbCrLf & "select a.*,b.*,c.*,d.*"
ssql = ssql & vbCrLf & "from dbo.Stock a"
ssql = ssql & vbCrLf & "left join dbo.CreditExchange b on a.sdate=b.sdate and a.stockid=b.stockid"
ssql = ssql & vbCrLf & "left join dbo.NLDL c on a.sdate=c.sdate and a.stockid=c.stockid"
ssql = ssql & vbCrLf & "left join dbo.CompTrade d on a.sdate=d.sdate and a.stockid=d.stockid"
ssql = ssql & vbCrLf & "where a.stockid>1000 and a.sdate>='2010-06-09'  "
ssql = ssql & vbCrLf & "order by a.stockid,a.sdate"

§ï¥Î³o¼Ëªº¤è¦¡ ¬Ý°_¨Ó¤ñ¸û¾ã»ô³á...
­è­è¤p§Ì¬Ý¤F¦Ñ¥b¤Ñ¡Aµo²{.... ·|¤£·|¬O±zqueryªºvalue¤Ó¤j ©Ò¥Hcommandtext©ñ¤£¤U...
§@ªÌ: joey0415    ®É¶¡: 2010-6-29 14:03

¦^´_ 6# Min


    ·|¤£·|¬O±zqueryªºvalue¤Ó¤j ©Ò¥Hcommandtext©ñ¤£¤U


¤p§Ì¤]ı±o¬O¦p¦¹¡A¦ý¬O¤p§Ì¤£·|§ï¡A½Ð¶{¤jÀ°¦£

ÁÂÁÂ
§@ªÌ: Min    ®É¶¡: 2010-6-29 22:19

¦^´_  Min


    ·|¤£·|¬O±zqueryªºvalue¤Ó¤j ©Ò¥Hcommandtext©ñ¤£¤U


¤p§Ì¤]ı±o¬O¦p¦¹¡A¦ý¬O¤p§Ì ...
joey0415 µoªí©ó 2010/6/29 02:03 PM



¤£¦n·N«ä §Ú¤]¨S¥Î¹L±z³oºØ¤è¦¡¦s¨úDataBase
¦ý±z¥i¥H°Ñ¦ÒADOªº¤è¦¡ http://forum.twbts.com/viewthread.php?tid=712&extra=
§@ªÌ: joey0415    ®É¶¡: 2010-6-30 00:32

¦^´_ 8# Min


½Ð°Ýª©¥D¡A§A¥Îªº¬Omysql¡A¤p§Ì¬O«üsql server

¤p§Ìªº¥D¾÷¡Gtest
±b¸¹¡Gabc
±K½X¡G123
¸ê®Æ®w¡Gstock


°²³]¤p§Ì¬d¸ß¬O¬Y¸ê®Æªí¦p¡Gdbo.stockname

¤p§Ìªº»y¥y¬O¡Gselect * from dbo.stockname

½Ð°Ý«ç»ò­×§ï©O¡H

ÁÂÁª©¥DÀ°¤p§Ì¤@¨B¨B°£¿ù¡A½Ð°Ý¤@¤U¦pªG¥Îª©¥Dªº¼gªk¡A¨ºsql»y¥yªºªøµu´N¤£·|­­¨î¤F¶Ü¡H
  1. Sub ADO_QueryTable()
  2.     Dim Cnnl As New ADODB.Connection
  3.     Dim Rst As New ADODB.Recordset
  4.     Dim SQL As String
  5.     Dim lCol As Long
  6.     Dim lRow As Long
  7.    
  8.     Cnnl.Open "Driver={MySQL ODBC 3.51 Driver};Server=TEST;Port=3306;Database=TEST;CharSet=big5;Uid=TEST;Pwd=TEST;Option=16387"  '³s±µSystem

  9.     SQL = "SELECT COL0,COL1,COL2,COL3,COL4,COL5 FROM TABLE"
  10.     Rst.Open SQL, Cnnl, adOpenKeyset, adLockOptimistic
  11.     Do Until Rst.EOF
  12.         For lCol = 0 To Rst.Fields.Count - 1
  13.             Cells(lRow, lCol + 1).Value = Rst(lCol)
  14.         Next
  15.         lRow = lRow + 1
  16.         Rst.MoveNext
  17.     Loop
  18.    
  19.     Set Cnnl = Nothing
  20.     Set Rst = Nothing
  21. End Sub
½Æ»s¥N½X

§@ªÌ: Min    ®É¶¡: 2010-6-30 21:03

¦^´_  Min


½Ð°Ýª©¥D¡A§A¥Îªº¬Omysql¡A¤p§Ì¬O«üsql server

¤p§Ìªº¥D¾÷¡Gtest
±b¸¹¡Gabc
±K½X¡G12 ...
joey0415 µoªí©ó 2010/6/30 12:32 AM


1.
sql server connection½d¨Ò¦p¤U
Cnnl.Open "Provider=MSIDXS.1; Data Source=path to file Indexing Service"

2.
SQL ½Ð±z¬Ý²Ä¤Q¦æ~

3.
¦Ü©óSQLªºªøµu...
¤£À´±zªº·N«ä!

¦pªG¬O«ü queroªº¸ê®Æ ¨º¶ëÃzexcel¨S°ÝÃD.. ¦ý¶ëÃz¦]¸Ó¨S¦³·N¸q... ©Ò¥H¦]¸Ó¬O¥u¯à©ñ65535*255!
¨º¦pªG±z«üªº¬OsqlÅÜ¼Æ ªøµu°ÝÃD.. ¨º¥i¥H¬d¤@¤Ustring¤W­­~




Åwªï¥úÁ{ ³Â»¶®a±Ú°Q½×ª©ª© (http://forum.twbts.com/)