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

This article mainly introduces "some common methods of VBS controlling Excel". In daily operation, I believe many people have doubts about some common methods of VBS controlling Excel. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "some common methods of VBS controlling Excel". Next, please follow the editor to study!

Dim oExcel,oWb,oSheet

Set oExcel= CreateObject ("Excel.Application")

Set oWb = oExcel.Workbooks.Open ("E:\ other\ newly installed phone list .xls")

Set oSheet = oWb.Sheets ("Sheet1")

MsgBox oSheet.Range ("B2"). Value'# extract the contents of cell B2

'.

3. If it is a 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

Some common ways for VBS to control Excel:

(I) using the method of dynamic creation

First create an Excel object, using ComObj:

OExcel = CreateObject ("Excel.Application")

1) display 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 an existing workbook:

OExcel.WorkBooks.Open ("C:\ Excel\ Demo.xls")

5) set the second worksheet as the active worksheet:

OExcel.WorkSheets (2). Activate

Or

OExcel.WorksSheets ("Sheet2"). Activate

6) assign values to cells:

OExcel.Cells (1B4). Value = "first row and fourth column"

7) set the width of the specified column (in characters), taking the first column as an example:

OExcel.ActiveSheet.Columns (1). ColumnsWidth = 5

8) sets the height of the specified row (in pounds) (1 lb = 0.035 cm), as a second example:

OExcel.ActiveSheet.Rows (2). RowHeight = 1ram 0.035 '1cm

9) insert a page break before line 8:

OExcel.WorkSheets (1) .Rows (8). PageBreak = 1

10) remove the page break before column 8:

OExcel.ActiveSheet.Columns (4). PageBreak = 0

11) specify the width of the border:

OExcel.ActiveSheet.Range ("B3:D4"). Borders (2). Weight = 3

1-left 2-right 3-Top 4-bottom 5-inclined (\) 6-oblique (/)

12) clear the cell formula in the first row and the fourth column:

OExcel.ActiveSheet.Cells (1, 4). ClearContents

13) set the first line 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) Page setup:

a. Header:

OExcel.ActiveSheet.PageSetup.CenterHeader = "report presentation"

b. Footer:

OExcel.ActiveSheet.PageSetup.CenterFooter = "Page & P"

c. Header to top margin 2cm:

OExcel.ActiveSheet.PageSetup.HeaderMargin = 2max 0.035

d. 3cm at the end of the footer:

OExcel.ActiveSheet.PageSetup.HeaderMargin = 3max 0.035

e. Top margin 2cm:

OExcel.ActiveSheet.PageSetup.TopMargin = 2max 0.035

f. Bottom margin 2cm:

OExcel.ActiveSheet.PageSetup.BottomMargin = 2max 0.035

g. Left margin 2cm:

OExcel.ActiveSheet.PageSetup.LeftMargin = 2max 0.035

h. Right margin 2cm:

OExcel.ActiveSheet.PageSetup.RightMargin = 2max 0.035

i. The page level is centered:

OExcel.ActiveSheet.PageSetup.CenterHorizontally = 2max 0.035

j. The page is vertically centered:

OExcel.ActiveSheet.PageSetup.CenterVertically = 2max 0.035

k. Print cell gridlines:

OExcel.ActiveSheet.PageSetup.PrintGridLines = True

15) copy operation:

a. Copy the entire worksheet:

OExcel.ActiveSheet.Used.Range.Copy

b. Copy the specified area:

OExcel.ActiveSheet.Range ("A1:E2"). Copy

c. Start pasting from A1 location:

OExcel.ActiveSheet.Range. ("A1"). PasteSpecial

d. Paste 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 preview worksheet:

OExcel.ActiveSheet.PrintPreview

19) printout worksheet:

OExcel.ActiveSheet.PrintOut

20) Save the worksheet:

If not oExcel.ActiveWorkBook.Saved then

OExcel.ActiveSheet.PrintPreview

21) Save the worksheet as:

OExcel.SaveAs ("C:\ Excel\ Demo1.xls")

22) abandon the save:

OExcel.ActiveWorkBook.Saved = True

23) close the workbook:

OExcel.WorkBooks.Close

24) exit Excel:

OExcel.Quit

