Private child browse _Click() '[ button? ] Click the event.
Common dialog box 1.filter = "Excel file (. XLS)|*。 XLS| all files | *. *' Set the general dialog box to filter XLS files or all files.
CommonDialog 1。 Displays the open dialog box.
File _ name = public dialog box 1. Filename' gets the file name selected by the user.
If file_name = ""then Exit Sub' judges whether the file name is valid or not, and exits the process if it is invalid.
List 1。 Clear' Empty list box.
Show xlExcel as Excel. "Application" declares an instance of an Excel application.
Adjust xlBook to Excel. "Workbook" declares an instance of an Excel workbook.
Adjust xlSheet to Excel format. "Worksheet" declares an instance of an Excel form.
Set xlExcel = createobject ("excel。 Application ")' generates an instance of an Excel application.
Set xlbook = xlexcel.workbooks.open (file _ name)' open xls file.
XlExcel。 "Visible = False" is processed in the background, and the Excel window is not displayed.
For I =1toxlbook.sheets.count' Traverse all worksheets.
Set xlSheet = xlBook. Worksheet (1)
XlSheet。 Activate the i-th worksheet.
Add the worksheet name to the list box.
DoEvents lets the system respond to other messages.
then
XlBook。 Close closes the workbook.
XlExcel。 Exit to exit Excel.
End joint
Private sublist 1_Click ()' List box click event
If Text 1.text = "",exit sub' If there is no content in the text box of Text 1, exit the process.
If the file name is = "",exit the process. If the file name selected by the user is invalid, exit the process.
Dimcon as newadodb。 "connection”adodb connection object declaration
"Dimconstraint as string" declares a string variable.
Con.CursorLocation = adUseClient' sets the location of the cursor engine. Please refer to MSDN for details.
Constr = "driver = SQL Server Server = (local); Uid = sapwd = 56776606 database = knife; Distribution: use sa account number 56776606 as password to connect to the knife database of local SQLServer.
Open the connection.
Con. Execute "select * into" & text1. Text & from OPENROWSET('MICROSOFT. JET.OLEDB.4.0',' Excel 5.0HDR = Yes; Database = "& file name &," & ampList 1. Text &$) "
Access, Oracle and other databases can use the SELECT ... to ... from ... statement, and can directly save the query results into the newly created table without creating the table in advance.
Please don't laugh. It seems that SQLServer can't do this. Of course, I didn't experiment. I've never done this before. This is the worksheet selected by List 1 as the data table, and the worksheet made by Text 1 as the target worksheet.
Close the connection.
Set Con = Nothing' releases variables.
MsgBox "Import succeeded!" Prompt that the data was imported successfully.
Beeping made a damn sound.
End joint
Private sub-import Excel_Click ()' Click event
Do nothing.
End joint