Shulou(Shulou.com)06/03 Report--

This article mainly shows you "what is the use of excel2access.vbs script", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "what is the use of excel2access.vbs script" this article.

The code is as follows:

'script description

'code by NP

'The outDateFile variable is the name of the database to be written

'The Tables variable is the table name in the Access database

'The ROW variable is the column name in the Access database

The data in Excel is directly for the data to be written, and there is no need for headings such as

'for example: the column to be written to the database is named

'Department | class | name | student number | gender | political outlook | year of birth | ID number | Family address | place of origin graduation school

In the Excel file, you only need to start with the following example

'Department of Urban Construction | 0001 of Urban Construction | Wei XX | 071010100111 | female | League member | 1981 Union11 | 451111111111111111 | Guangxi xxxxxxxxx | xxxx School

'The data and column names should be the right number!

'double-click the script directly for more prompt action

If WScript.Arguments.Count > 0 Then InXlsFile = WScript.Arguments (0)

If InXlsFile = "" Then

InXlsFile = CreateObject ("Excel.Application") .GetOpenFilename ("Excel Files (* .xls), * .xls")

If VarType (Filename) = vbBoolean Then

Wscript.Echo "Excel2Access." & vbCr & vbLf & vbCr & vbLf & "usage: Excel2Access filename.xls or open the Excel file in a dialog box."

WScript.Quit

End If

End If

OutDateFile= "Excel2Access.mdb" the name of the database to be converted

Tables= Student Information Table 'the name of the table to be created and written

Row= "Department, class, name, student number, gender, political profile, date of birth, ID number, home address, place of origin graduation school" to create and write the list names, separated by commas

Set Fso=CreateObject ("Scripting.FileSystemObject")

If Fso.FileExists (outDateFile) Then

Call Writemdb

Else

Set DB = CreateObject ("ADOX.Catalog")

DB.Create ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & outDateFile)

Set ADOXTable = CreateObject ("ADOX.Table")

ADOXTable.Name = Tables

For Each s In Split (Row, ","-1,1)

ADOXTable.Columns.Append sPhone202 'string

Next

DB.Tables.Append ADOXTable

Set DB=Nothing

Call Writemdb

End If

Set Fso = Nothing

Sub Writemdb ()

Set objExcel = CreateObject ("Excel.Application")

Set objWorkbook = objExcel.Workbooks.Open (InXlsFile)

Set Conn=CreateObject ("ADODB.Connection")

ConnStr= "DBQ=" & outDateFile& "; Driver= {Microsoft Access Driver (* .mdb)};"

Conn.Open ConnStr

Rows=objExcel.ActiveSheet.UsedRange.Rows.Count

Columns=objExcel.ActiveSheet.UsedRange.Columns.Count

For I = 1 To rows

Tmp= ""

For j = 1 To Columns

If Len (objExcel.Cells (iMagnej) .value) 0 Then

If jColumns Then

Tmp=tmp& "'" & objExcel.Cells (iMagazine j) .value & "',"

Else

Tmp=tmp& "'" & objExcel.Cells (iMagazine j) .value & "'"

Wscript.Echo "Insert Into" & Tables& "(" & row& ") Values (" & tmp& ")"

Conn.Execute "Insert Into" & Tables& "(" & Row& ") Values (" & tmp& ")

End If

Else

Exit For

End If

Next

Next

ObjWorkbook.Close

ObjExcel.Quit

Conn.Close

Set Conn=Nothing

Set objWorkbook=Nothing

Set objExcel=Nothing

Wscript.Echo "after processing, please check whether the import is normal"

End Sub

The above is all the content of this article "what is the use of excel2access.vbs script?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.