VBS operations Excel Common methods _vbs

Dim Oexcel,owb,osheet
Set oexcel= CreateObject ("Excel.Application")
Set OWB = OExcel.Workbooks.Open ("E:\ other \ New installed phone table. xls")
Set osheet = owb.sheets ("Sheet1")
MsgBox Osheet.range ("B2"). Value ' #提取单元格B2内容
'.....
3, if it is an XP system, you can use the following code
Dim objFileDlg
Set objFileDlg = CreateObject ("UserAccounts.CommonDialog")
Objfiledlg.filter = "Excel File (*.xls) |*.xls"
If Objfiledlg.showopen Then
MsgBox "The file you selected is:" & Objfiledlg.filename & VbCrLf
End If

VBS controls some of the common methods of Excel:
(i) using a dynamically created method
First create an Excel object, using Comobj:
oexcel = CreateObject ("Excel.Application")
1 Displays the current window:
oExcel.Visible = True
2) Change the Excel title bar:
Oexcel.caption = "Application calls Microsoft Excel"
3 Add a new workbook:
OExcel.WorkBooks.Add
4 Open a workbook that already exists:
OExcel.WorkBooks.Open ("C:\Excel\Demo.xls")
5) Set the 2nd worksheet as the active worksheet:
Oexcel.worksheets (2). Activate
Or
Oexcel.workssheets ("Sheet2"). Activate
6) Assigning values to cells:
Oexcel.cells (1,4). Value = "First row, fourth column"
7 Sets the width of the specified column in: Number of characters, in the first column example:
OExcel.ActiveSheet.Columns (1). Columnswidth = 5
8 Sets the height of the specified row (in points) (1 lb = 0.035 cm), with the second behavior:
OExcel.ActiveSheet.Rows (2). RowHeight = 1/0.035 ' 1 cm
9 Insert a page break before line 8th:
Oexcel.worksheets (1). Rows (8). PageBreak = 1
10 Delete the page break before column 8th:
OExcel.ActiveSheet.Columns (4). PageBreak = 0
11 Specify the width of the border line:
OExcel.ActiveSheet.Range ("B3:d4"). Borders (2). Weight = 3
1-Left 2-right 3-top 4-bottom 5-oblique (\) 6-oblique (/)
12 clears the first row of column fourth cell formulas:
OExcel.ActiveSheet.Cells (1,4). ClearContents
13 set the first line of font properties:
OExcel.ActiveSheet.Rows (1). Font.Name = "Official script"
OExcel.ActiveSheet.Rows (1). Font.Color = Clblue
OExcel.ActiveSheet.Rows (1). Font.Bold = True
OExcel.ActiveSheet.Rows (1). Font.underline = True
14) for Page setup:
A. Header:
OExcel.ActiveSheet.PageSetup.CenterHeader = "Report Demo"
B. Footer:
OExcel.ActiveSheet.PageSetup.CenterFooter = "Page &p"
C. Header to top margin 2cm:
OExcel.ActiveSheet.PageSetup.HeaderMargin = 2/0.035
D. Footer End margin 3cm:
OExcel.ActiveSheet.PageSetup.HeaderMargin = 3/0.035
E. Top margin 2cm:
OExcel.ActiveSheet.PageSetup.TopMargin = 2/0.035
F. Bottom margin 2cm:
OExcel.ActiveSheet.PageSetup.BottomMargin = 2/0.035
G. Left margin 2cm:
OExcel.ActiveSheet.PageSetup.LeftMargin = 2/0.035
H. Right margin 2cm:
OExcel.ActiveSheet.PageSetup.RightMargin = 2/0.035
I. Page Horizontal Center:
oExcel.ActiveSheet.PageSetup.CenterHorizontally = 2/0.035
J. Page Center vertically:
oExcel.ActiveSheet.PageSetup.CenterVertically = 2/0.035
K. Print Cell network cable:
OExcel.ActiveSheet.PageSetup.PrintGridLines = True
15 Copy operation:
A. Copy the entire worksheet:
OExcel.ActiveSheet.Used.Range.Copy
B. Copy designated Area:
OExcel.ActiveSheet.Range ("A1:e2"). Copy
C. Start pasting from A1 position:
OExcel.ActiveSheet.Range. ("A1"). PasteSpecial
D. Start pasting from the end of the file:
OExcel.ActiveSheet.Range.PasteSpecial
16) Insert a row or column:
A. OExcel.ActiveSheet.Rows (2). Insert
B. OExcel.ActiveSheet.Columns (1). Insert
17) Delete one row or column:
A. OExcel.ActiveSheet.Rows (2). Delete
B. OExcel.ActiveSheet.Columns (1). Delete
18 Print Preview Worksheet:
OExcel.ActiveSheet.PrintPreview
19 Print output Worksheet:
OExcel.ActiveSheet.PrintOut
20) Worksheet Save:
If not oExcel.ActiveWorkBook.Saved then
OExcel.ActiveSheet.PrintPreview
21) Save the worksheet as:
Oexcel.saveas ("C:\Excel\Demo1.xls")
22) Discard the disk:
oExcel.ActiveWorkBook.Saved = True
23 Close the workbook:
OExcel.WorkBooks.Close
24 Exit Excel:
oExcel.Quit
(ii) using the VBS control EXCLE two-dimensional diagram
1 Select the first worksheet when the first work sheet
Set Osheet=oexcel.workbooks (1). Worksheets (1)
2) Add a two-dimensional graph
Achart=osheet.chartobjects.add (100,100,200,200)
3 Choose the form of two-dimensional graphs
Achart.chart.charttype=4
4 assigning values to two-dimensional graphs
Set Series=achart.chart.seriescollection
Range= "SHEET1!R2C3:R3C9"
Series.add Range,true
5) plus the title of the two-dimensional graph
Achart. Chart.hastitle=true
Achart. chart.charttitle.characters.text= "Excle Two-dimensional map"
6) Change the title font size of the two-dimensional graph
Achart. Chart.charttitle.font.size=18
7) to the two-dimensional map and subscript description
Achart. Chart.axes (Xlcategory, xlprimary). HasTitle = True
Achart. Chart.axes (Xlcategory, xlprimary). AxisTitle.Characters.Text = "Subscript description"
8) to the two-dimensional diagram Gazo description
Achart. Chart.axes (Xlvalue, xlprimary). HasTitle = True
Achart. Chart.axes (Xlvalue, xlprimary). AxisTitle.Characters.Text = "Left label description"
9) to the two-dimensional map plus the right standard description
Achart. Chart.axes (Xlvalue, xlsecondary). HasTitle = True
Achart. Chart.axes (Xlvalue, xlsecondary). AxisTitle.Characters.Text = "Right Mark description"
10) Change the size of the display area of the two-dimensional graph
Achart. Chart.PlotArea.Left = 5
Achart. Chart.PlotArea.Width = 223
Achart. Chart.PlotArea.Height = 108

