VBS operations Excel Common methods _vbs

Source: Internet
Author: User
Tags list of attributes first row

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.