最近用BCB寫了一個由Excel中執行匯入文字檔, 我先在Excel中執行了  資料->取得外部資料->匯入文字檔案 選擇好檔案後,選擇以分隔符號的方式匯入,分隔的符號是 Tab 和 逗號 ,然後欄位的資料格式為"文字","文字","一般"*6 為我有八個欄位.之後按下完成... 在這之前要先錄製巨集以取得 VB 的程式....如下

 
With ActiveSheet.QueryTables.Add(Connection:= _
  "TEXT;D:\Temp\BCB\Excel\ASIC-IO\test.txt", Destination:=Range("A1"))
        .Name = "test_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = xlWindows
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(2, 2, 1, 1, 1, 1, 1, 1)
        .Refresh BackgroundQuery:=False
    End With   

 將以上的程式改為 BCB 語法,如下:  
   Variant iTmp[8]={2,2,1,1,1,1,1,1};
   Variant MSExcel,MSWorkbooks,MSWorkbook;
   Variant MSSheets,MSSheet,vTmp1,vTmp2,vTmp3,vRange;
   Variant vArray(iTmp,7);       MSExcel=CreateOleObject("Excel.Application");
   MSExcel.OlePropertySet("Visible",true);
   MSWorkbooks=MSExcel.OlePropertyGet("Workbooks");
   MSWorkbook=MSWorkbooks.OleFunction("Add");
   MSSheets=MSWorkbook.OlePropertyGet("Worksheets");
   MSSheet=MSSheets.OlePropertyGet("Item",1);
   vTmp1=MSWorkbook.OlePropertyGet("ActiveSheet").OlePropertyGet("QueryTables");
   vRange=MSSheet.OlePropertyGet("Cells",1,1);
   vTmp2=vTmp1.OleFunction("Add","TEXT;D:\\Temp\\BCB\\Excel\\ASIC-IO\\test.txt",vRange);
   vTmp2.OlePropertySet("Name","test");
   vTmp2.OlePropertySet("FieldNames",true);
   vTmp2.OlePropertySet("RowNumbers",false);
   vTmp2.OlePropertySet("FillAdjacentFormulas", false);
   vTmp2.OlePropertySet("PreserveFormatting",true);
   vTmp2.OlePropertySet("RefreshOnFileOpen" , false);
   vTmp2.OlePropertySet("RefreshStyle",xlInsertDeleteCells);
   vTmp2.OlePropertySet("SavePassword",false);
   vTmp2.OlePropertySet("SaveData",true);
   vTmp2.OlePropertySet("AdjustColumnWidth",true);
   vTmp2.OlePropertySet("RefreshPeriod",0);
   vTmp2.OlePropertySet("TextFilePromptOnRefresh",false);
   vTmp2.OlePropertySet("TextFilePlatform",xlWindows);
   vTmp2.OlePropertySet("TextFileStartRow",1);
   vTmp2.OlePropertySet("TextFileParseType",xlDelimited);
   vTmp2.OlePropertySet("TextFileTextQualifier",xlTextQualifierDoubleQuote);
   vTmp2.OlePropertySet("TextFileConsecutiveDelimiter",false);
   vTmp2.OlePropertySet("TextFileTabDelimiter",true);
   vTmp2.OlePropertySet("TextFileSemicolonDelimiter",false);
   vTmp2.OlePropertySet("TextFileCommaDelimiter",true);
   vTmp2.OlePropertySet("TextFileSpaceDelimiter",false);
   vTmp2.OlePropertySet("TextFileColumnDataTypes",vArray);
   vTmp3=vTmp2.OleFunction("Refresh",false);
   MSExcel = Unassigned;      在 *.h 中 include
#include 
#include 

以上是我改的BCB code,若有錯誤的地方,請不吝指教, 謝謝 發表人 - firewing 於 2003/08/07 10:58:48 發表人 - firewing 於 2003/08/07 11:04:33 發表人 - firewing 於 2003/08/07 11:33:53

看到這篇好文忍不住玩了一下,但編譯不過,

像 xlTextQualifierDoubleQuote 這個好像沒有定義.

另請問是否可以改為匯入 excel 檔? 用 excel 匯入 excel, 原因是被匯入的 excel 檔是很舊的版本,每次開啟時總出現工作表名稱錯誤,然用 C 開啟就是當給我看,害我連轉的機會都沒有,然而用匯入的就沒有錯誤訊息,樓主可以改寫一下嗎?

已經找到 Excel 匯入舊版 Excel  的方法了(依樓主的程式改寫一些)

xlCmdTable 的代碼是 3,
可參考

http://topic.csdn.net/t/20030522/13/1818670.html

Excel 常用代碼表

//---------------------------------------------------------------------------
void __fastcall TForm1::SpeedButton2Click(TObject *Sender){
Variant MSExcel,MSWorkbooks,MSWorkbook;
Variant MSSheets,MSSheet,vTmp1,vTmp2,vTmp3,vRange;
MSExcel=CreateOleObject("Excel.Application");
MSExcel.OlePropertySet("Visible",true);
MSWorkbooks=MSExcel.OlePropertyGet("Workbooks");
MSWorkbook=MSWorkbooks.OleFunction("Add");
MSSheets=MSWorkbook.OlePropertyGet("Worksheets");
MSSheet=MSSheets.OlePropertyGet("Item",1);
vTmp1=MSWorkbook.OlePropertyGet("ActiveSheet").OlePropertyGet("QueryTables");
vRange=MSSheet.OlePropertyGet("Cells",1,1);
//只要四行就行,缺一不可
vTmp2=vTmp1.OleFunction("Add", "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\1.xls;Jet OLEDB:Engine Type=34;", vRange);
vTmp2.OlePropertySet("CommandType", 3);
vTmp2.OlePropertySet("CommandText", "\'$\'");
vTmp3 = vTmp2.OleFunction("Refresh", false);


MSExcel = Unassigned;
}


//---------------------------------------------------------------------------

編輯記錄

Load外部的圖檔，貼入Execl。