(2) use VBS to control Excle two-dimensional 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 (100100200200)

3) choose the shape of the two-dimensional graph

Achart.chart.charttype=4

4) assign values to two-dimensional graphs

Set series=achart.chart.seriescollection

Range= "sheet1percent r2c3purr3c9"

Series.add range,true

5) add the title of the two-dimensional graph

Achart.Chart.HasTitle=True

Achart.Chart.ChartTitle.Characters.Text= "Excle 2D Diagram"

6) change the font size of the title of the 2D graph

Achart.Chart.ChartTitle.Font.size=18

7) subscript the two-dimensional diagram

Achart.Chart.Axes (xlCategory, xlPrimary). HasTitle = True

Achart.Chart.Axes (xlCategory, xlPrimary). AxisTitle.Characters.Text = "subscript description"

8) add a left label to the two-dimensional diagram

Achart.Chart.Axes (xlValue, xlPrimary). HasTitle = True

Achart.Chart.Axes (xlValue, xlPrimary). AxisTitle.Characters.Text = "left description"

9) add a right label to the two-dimensional diagram

Achart.Chart.Axes (xlValue, xlSecondary). HasTitle = True

Achart.Chart.Axes (xlValue, xlSecondary). AxisTitle.Characters.Text = "right description"

10) change the size of the display area of the 2D graph

Achart.Chart.PlotArea.Left = 5

Achart.Chart.PlotArea.Width = 223

Achart.Chart.PlotArea.Height = 108

How to write the cell data of excel to txt with vbs

The copy 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

The 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 the 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

Just save the above code as Excel2Txt.vbs and double-click to execute it.

VBS operation Excel

The copy code is as follows:

Set objExcel = CreateObject ("Excel.Application") '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 & vbcrlf

Count = objWorkbook.WorkSheets.Count 'take the number of sheet

Set my = CreateObject ("Excel.Sheet") 'New sheet object

For Each my In objWorkbook.WorkSheets' traverses sheet

If my.Name = "directory" or my.Name = "SecondHandHouse" Then

'do nothing

Else

'Wscript.Echo my.Name 'gets 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' traverses 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 exit

The copy code is as follows:

Dim Excel

Set Excel = CreateObject ("Excel.Application")

'do not display the prompt so that you will not be prompted to overwrite the original file when you save it

Excel.DisplayAlerts=FALSE

'do not display when calling the EXCEL file

Excel.visible=FALSE

Excel.workbooks.open ("D:\ test.XLS")

'set sheet1 as the active sheet

Excel.workbooks (1). Activate

Insert a row. I couldn't find this one when I looked for MSDN. I finally tried it randomly.

Excel.ActiveSheet.rows (1). Insert

Excel.ActiveSheet.Cells (1). Value = Date

Excel.ActiveSheet.Cells (1). Value = "row1"

Excel.ActiveSheet.Cells (1, 3). Value = "comment1"

Excel.ActiveSheet.rows (2). Insert

Excel.ActiveSheet.Cells (2). Value = Date

Excel.ActiveSheet.Cells (2, 4). Value = "row2"

Excel.ActiveSheet.Cells (2 and 7). Value = "comment2"

Excel.save

Excel.quit

Set Excel = Nothing

Excel.ActiveSheet.rows (1). Insert

Without looking for MSDN, you can find it in EXCEL help. Read the introduction of "programming information" / "Microsoft Excel Visual Basic reference" / "attribute" / "QmurR" / "Rows attribute", and the introduction of "programming information" / "Microsoft Excel Visual Basic reference" / "method" / "Imurl" / "Insert method" to understand the syntax of this statement.

Because in the VBA of EXCEL, the "Rows", "Columns", and "Cells" properties return Range objects, the application of them can be equivalent to the application of Range objects.

For example, you can write this in the VBA editor of EXCEL

Cells (1) .value = "abc"

Cells (1). Wraptext=false

When writing these sentences, you should notice that when you finish writing "cells (1 Magol 1)." After that, the list of properties / methods that should pop up does not pop up, but these statements do work properly.

My method is to record the macro in EXCEL, then modify the statement in EXCEL's VBA editor, debug and run correctly, and then paste it into the VBS statement to make appropriate changes.

At this point, the study of "some common ways for VBS to control Excel" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.