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

[µo°Ý] ¦p¦ó¦bSQL»yªk¤¤¤Þ¥ÎREPLACE ¨ú¥NÂù¤Þ¸¹©O¡H

[µo°Ý] ¦p¦ó¦bSQL»yªk¤¤¤Þ¥ÎREPLACE ¨ú¥NÂù¤Þ¸¹©O¡H

¦U¦ì¤j¤j¦n¡G

   ¤p§Ì¦³¤@°}¦CÂà´«ªº°ÝÃD¡A
¤@ª½¦Ê«ä¤£¸Ñ¡C¯S½Ð敎¦U¦ì¤j¤j¡C
¤p§Ì¬O·Qª½±µ§Q¥ÎTransposeÂà¸m¤è¦¡¨Ó逹¦¨
°}¦CªºÂà´«¡C¦ý¦]¸ê®Æ®w¤º³¡¥÷¦r¦ê·|¦bÂù¤Þ¸¹¤º¡C
¦p¦¹¡A¼vÅT¨ìTRANSPOSE°}¦CªºÂà´«¡C
½Ð°Ý¦bSQL»yªk¤¤¦p¦ó¤Þ¥ÎREPLACE±N
Âù¤Þ¸¹§ó§ï¬°³æ¤Þ¸¹©O¡H

»yªk¦p¤U¡G
Sub AA()   
    Dim myCon     As New ADODB.Connection
    Dim myRst     As New ADODB.Recordset
    Dim myCnc     As String
    Dim myCmd     As String
    Dim myFileName As String
    Dim s1%, s2%
    Dim mPath$
    Dim mData, mData1
    Dim mSht As Worksheet   

    mPath = "D:\TEMP"   
    myFileName = "TEMP-A.xls"   '´ú¸ÕÀÉ
    myCnc = "Driver={Microsoft Excel Driver (*.xls)};" & _
    "DBQ=" & mPath & "\" & myFileName & ";"
   
    myCmd = "SELECT ½s¸¹,¤ÀÃþ,¦W¦r,°ê¤å,¼Æ¾Ç,¦X­p FROM [F_Data01$]"  '¦p¦ó¦b¦¹»yªk¤º¤Þ¥ÎREPLACE¨Ó¨ú¥N½s¸¹¡B¤ÀÃþ¡B¦W¦r¤ºªºÂù¤Þ¸¹©O¡H
   
    myCon.Open "Provider=MSDASQL;" & myCnc

    myRst.Open Source:=myCmd, ActiveConnection:=myCon
   
    mData = myRst.GetRows  'AÄæ¦]³¡¥÷¦r¦ê¦³Âù¤Þ¸¹¦]¦ÓµLªk¨ú¥X¸ê®Æ
   
    s1 = UBound(mData, 1)
    s2 = UBound(mData, 2)
   
    Set mSht = Worksheets(1)
   
    mData1 = Application.Transpose(mData)  'µLªkÂà¸m °õ¦æ¶¥¬q¿ù»~13 ¡A«¬ºA¤£²Å¦X¡C
    With mSht
        .Range("a1").Resize(s2, s1) = mData1 'µLªkÂà¸m °õ¦æ¶¥¬q¿ù»~13 ¡A«¬ºA¤£²Å¦X¡C
   
    End With
    myRst.Close
    myCon.Close
    Set myRst = Nothing
    Set myCon = Nothing
End Sub

³Æµù¡GÂù¤Þ¸¹¦b¦r¦ê¤º¨Ã«D¦³¤@©w³W«h

·P®¦¦U¦ì¤j¤j¡I

TEMP-A.rar (4.48 KB)

¦^´_ 1# dechiuan999
§Ú¤£½T©w¹ï SQL »yªk¨Ó»¡¬O§_¥i¦æ,
¤£¹L¹ï©ó ExcelVba ¤¤ªº¦r¦ê¨Ó»¡ "  ¬O¥i¥H¥Î chr(34) ¨Ó¥N´Àªº.
¨Ò¦p :
­n³]©w Test$ ªº¤º®e¬° abc"CD"se ®É¥i¥Î
Test = "abc" & chr(34) & "CD" & chr(34) & "se"
¨Ó«ü©w

TOP

ÁÂÁ§Aªº¦^ÂСC
¤p§Ì¦]¸ê®Æ®w¬°access¸ê®Æ®w¡A
µLªkª½±µ¦bexcel¸ê®Æªí¤º¥ý¦æ§ïÅÜ¡C
©Ò¥H§Æ±æ¥i¦bsql»yªk¤¤¹ïaccess¸ê®Æ®w¤º¯à±N¦¹Âù¤Þ¸¹
Åܧ󬰳æ¤Þ¸¹¡C¦p¦¹¥iÁקK¼vÅT¨ì°}¦CªºÂà´«
¡C¦pªGµLªk¬ð¯}¦¹°ÝÃD®É¡F
«h¤S­n¥h¶·§ï­ì©lµ{¦¡¤F¡C

·P®¦¤j¤j¡I

TOP

¥»©«³Ì«á¥Ñ kimbal ©ó 2011-6-23 20:28 ½s¿è

ACCESSªº¸Ü¥i¥H¸Õ¸Õ³o¼Ë,«e8­ÓÂù¤Þ¸¹, «á4­Ó
SELECT
replace(½s¸¹,"""""""","""") ,
¤ÀÃþ,¦W¦r,°ê¤å,¼Æ¾Ç,¦X­p FROM [F_Data01$]"
À´±oµo°Ý,µª®×´N·|¦b¨ä¤¤

¤µ¤éの¤@¬íは  ©ú¤éにない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com

TOP

¥»©«³Ì«á¥Ñ dechiuan999 ©ó 2011-6-24 07:04 ½s¿è

ÁÂÁª©¥D¤j¤j¡C
¤p§Ì¨Ìª©¥D¤j¤jªº«ü¥Ü¡A
´ú¸Õµ²ªG¤´¦³Error¥X²{¡C
¨ä¤º®e¦p¤U¡G
°õ¦æ¶¥¬q¿ù»~'-2147217900 (80040e14)'
¹Bºâ¦¡¤¤¥¼©w¸qªº'replace'¨ç¼Æ

¥t¤p§Ì¬ðµM·Q¨ìª½±µ¦baccess¸ê®Æ®w
¥ý±N­û¤u¸ê®Æªíªº©m¦W¥[Âù¤Þ¸¹¤§«á
§Q¥Î¬d¸ß¤Îreplace¨ú®øÂù¤Þ¸¹´N¥i
¨ú±o¥¿½T¸ê®Æ¡C¨ä»yªk¦p¤U¡G

SELECT ­û¤u.°O¿ý½s¸¹, ­û¤u.­û¤u½s¸¹, ­û¤u.³¡ªù, ­û¤u.¾ºÙ, Replace(­û¤u.©m¦W,"""","") AS Expr1, ­û¤u.©Ê§O
FROM ­û¤u;
¦]¦¹ªí¥Ü¤Þ¥Î replace ¬O¥i¦æªº

·P®¦ª©¥D¤j¤j¡I

TOP

        ÀR«ä¦Û¦b : ¸Ü¦h¤£¦p¸Ü¤Ö¡A¸Ü¤Ö¤£¦p¸Ü¦n¡C
ªð¦^¦Cªí ¤W¤@¥DÃD