Common vbs Excel operations

Dim oexcel, owb, osheet
Set oexcel = Createobject ("Excel. application ")
Set owb = oexcel. workbooks. Open ("E: \ others \ new phone table .xls ")
Set osheet = owb. Sheets ("sheet1 ")
Msgbox osheet. Range ("B2"). value' # extract cell B2 content
'.....
3. If it is an XP system, you can use the followingCode
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

Common Excel control methods in vbs:
(1) Use the dynamic creation method
First, create an Excel Object and use comobj:
Oexcel = Createobject ("Excel. application ")
1) display the current window:
Oexcel. Visible = true
2) Change the Excel title bar:
Oexcel. Caption = "Application Program Call Microsoft Excel"
3) Add a new workbook:
Oexcel. workbooks. Add
4) open an existing workbook:
Oexcel. workbooks. Open ("C: \ Excel \ demo.xls ")
5) set 2nd worksheets as active worksheets:
Oexcel. worksheets (2). Activate
Or
Oexcel. workssheets ("sheet2"). Activate
6) assign values to cells:
Oexcel. cells (). value = "column 4 in the first row"
7) set the width of the specified column (unit: number of characters). Take the first column as an example:
Oexcel. activesheet. Columns (1). columnswidth = 5
8) set the height of the specified row (unit: lbs) (1 lbs = 0.035 cm). Take the second behavior as an example:
Oexcel. activesheet. Rows (2). rowheight = 1/0. 035 '1 cm
9) insert a paging character before Row 3:
Oexcel. worksheets (1). Rows (8). pagebreak = 1
10) Delete the paging character before the 8th column:
Oexcel. activesheet. Columns (4). pagebreak = 0
11) Specify the border line width:
Oexcel. activesheet. Range ("B3: D4"). Borders (2). Weight = 3
1-left 2-Right 3-Top 4-bottom 5-oblique (\) 6-oblique (/)
12) Clear the cell formula in the fourth column of the first row:
Oexcel. activesheet. cells (1, 4). clearcontents
13) set the font attribute of the first line:
Oexcel. activesheet. Rows (1). Font. Name = ""
Oexcel. activesheet. Rows (1). Font. Color = clblue
Oexcel. activesheet. Rows (1). Font. Bold = true
Oexcel. activesheet. Rows (1). Font. Underline = true
14) set the page:
A. header:
Oexcel. activesheet. pagesetup. centerheader = "report Demo"
B. footer:
Oexcel. activesheet. pagesetup. centerfooter = "Page & P"
C. The header to the top margin is 2 cm:
Oexcel. activesheet. pagesetup. headermargin = 2/0. 035
D. footer end margin 3 cm:
Oexcel. activesheet. pagesetup. headermargin = 3/0. 035
E. Top margin 2 cm:
Oexcel. activesheet. pagesetup. topmargin = 2/0. 035
F. The bottom margin is 2 cm:
Oexcel. activesheet. pagesetup. bottommargin = 2/0. 035
G. The left side is 2 cm:
Oexcel. activesheet. pagesetup. leftmargin = 2/0. 035
H. Right side: 2 cm:
Oexcel. activesheet. pagesetup. rightmargin = 2/0. 035
I. horizontal center of pages:
Oexcel. activesheet. pagesetup. centerhorizontally = 2/0. 035
J. Center the page vertically:
Oexcel. activesheet. pagesetup. centervertically = 2/0. 035
K. Print the cell network cable:
Oexcel. activesheet. pagesetup. printgridlines = true
15) copy operation:
A. Copy the entire Worksheet:
Oexcel. activesheet. Used. range. Copy
B. Copy the Specified Region:
Oexcel. activesheet. Range ("A1: E2"). Copy
C. paste the file at A1:
Oexcel. activesheet. range. ("A1"). pastespecial
D. paste it 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 a row or column:
A. oexcel. activesheet. Rows (2). Delete
B. oexcel. activesheet. Columns (1). Delete
18) print the preview Worksheet:
Oexcel. activesheet. printpreview
19) print the output Worksheet:
Oexcel. activesheet. Printout
20) Save the worksheet:
If not oexcel. activeworkbook. Saved then
Oexcel. activesheet. printpreview
21) Save the worksheet as follows:
Oexcel. saveas ("C: \ Excel \ demo1.xls ")
22) discard the storage:
Oexcel. activeworkbook. Saved = true
23) Close the workbook:
Oexcel. workbooks. Close
24) Exit Excel:
Oexcel. Quit
(2) Use vbs to control the excle 2D graph
1) Select the first worksheet when the first workbook
Set osheet = oexcel. workbooks (1). worksheets (1)
2) Add a two-dimensional graph.
Achart = osheet. chartobjects. Add (100,100,200,200)
3) Select the shape of a two-dimensional graph.
Achart. Chart. charttype = 4
4) assign a value to a two-dimensional graph
Set series = achart. Chart. seriescollection
Range = "sheet1! R2c3: r3c9"
Series. Add range, true
5) Add the title of a two-dimensional graph.
Achart. Chart. hastitle = true
Achart. Chart. charttitle. characters. Text = "excle 2D graph"
6) change the title Font size of a two-dimensional image.
Achart. Chart. charttitle. Font. size = 18
7) add subscript descriptions to two-dimensional graphs.
Achart. Chart. axes (xlcategory, xlprimary). hastitle = true
Achart. Chart. axes (xlcategory, xlprimary). axistitle. characters. Text = "subscript description"
8) Add a left label for a two-dimensional graph
Achart. Chart. axes (xlvalue, xlprimary). hastitle = true
Achart. Chart. axes (xlvalue, xlprimary). axistitle. characters. Text = "Left label description"
9) add the right label to the two-dimensional graph
Achart. Chart. axes (xlvalue, xlsecondary). hastitle = true
Achart. Chart. axes (xlvalue, xlsecondary). axistitle. characters. Text = ""
10) change the display area of a two-dimensional graph.
Achart. Chart. plotarea. Left = 5
Achart. Chart. plotarea. width = 223
Achart. Chart. plotarea. Height = 108

