³s±µ¨ä¥L¸ê®Æ®w®É,¦p¦ó¥i¥H¤£¥Î¦A¿é¤J±b±K

VANESSA

¦¹¤å¥óªº¼Æ¦r¬O¦³³s±µ¨ì¨ä¥L¨t²Î,¨C¦¸­n¶i¤J®É³£¥²¶·­«·s¿é¤J±b¸¹¤Î±K½X,¯àª½±µ±N±b¸¹¤Î±K½X¼g¦bexcel¸Ì¶Ü?¶}±Òexcel®É´N¯à¦Û°Ê³s½u¤F¡C

VANESSA

¦^´_ 7# doom834


    ÁÂÁ±z,§Úªº¸ê®Æ¨Ó·½¬OHYPERION¨t²Î(³o¬OORACLE¤½¥qªºÀ³¥Î³nÅ餧¤@)§Ú¤£ª¾¹D³o¬O¥Î¤°»ò¼gªº¡A©Ò¥H§Ú·|¦A¸Õ¸Õ¡AÁÂÁÂ

doom834

§Ñ°O´£¿ô­n³]©w¤Þ¥Î¶µ¥Ø
Microsoft ActiveX Data Object 2.0 Library
°O±o­n¤Ä³á

doom834

´£¿ô§A¤@¤U¡A§Ú³o­Ó³s±µªº¸ê®Æ®w¬OOracleªº¤è¦¡¡A
­Y§A³s±µªº¸ê®Æ®w¬Oaccess©ÎSQL Server¡A¨º¼gªk¥i¯à­n¦³©ÒÅܧó¡A
¨Ò¦p¥Î³s±µaccess«h­n§ï¦¨

³s±µ¸ê®Æ®w®É¡G
' Connect DB
Dim My_con As New ADODB.Connection
Dim My_set As ADODB.Recordset

My_FileName = "aaa.mdb" '½Ð¦Û¦æ¿é¤J§AaccessªºÀɮצWºÙ
My_con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & "\" & My_FileName & ";"
My_SQL = "select * from test_table" 'My_SQL½Ð¦Û¦æ©w¸q§Aªºsql£®¡A¨Ò¦p¡GMy_SQL= "select * from XXXX(¦¹¬°§Aªº¸ê®Æªí¦WºÙ)
Set My_set = My_con.Execute(My_SQL)            
For I = 1 To My_set.Fields.Count
   Cells(1, I).Value = My_set.Fields(I - 1).Name
Next I
            
Do While Not My_set.EOF
   Cells(2, 1).CopyFromRecordset My_set
Loop

VANESSA

¦^´_ 4# doom834


    ­n§l¦¬³o­Óµ{¦¡ªº·N«ä,¹ï§Ú¦Ó¨¥¦³ÂI§xÃø»Ý­n¤@¨Ç®É¶¡,¥ýÁÂÁ±zªºÀ°¦£

doom834

²z½×¤W¬O­°¤l§ï
Const HOST$ = "100.00.000.000"
Const DBNAME$ = "abcde"
Const ORACLE_USER_NAME$ = "fghij"
Const ORACLE_PASSWORD$ = "12345"

³s±µ¸ê®Æ®w®É¡G
            ' Connect DB
            strConOracle = "Driver={Microsoft ODBC for Oracle}; CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
            strConOracle = strConOracle & "(HOST=" & HOST & ")(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=" & DBNAME
            strConOracle = strConOracle & "))); uid=" & ORACLE_USER_NAME & " ;pwd=" & ORACLE_PASSWORD & ";"
            Set oConOracle = CreateObject("ADODB.Connection")
            Set oRsOracle = CreateObject("ADODB.RecordSet")
            oConOracle.Open strConOracle
                    
            Set oRsOracle = oConOracle.Execute(My_SQL) 'My_SQL½Ð¦Û¦æ©w¸q§Aªºsql
            
            For I = 1 To oRsOracle.Fields.Count
            
                Sheets("TEST").Cells(1, I).Value = oRsOracle.Fields(I - 1).Name
            
            Next I
            
            Do While Not oRsOracle.EOF
            
                Sheets("TEST").Cells(2, 1).CopyFromRecordset oRsOracle
            
            Loop

VANESSA

¦^´_ 2# doom834


    ÁÂÁ±z,¦ý¬O§Ú¸Õ¨S¦³¦¨¥\,¦pªG§Ú¬O³s¨ì³o­Ó¸ê®Æ®whttp://100.00.000.000/HFMOfficeProvider/HFMOfficeProvider.aspx.
¸ê®Æ®w¦WºÙ:abcde
±b¸¹:fghij
±K½X:12345
¨º§Ú¸Ó¦p¦ó­×§ï?

doom834

§ÚµLªk¤U¸üªþ¥ó¡A§A¥i¥H¸Õ¸Õ¥H¤Uªº¤èªk

Const HOST$ = "xxx.xxx.xxx.xxx"  ' Server hosting the Oracle db
Const DBNAME$ = "¥D¾÷¦WºÙ"
Const ORACLE_USER_NAME$ = "¨Ï¥ÎªÌ¦WºÙ"
Const ORACLE_PASSWORD$ = "±K½X"

³s±µ®Éªº»yªk
            ' Connect DB
            strConOracle = "Driver={Microsoft ODBC for Oracle}; CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
            strConOracle = strConOracle & "(HOST=" & HOST & ")(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=" & DBNAME
            strConOracle = strConOracle & "))); uid=" & ORACLE_USER_NAME & " ;pwd=" & ORACLE_PASSWORD & ";"
            Set oConOracle = CreateObject("ADODB.Connection")
            Set oRsOracle = CreateObject("ADODB.RecordSet")
            oConOracle.Open strConOracle