How to use the VBS to write Excel cell data to txt

Copy Code code as follows:

If WScript.Arguments.Count > 0 Then Filename = wscript.arguments (0)
Set A = CreateObject ("Excel.Application")
If Filename = "" Then
Filename = A.getopenfilename ("Excel Files (*.xls), *.xls")
If VarType (Filename) = Vbboolean Then
MsgBox Excel2txt is used to save each sheet of an Excel file as a text file. "& vbcr & vblf & vbcr & vblf &" Usage: excel2txt filename.xls or open an Excel file in a dialog box. "
Wscript.Quit
End If
End If
Set w = a.workbooks.open (Filename)
n = replace (replace (W.name, ". xls", ""), ". XLS "," ")
A.displayalerts = False
For each s in W.sheets
S.saveas W.path & "\" & N & "_" & S.name & ". txt", 20
Next
A.quit

Save the above code as Excel2txt.vbs double click to execute the line

VBS operation Excel

Copy Code code as follows:

Set Objexcel = CreateObject ("Excel.Application") ' Builds a Exel object
Set Objworkbook = ObjExcel.Workbooks.Open _
("E:\DOC\Hewl\ domain model. xls") ' Open File
strtobewrited = "-----------------------------------" & vbCrLf & _
"--generated by Scriptgenerator---" & vbCrLf & _
"-----------------------------------" & vbCrLf & vbCrLf
Count = ObjWorkbook.WorkSheets.Count ' Take sheet quantity
Set my = CreateObject ("Excel.Sheet") ' New Sheet object
For each i in objworkbook.worksheets ' traversal sheet
If My. Name = "Directory" or My. Name = "Secondhandhouse" Then
' Do nothing
Else
' WScript.Echo My. Name ' Get sheet names
' WScript.Echo My. Rows.Count
' strtobewrited = strtobewrited & ' CREATE TABLE ' & My. Name & vbCrLf
strtobewrited = strtobewrited & "/*==============================================================*/" & vbCrLf
strtobewrited = strtobewrited & "/* Table:" & My. Name & "* *" & vbCrLf
strtobewrited = strtobewrited & "/*==============================================================*/" & vbCrLf
strtobewrited = strtobewrited & "CREATE TABLE" & My. Name & "(" & vbCrLf
RowNum = 3
Do Until My. Cells (rownum,1). Value = ""
' WScript.Echo ' sAMAccountName: ' & My. Cells (RowNum, 2). Value
strtobewrited = strtobewrited & "" & My. Cells (rownum,2). Value & "" & My. Cells (rownum,3). Value & "NOT NULL"
If not My. Cells (rownum,9). Value = "" Then
strtobewrited = strtobewrited & "Default" & My. Cells (rownum,9). Value
End If
strtobewrited = strtobewrited & "," & vbCrLf
RowNum = rownum + 1
Loop
strtobewrited = strtobewrited & "Constraint Pk_" & My. Name & "PRIMARY key (ID)" & vbCrLf
strtobewrited = strtobewrited & ")" & vbCrLf
End If
strtobewrited = strtobewrited & vbCrLf
Next
For each i in objworkbook.worksheets ' traversal sheet
If My. Name = "Directory" or My. Name = "Secondhandhouse" Then
' Do nothing
Else

