VB6 tutorial: Export ACCESS database to EXCEL table

How to use VB6 to export ACCESS database into 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.

How to use VC to read EXCEL table ⑴ Judge Yingsu

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();

How to read excel data in vb and store it in an array

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

How to read excel line by line using VB

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!