Home > Article > Software Tutorial > VB6 tutorial: Export ACCESS database to EXCEL table
VB itself provides automation functions that can read and write EXCEL tables. The method is as follows:
1. Reference the Microsoft Excel type library in the project:
Select the "Reference" column from the "Project" menu; select Microsoft Excel 9.0 Object Library (EXCEL2000), and then select "OK". Indicates that the EXCEL type library should be referenced in the project.
2. Define the EXCEL object during the declaration process of the general object:
Dim xlApp As Excel.Application
Dim xlBook As Excel.WorkBook
Dim xlSheet As Excel.Worksheet
3. Common commands for operating EXCEL tables in the program:
Set xlApp = CreateObject("Excel.Application") 'Create EXCEL object
Set xlBook = xlApp.Workbooks.Open("File name") 'Open an existing EXCEL workbook file
xlApp.Visible = True 'Set the EXCEL object to be visible (or invisible)
Set xlSheet = xlBook.Worksheets("sheet name") 'Set active worksheet
xlSheet.Cells(row, col) =value 'Assign a value to the cell (row, col)
xlSheet.PrintOut 'Print worksheet
xlBook.Close (True) 'Close the workbook
xlApp.Quit 'End EXCEL object
Set xlApp = Nothing 'Release xlApp object
xlBook.RunAutoMacros (xlAutoOpen) 'Run EXCEL startup macro
xlBook.RunAutoMacros (xlAutoClose) 'Run EXCEL close macro
4. When using the above VB commands to operate the EXCEL table, unless the EXCEL object is set to be invisible, the VB program can continue to perform other operations, close EXCEL, and operate EXCEL at the same time. However, when the EXCEL object is closed during the EXCEL operation, the VB program cannot know. If the EXCEL object is used at this time, the VB program will generate an automation error. This results in a situation where the VB program cannot fully control EXCEL, causing VB to be disconnected from EXCEL.
EXCEL is actually a database. Its columns are the column fields of the database table, and the rows are the items of the database table. I have the code inserted through ADO. You can reverse it and replace the insertion with reading.
_ConnectionPtr m_pConnect; //ADO object, the same below
_RecordsetPtr m_pRecordset;
CString sql;
sql = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
sql =strExcelFile;//The absolute path of the EXCEL file and its own file name.
sql =";Extended Properties=Excel 8.0";
m_pConnect.CreateInstance(__uuidof(Connection));
m_pRecordset.CreateInstance(__uuidof(Recordset));
m_pConnect->Open((LPCSTR)sql,""",""",adModeUnknown);
m_pRecordset->Open("select * from [iomstats]", m_pConnect.GetInterfacePtr(),
adOpenDynamic, adLockOptimistic,adCmdText);//[iomstats] is the modified name of [sheet] in the lower left corner of excel.
while(!m_pRecordset->adoEOF)
{
//Modify line
if((LPCSTR)_bstr_t(m_pRecordset->GetCollect("times")) == strTime)
{
//_variant_t
ultoa(iomStats.dwNumOfDiskReads,charbuf,RADIX);
m_pRecordset->PutCollect("dwNumOfDiskReads",_variant_t(charbuf));
}
m_pRecordset->MoveNext();
}
m_pRecordset->Update();
m_pRecordset->Close();
m_pConnect->Close();
Read and write EXCEL table:
1. Reference the Microsoft Excel type library in the project:
Select the "Reference" column from the "Project" menu; select Microsoft Excel 12.0 Object Library (EXCEL2007), and then select "OK". Indicates that the EXCEL type library should be referenced in the project.
2. Define the EXCEL object during the declaration process of the general object:
Dim xlApp As Excel.Application
Dim xlBook As Excel.WorkBook
Dim xlSheet As Excel.Worksheet
3. Common commands for operating EXCEL tables in the program:
Set xlApp = CreateObject("Excel.Application") 'Create EXCEL object
Set xlBook = xlApp.Workbooks.Open("File name") 'Open an existing EXCEL workbook file
Set xlBook = xlApp.Workbooks.Add("File name") 'Create a new EXCEL workbook file
xlApp.Visible = True 'Set the EXCEL object to be visible (or invisible)
Set xlSheet = xlBook.Worksheets("sheet name") 'Set active worksheet
for i=1 to 100
for j=1 to 50
numArr(j,i)=xlSheet.Cells(j, i) '............................. .......
next
next
xlBook.Close (True) 'Close the workbook
xlApp.Quit 'End EXCEL object
Set xlApp = Nothing 'Release xlApp object
Zhang Zhichen
1. First create an Excel Object in VB to access the Excel file. If your Excel is CSV, you don’t need it. Just open it and read it in Txt text mode.
2. Create a Button event and pass the obtained data into the TextBox.
Refer to the following code:
Conditions: I have an Excel file D:\A.xls with 100 words starting from A1 in Sheet1; in VB, there is a TextBox named Text1 in Form1 and a button named Command1. The implementation code is as follows :
Private i As Integer
Private Sub Command1_Click()
Set ExcelApp = CreateObject("Excel.Application")
Set ExcelBook = ExcelApp.Workbooks.Open("D:\A.xlsx")
Set ExcelSheet = ExcelBook.Worksheets("Sheet1")
Text1.Text = ExcelSheet.cells(i, 1)
i = i 1
If i >100 Then i = 1
End Sub
Private Sub Form_Load()
i = 1
End Sub
希望可以帮助到你!
The above is the detailed content of VB6 tutorial: Export ACCESS database to EXCEL table. For more information, please follow other related articles on the PHP Chinese website!