How to Use vbs to write Excel cell data to txt

Copy code The Code is 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 &" Usage: excel2txt filename.xls or open an Excel file in the 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 and double-click it.

Vbs Excel operations Copy code The Code is as follows: Set objexcel = Createobject ("Excel. application") 'to create an Exel object
Set objworkbook = objexcel. workbooks. Open _
("E: \ doc \ hewl \ domain model .xls") 'open the file
Strtobewrited = "-----------------------------------" & vbcrlf &_
"-- Generated by scriptgenerator ---" & vbcrlf &_
"-----------------------------------" & Vbcrlf
Count = objworkbook. worksheets. Count 'count the number of sheets.
Set my = Createobject ("Excel. Sheet") 'create a sheet object
For each My in objworkbook. worksheets 'traverse Sheet
If my. Name = "directory" or my. Name = "secondhandhouse" then
'Do nothing
Else
'Wscript. echo my. name' get the sheet name
'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 My in objworkbook. worksheets 'traverse 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 a 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 and quit

copy the Code the code is as follows: dim Excel
set Excel = Createobject ("Excel. application ")
'No prompt is displayed, so that the system does not prompt whether to overwrite the original file when saving
excel. displayalerts = false
'It is not displayed when an Excel file is called
excel. visible = false
excel. workbooks. open ("D: \ test. xls ")
'sets sheet1 as an active sheet
excel. workbooks (1 ). activate
'insert row. I have not found this line on msdn. I finally tried 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

no need to find msdn, which can be found in Excel help, see introduction to "programming Info"/"Microsoft Excel Visual Basic Reference"/"properties"/"Q-R"/"rows properties, and "programming information"/"Microsoft Excel Visual Basic Reference"/"method"/"I-L"/"insert method" introduction, you can understand the syntax of this statement.
because in Excel VBA, the "rows", "columns", and "cells" attributes return range objects, their applications can be equivalent to those of range objects.
for example, you can write
cells () in the Excel VBA editor ). value = "ABC"
cells (1, 1 ). wraptext = false
when writing these statements, you should note that after writing "cells ). ", the attribute/method list that should pop up is not displayed, but these statements can run normally.
my method is to record Macros in Excel, modify the statements in the Excel VBA Editor, debug and run the statements correctly, and paste them into the vbs statement for proper modification.