What is the use of excel2access.vbs script
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!