strtobewrited = strtobewrited & "Constraint Pk_" & My. Name & "PRIMARY key (ID)" & vbCrLf
strtobewrited = strtobewrited & ")" & vbCrLf
End If
strtobewrited = strtobewrited & vbCrLf
Next
' Write file
Set FS =createobject ("Scripting.FileSystemObject")
Set F = Fs.opentextfile ("E:\DOC\Hewl\dbscript.sql", 2, True)
' WScript.Echo strtobewrited
F.write strtobewrited
F.close
Set F = Nothing
Set fs = Nothing
Objexcel.quit ' End exit

Copy Code code as follows:

Dim Excel
Set Excel = CreateObject ("Excel.Application")
' Do not display prompt information, so you won't be prompted to overwrite the original file when you save it
Excel.displayalerts=false
' Do not show when calling Excel file
Excel.visible=false
Excel.workbooks.open ("D:\test.") XLS ")
' Set Sheet1 to active sheet
Excel.Workbooks (1). Activate
' Insert row, I didn't find MSDN, I finally tried to try it out.
Excel.ActiveSheet.rows (1). Insert
Excel.ActiveSheet.Cells (1,1). Value = Date
Excel.ActiveSheet.Cells (1,2). Value = "Row1"
Excel.ActiveSheet.Cells (1,3). Value = "Comment1"
Excel.ActiveSheet.rows (2). Insert
Excel.ActiveSheet.Cells (2,1). Value = Date
Excel.ActiveSheet.Cells (2,4). Value = "Row2"
Excel.ActiveSheet.Cells (2,7). Value = "Comment2"
Excel.save
Excel.quit
Set Excel = Nothing
Excel.ActiveSheet.rows (1). Insert

Don't look for MSDN, you can find it in Excel help, look at the introduction to programming Information/Microsoft Excel Visual Basic Reference/Properties/Q-r/Rows properties, and programming information/Microsoft Excel The syntax of this statement can be understood with the introduction of Visual Basic Reference/method/I-l/Insert method.
Because in Excel VBA, the Rows, Columns, and Cells properties return a Range object, their application can be equivalent to the Range object's application.
For example: You can write this in Excel's VBA editor
Cells (1,1). value= "ABC"
Cells (1,1). Wraptext=false
When you write these statements, you should notice that you are finished writing "cells (1,1)." After that, there is no pop-up list of attributes/methods that should pop up, but these statements do work correctly.
My approach is to record a macro in Excel, and then modify the statement in Excel's VBA editor, debug it, and then paste it into the VBS statement for appropriate modification.