(2014-04-04 15:13:44)
標(biāo)簽:
股票分類(lèi):
W~VBSdim oExcel,oWb,oSheet
Set oExcel= CreateObject("Excel.Application")
Set oWb = oExcel.Workbooks.Open("E:\其他\新裝電話(huà)表.xls")
Set oSheet = oWb.Sheets("Sheet1")
MsgBox oSheet.Range("B2").Value '#提取單元格B2內(nèi)容
'.....
3、如果是XP系統(tǒng),可以使用如下代碼
Dim objFileDlg
Set objFileDlg = CreateObject("UserAccounts.CommonDialog")
objFileDlg.Filter = "Excel File (*.xls) |*.xls"
If objFileDlg.ShowOpen Then
msgbox "您選擇的文件是:" & objFileDlg.FileName & vbCrLf
End If
VBS控制Excel的一些常見(jiàn)方法:
(一) 使用動(dòng)態(tài)創(chuàng)建的方法
首先創(chuàng)建 Excel 對(duì)象,使用ComObj:
oExcel = CreateObject( "Excel.Application" )
1) 顯示當(dāng)前窗口:
oExcel.Visible = True
2) 更改 Excel 標(biāo)題欄:
oExcel.Caption = "應(yīng)用程序調(diào)用 Microsoft Excel"
3) 添加新工作簿:
oExcel.WorkBooks.Add
4) 打開(kāi)已存在的工作簿:
oExcel.WorkBooks.Open( "C:\Excel\Demo.xls" )
5) 設(shè)置第2個(gè)工作表為活動(dòng)工作表:
oExcel.WorkSheets(2).Activate
或
oExcel.WorksSheets( "Sheet2" ).Activate
6) 給單元格賦值:
oExcel.Cells(1,4).Value = "第一行第四列"
7) 設(shè)置指定列的寬度(單位:字符個(gè)數(shù)),以第一列為例:
oExcel.ActiveSheet.Columns(1).ColumnsWidth = 5
8) 設(shè)置指定行的高度(單位:磅)(1磅=0.035厘米),以第二行為例:
oExcel.ActiveSheet.Rows(2).RowHeight = 1/0.035 ' 1厘米
9) 在第8行之前插入分頁(yè)符:
oExcel.WorkSheets(1).Rows(8).PageBreak = 1
10) 在第8列之前刪除分頁(yè)符:
oExcel.ActiveSheet.Columns(4).PageBreak = 0
11) 指定邊框線寬度:
oExcel.ActiveSheet.Range( "B3:D4" ).Borders(2).Weight = 3
1-左 2-右 3-頂 4-底 5-斜( \ ) 6-斜( / )
12) 清除第一行第四列單元格公式:
oExcel.ActiveSheet.Cells(1,4).ClearContents
13) 設(shè)置第一行字體屬性:
oExcel.ActiveSheet.Rows(1).Font.Name = "隸書(shū)"
oExcel.ActiveSheet.Rows(1).Font.Color = clBlue
oExcel.ActiveSheet.Rows(1).Font.Bold = True
oExcel.ActiveSheet.Rows(1).Font.UnderLine = True
14) 進(jìn)行頁(yè)面設(shè)置:
a.頁(yè)眉:
oExcel.ActiveSheet.PageSetup.CenterHeader = "報(bào)表演示"
b.頁(yè)腳:
oExcel.ActiveSheet.PageSetup.CenterFooter = "第&P頁(yè)"
c.頁(yè)眉到頂端邊距2cm:
oExcel.ActiveSheet.PageSetup.HeaderMargin = 2/0.035
d.頁(yè)腳到底端邊距3cm:
oExcel.ActiveSheet.PageSetup.HeaderMargin = 3/0.035
e.頂邊距2cm:
oExcel.ActiveSheet.PageSetup.TopMargin = 2/0.035
f.底邊距2cm:
oExcel.ActiveSheet.PageSetup.BottomMargin = 2/0.035
g.左邊距2cm:
oExcel.ActiveSheet.PageSetup.LeftMargin = 2/0.035
h.右邊距2cm:
oExcel.ActiveSheet.PageSetup.RightMargin = 2/0.035
i.頁(yè)面水平居中:
oExcel.ActiveSheet.PageSetup.CenterHorizontally = 2/0.035
j.頁(yè)面垂直居中:
oExcel.ActiveSheet.PageSetup.CenterVertically = 2/0.035
k.打印單元格網(wǎng)線:
oExcel.ActiveSheet.PageSetup.PrintGridLines = True
15) 拷貝操作:
a.拷貝整個(gè)工作表:
oExcel.ActiveSheet.Used.Range.Copy
b.拷貝指定區(qū)域:
oExcel.ActiveSheet.Range( "A1:E2" ).Copy
c.從A1位置開(kāi)始粘貼:
oExcel.ActiveSheet.Range.( "A1" ).PasteSpecial
d.從文件尾部開(kāi)始粘貼:
oExcel.ActiveSheet.Range.PasteSpecial
16) 插入一行或一列:
a. oExcel.ActiveSheet.Rows(2).Insert
b. oExcel.ActiveSheet.Columns(1).Insert
17) 刪除一行或一列:
a. oExcel.ActiveSheet.Rows(2).Delete
b. oExcel.ActiveSheet.Columns(1).Delete
18) 打印預(yù)覽工作表:
oExcel.ActiveSheet.PrintPreview
19) 打印輸出工作表:
oExcel.ActiveSheet.PrintOut
20) 工作表保存:
if not oExcel.ActiveWorkBook.Saved then
oExcel.ActiveSheet.PrintPreview
21) 工作表另存為:
oExcel.SaveAs( "C:\Excel\Demo1.xls" )
22) 放棄存盤(pán):
oExcel.ActiveWorkBook.Saved = True
23) 關(guān)閉工作簿:
oExcel.WorkBooks.Close
24) 退出 Excel:
oExcel.Quit
(二) 使用VBS 控制Excle二維圖
1)選擇當(dāng)?shù)谝粋€(gè)工作薄第一個(gè)工作表
set oSheet=oExcel.Workbooks(1).Worksheets(1)
2)增加一個(gè)二維圖
achart=oSheet.chartobjects.add(100,100,200,200)
3)選擇二維圖的形態(tài)
achart.chart.charttype=4
4)給二維圖賦值
set series=achart.chart.seriescollection
range="sheet1!r2c3:r3c9"
series.add range,true
5)加上二維圖的標(biāo)題
achart.Chart.HasTitle=True
achart.Chart.ChartTitle.Characters.Text=" Excle二維圖"
6)改變二維圖的標(biāo)題字體大小
achart.Chart.ChartTitle.Font.size=18
7)給二維圖加下標(biāo)說(shuō)明
achart.Chart.Axes(xlCategory, xlPrimary).HasTitle = True
achart.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "下標(biāo)說(shuō)明"
8)給二維圖加左標(biāo)說(shuō)明
achart.Chart.Axes(xlValue, xlPrimary).HasTitle = True
achart.Chart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "左標(biāo)說(shuō)明"
9)給二維圖加右標(biāo)說(shuō)明
achart.Chart.Axes(xlValue, xlSecondary).HasTitle = True
achart.Chart.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "右標(biāo)說(shuō)明"
10)改變二維圖的顯示區(qū)大小
achart.Chart.PlotArea.Left = 5
achart.Chart.PlotArea.Width = 223
achart.Chart.PlotArea.Height = 108
如何用vbs把excel的單元格數(shù)據(jù)寫(xiě)到txt
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用于將Excel文件的每個(gè)Sheet保存為一個(gè)文本文件。" & vbCr & vbLf & vbCr & vbLf & "用法: Excel2Txt filename.xls 或在對(duì)話(huà)框中打開(kāi)Excel文件。"
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
VBS操作Excel
Set objExcel = CreateObject("Excel.Application") '建一個(gè)exel對(duì)象
Set objWorkbook = objExcel.Workbooks.Open _
("E:\DOC\Hewl\領(lǐng)域模型.xls") '打開(kāi)文件
strToBeWrited = "-----------------------------------" & vbcrlf & _
"-- Generated by ScriptGenerator ---" & vbcrlf & _
"-----------------------------------" & vbcrlf & vbcrlf
Count = objWorkbook.WorkSheets.Count '取sheet數(shù)量
Set my = CreateObject("Excel.Sheet") '新建sheet對(duì)象
For Each my In objWorkbook.WorkSheets '遍歷sheet
If my.Name = "目錄" or my.Name = "SecondHandHouse" Then
'do nothing
Else
'Wscript.Echo my.Name '獲得sheet名字
'Wscript.Echo my.Rows.Count
'strToBeWrited = strToBeWrited & "create table " & my.Name & vbcrlf
strToBeWrited = strToBeWrited & "" & vbcrlf
strToBeWrited = strToBeWrited & "" & 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 '遍歷sheet
If my.Name = "目錄" 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
'寫(xiě)文件
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 '結(jié)束退出
Dim Excel
Set Excel = CreateObject("Excel.Application")
'不顯示提示信息,這樣保存的時(shí)候就不會(huì)提示是否要覆蓋原文件
Excel.DisplayAlerts=FALSE
'調(diào)用EXCEL文件的時(shí)候不顯示
Excel.visible=FALSE
Excel.workbooks.open("D:\test.XLS")
'將sheet1設(shè)置為活動(dòng)sheet
Excel.workbooks(1).activate
'插入行,這條我找MSDN都沒(méi)找到,最后亂試試出來(lái)的
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
獲取本地的行數(shù)和列數(shù)
Set excel=CreateObject("Excel.Application")
set openexcel=excel.Workbooks.Open("這里輸入EXCEL路徑")
rowcount =openexcel.ActiveSheet.UsedRange.Rows.Count
Columnscount =openexcel.ActiveSheet.UsedRange.Columns.count
print " 行數(shù):"&rowcount&" 列數(shù):"&Columnscount
openexcel.Close
excel.Quit
set openexcel=nothing
Set excel=nothing
'若想要獲取Excel中指定sheet的行數(shù)和列數(shù),函數(shù)如下:
Public function getrowandcol(filepath,sheetname)
'函數(shù)作用:獲取EXCEL指定sheet的行數(shù)和列數(shù)
'參數(shù)說(shuō)明
'filepath:EXCEL所在路徑
'sheetname:需要統(tǒng)計(jì)行數(shù)和列數(shù)的SHEET名稱(chēng)
'用法示例:getrowandcol"D:/qqq.xlsx","sheet1"
Set excel=CreateObject("Excel.Application")
set openexcel=excel.Workbooks.Open(filepath)
openexcel.WorkSheets(sheetname).Activate
rowcount =openexcel.ActiveSheet.UsedRange.Rows.Count
Columnscount =openexcel.ActiveSheet.UsedRange.Columns.count
If openexcel.WorkSheets(sheetname).Cells(1,1 )="" and rowcount=1 and Columnscount=1 then
print sheetname&"中沒(méi)有數(shù)據(jù)"
else
print sheetname&"的行數(shù)為:"&rowcount&" 列數(shù):"&Columnscount
end if
openexcel.Close
excel.Quit
set openexcel=nothing
Set excel=nothing
End Function
'說(shuō)明,若sheet中沒(méi)有數(shù)據(jù),則默認(rèn)為1行1列,可以寫(xiě)個(gè)判斷語(yǔ)句,避免沒(méi)有數(shù)據(jù)時(shí)行數(shù)和列數(shù)顯示為1。見(jiàn)函數(shù)中紅色代碼