如何寫程式將 Excel 資料匯至 Oracle
如何將 Excel 頁籤中的資料匯至 Oracle 表格中
Dim db As Object
Dim strCn As String, strDsn As String, strUid As String, strPwd As String
Dim strTableName As String, strExcelName As String, strSheetName As String
Dim strOracleDriver As String
strDsn = "Oracle服務名稱"
strUid = "Oracle使用者"
strPwd = "Oracle密碼"
' Oracle ODBC 驅動程式名稱
strOracleDriver = "Oracle ODBC Driver"
' PS: Oracle 的 ODBC Driver , 因版本不同而ODBC Driver 名稱也不同
' 9i 以後版本 , Driver 名稱不同, 9i 為 Oracle in OraHome9? , ? 為 0 , 1 , 2 ..
' 10g 版本 Driver 為 Oracle in OraDb10g_home1
' Oracle ODBC 連線字串
strCn = "ODBC;DRIVER={" & strOracleDriver & "};" & _
"SERVER=" & strDsn & ";UID=" & strUid & ";PWD=" & strPwd & ";DBQ=" & strDsn
' 建立 DAO Database 物件 , 並開啟 (連線)資料庫
Set db = CreateObject("DAO.DBEngine.36").WorkSpaces(0).OpenDatabase("", 0, 0, strCn)
strTableName = "Oracle 資料表名稱"
strExcelName = "Excel 路徑 + 檔名"
strSheetName = "Excel 中 Sheet 名稱"
' INSERT INTO 語法是將 Excel 中資料匯入 Oracle , 但 Table 必須已經存在 Oracle 中
db.Execute "INSERT INTO " & strTableName & _
" SELECT * FROM [Excel 8.0;Database=" & strExcelName & "].[" & strSheetName & "$] "
' SELECT * INTO 語法是將 Excel 中資料匯入 Oracle , 並會在 Oracle 中建立對應型態之 Table
db.Execute "SELECT * INTO " & strTableName & _
" FROM [Excel 8.0;Database=" & strExcelName & "].[" & strSheetName & "$] "