Board logo

标题: [转贴] VBS对Office软件Word、Excel等的操作实例 [打印本页]

作者: HAT    时间: 2009-9-22 23:55     标题: VBS对Office软件Word、Excel等的操作实例

【Word】

VBS把多个Word文档合并成一个
http://bbs.bathome.net/thread-13081-1-1.html
  1. Add Formatted Text to a Word Document
  2. Demonstration script that displays formatted data in a Microsoft Word document.
  3. Set objWord = CreateObject("Word.Application")
  4. objWord.Visible = True
  5. Set objDoc = objWord.Documents.Add()
  6. Set objSelection = objWord.Selection
  7. objSelection.Font.Name = "Arial"
  8. objSelection.Font.Size = "18"
  9. objSelection.TypeText "Network Adapter Report"
  10. objSelection.TypeParagraph()
  11. objSelection.Font.Size = "14"
  12. objSelection.TypeText "" & Date()
  13. objSelection.TypeParagraph()
  14. Add a Formatted Table to a Word Document
  15. Demonstration script that retrieves service data from a computer and then displays that data in a formatted table in Microsoft Word.
  16. Set objWord = CreateObject("Word.Application")
  17. objWord.Visible = True
  18. Set objDoc = objWord.Documents.Add()
  19. Set objRange = objDoc.Range()
  20. objDoc.Tables.Add objRange,1,3
  21. Set objTable = objDoc.Tables(1)
  22. x=1
  23. strComputer = "."
  24. Set objWMIService = _
  25.     GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
  26. Set colItems = objWMIService.ExecQuery("Select * from Win32_Service")
  27. For Each objItem in colItems
  28.     If x > 1 Then
  29.         objTable.Rows.Add()
  30.     End If
  31.     objTable.Cell(x, 1).Range.Font.Bold = True
  32.     objTable.Cell(x, 1).Range.Text = objItem.Name
  33.     objTable.Cell(x, 2).Range.text = objItem.DisplayName
  34.     objTable.Cell(x, 3).Range.text = objItem.State
  35.     x = x + 1
  36. Next
  37. Apply a Style to a Table in a Word Document
  38. Demonstration script that retrieves service data from a computer, displays that data in a table in Microsoft Word, then formats the data by using a predefined Microsoft Word style
  39. Set objWord = CreateObject("Word.Application")
  40. objWord.Visible = True
  41. Set objDoc = objWord.Documents.Add()
  42. Set objRange = objDoc.Range()
  43. objDoc.Tables.Add objRange,1,3
  44. Set objTable = objDoc.Tables(1)
  45. objTable.Range.Font.Size = 10
  46. objTable.Range.Style = "Table Contemporary"
  47. x=2
  48. objTable.Cell(x, 1).Range.Text = "Service Name"
  49. objTable.Cell(x, 2).Range.text = "Display Name"
  50. objTable.Cell(x, 3).Range.text = "State"
  51. strComputer = "."
  52. Set objWMIService = _
  53.     GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
  54. Set colItems = objWMIService.ExecQuery("Select * from Win32_Service")
  55. For Each objItem in colItems
  56.     If x > 1 Then
  57.         objTable.Rows.Add()
  58.     End If
  59.     objTable.Cell(x, 1).Range.Text = objItem.Name
  60.     objTable.Cell(x, 2).Range.text = objItem.DisplayName
  61.     objTable.Cell(x, 3).Range.text = objItem.State
  62.     x = x + 1
  63. Next
  64. Add a Table to a Word Document
  65. Demonstration script that retrieves service information from a computer and then displays that information in tabular format in Microsoft Word.
  66. Set objWord = CreateObject("Word.Application")
  67. objWord.Visible = True
  68. Set objDoc = objWord.Documents.Add()
  69. Set objRange = objDoc.Range()
  70. objDoc.Tables.Add objRange,1,3
  71. Set objTable = objDoc.Tables(1)
  72. x=1
  73. strComputer = "."
  74. Set objWMIService = _
  75.     GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
  76. Set colItems = objWMIService.ExecQuery("Select * from Win32_Service")
  77. For Each objItem in colItems
  78.     If x > 1 Then
  79.         objTable.Rows.Add()
  80.     End If
  81.     objTable.Cell(x, 1).Range.Text = objItem.Name
  82.     objTable.Cell(x, 2).Range.text = objItem.DisplayName
  83.     objTable.Cell(x, 3).Range.text = objItem.State
  84.     x = x + 1
  85. Next
  86. Append Text to a Word Document
  87. Demonstration script that appends the current date to the existing Microsoft Word document C:\Scripts\Word\Testdoc.doc.
  88. Const END_OF_STORY = 6
  89. Const MOVE_SELECTION = 0
  90. Set objWord = CreateObject("Word.Application")
  91. objWord.Visible = True
  92. Set objDoc = objWord.Documents.Open("c:\scripts\word\testdoc.doc")
  93. Set objSelection = objWord.Selection
  94. objSelection.EndKey END_OF_STORY, MOVE_SELECTION
  95. objSelection.TypeParagraph()
  96. objSelection.TypeParagraph()
  97. objSelection.Font.Size = "14"
  98. objSelection.TypeText "" & Date()
  99. objSelection.TypeParagraph()
  100. objSelection.TypeParagraph()
  101. objSelection.Font.Size = "10"
  102. Create a New Word Document
  103. Demonstration script that creates and displays a new Microsoft Word document.
  104. Set objWord = CreateObject("Word.Application")
  105. objWord.Visible = True
  106. Set objDoc = objWord.Documents.Add()
  107. Create and Save a Word Document
  108. Demonstration script that retrieves network adapter data from a computer, displays that data in a Microsoft Word document, and then saves the document as C:\Scripts\Word\Testdoc.doc.
  109. Set objWord = CreateObject("Word.Application")
  110. objWord.Caption = "Test Caption"
  111. objWord.Visible = True
  112. Set objDoc = objWord.Documents.Add()
  113. Set objSelection = objWord.Selection
  114. objSelection.Font.Name = "Arial"
  115. objSelection.Font.Size = "18"
  116. objSelection.TypeText "Network Adapter Report"
  117. objSelection.TypeParagraph()
  118. objSelection.Font.Size = "14"
  119. objSelection.TypeText "" & Date()
  120. objSelection.TypeParagraph()
  121. objSelection.TypeParagraph()
  122. objSelection.Font.Size = "10"
  123. strComputer = "."
  124. Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
  125. Set colItems = objWMIService.ExecQuery _
  126.     ("Select * from Win32_NetworkAdapterConfiguration")
  127. For Each objItem in colItems
  128.     objSelection.Font.Bold = True
  129.     objSelection.TypeText "ARP Always Source Route: "
  130.     objSelection.Font.Bold = False
  131.     objSelection.TypeText "" & objItem.ArpAlwaysSourceRoute
  132.     objSelection.TypeParagraph()
  133.     objSelection.Font.Bold = True
  134.     objSelection.TypeText "ARP Use EtherSNAP: "
  135.     objSelection.Font.Bold = False
  136.     objSelection.TypeText ""  & objItem.ArpUseEtherSNAP
  137.     objSelection.TypeParagraph()
  138.     objSelection.Font.Bold = True
  139.     objSelection.TypeText "Caption: "
  140.     objSelection.Font.Bold = False
  141.     objSelection.TypeText ""  & objItem.Caption
  142.     objSelection.TypeParagraph()
  143.     objSelection.Font.Bold = True
  144.     objSelection.TypeText "Database Path: "
  145.     objSelection.Font.Bold = False
  146.     objSelection.TypeText ""   & objItem.DatabasePath
  147.     objSelection.TypeParagraph()
  148.     objSelection.Font.Bold = True
  149.     objSelection.TypeText "Dead GW Detection Enabled: "
  150.     objSelection.Font.Bold = False
  151.     objSelection.TypeText ""   & objItem.DeadGWDetectEnabled
  152.     objSelection.TypeParagraph()
  153.     objSelection.Font.Bold = True
  154.     objSelection.TypeText "Default IP Gateway: "
  155.     objSelection.Font.Bold = False
  156.     objSelection.TypeText "" & objItem.DefaultIPGateway
  157.     objSelection.TypeParagraph()
  158.     objSelection.Font.Bold = True
  159.     objSelection.TypeText "Default TOS: "
  160.     objSelection.Font.Bold = False
  161.     objSelection.TypeText ""  & objItem.DefaultTOS
  162.     objSelection.TypeParagraph()
  163.     objSelection.Font.Bold = True
  164.     objSelection.TypeText "Default TTL: "
  165.     objSelection.Font.Bold = False
  166.     objSelection.TypeText ""  & objItem.DefaultTTL
  167.     objSelection.TypeParagraph()
  168.     objSelection.Font.Bold = True
  169.     objSelection.TypeText "Description: "
  170.     objSelection.Font.Bold = True
  171.     objSelection.Font.Bold = False
  172.     objSelection.TypeText ""  & objItem.Description
  173.     objSelection.TypeParagraph()
  174.     objSelection.TypeParagraph()
  175. Next
  176. objDoc.SaveAs("C:\Scripts\Word\testdoc.doc")
  177. objWord.Quit
  178. Display Service Information in a Word Document
  179. Demonstration script that retrieves service information from a computer and then displays that data in a Microsoft Word document.
  180. Set objWord = CreateObject("Word.Application")
  181. objWord.Visible = True
  182. Set objDoc = objWord.Documents.Add()
  183. Set objSelection = objWord.Selection
  184. objSelection.TypeText "Services Report"
  185. objSelection.TypeParagraph()
  186. objSelection.TypeText "" & Now
  187. objSelection.TypeParagraph()
  188. objSelection.TypeParagraph()
  189. strComputer = "."
  190. Set objWMIService = _
  191.     GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
  192. Set colItems = objWMIService.ExecQuery("Select * from Win32_Service")
  193. For Each objItem in colItems
  194.     objSelection.TypeText objItem.DisplayName & " -- " & objItem.State
  195.     objSelection.TypeParagraph()
  196. Next
  197. List Microsoft Word Properties
  198. Demonstration script that lists Microsoft Word configuration settings.
  199. On Error Resume Next
  200. Set objWord = CreateObject("Word.Application")
  201. Wscript.Echo "Active Printer:", objWord.ActivePrinter
  202. For Each objAddIn in objWord.AddIns
  203.     Wscript.Echo "AddIn: ", objAddIn
  204. Next
  205. Wscript.Echo "Application:", objWord.Application
  206. Wscript.Echo "Assistant:", objWord.Assistant
  207. For Each objCaption in objWord.AutoCaptions
  208.     Wscript.Echo "AutoCaptions:", objCaption
  209. Next
  210. Wscript.Echo "Automation Security:", objWord.AutomationSecurity
  211. Wscript.Echo "Background Printing Status:", objWord.BackgroundPrintingStatus
  212. Wscript.Echo "Background Saving Status:", objWord.BackgroundSavingStatus
  213. Wscript.Echo "Browse Extra File Type:", objWord.BrowseExtraFileTypes
  214. Wscript.Echo "Build:", objWord.Build
  215. Wscript.Echo "Caps Lock:", objWord.CapsLock
  216. Wscript.Echo "Caption:", objWord.Caption
  217. For Each objLabel in objWord.CaptionLabels
  218.     Wscript.Echo "Caption Label:", objLabel
  219. Next
  220. Wscript.Echo "Check Language:", objWord.CheckLanguage
  221. For Each objAddIn in objWord.COMAddIns
  222.     Wscript.Echo "COM AddIn:", objAddIn
  223. Next
  224. Wscript.Echo "Creator:", objWord.Creator
  225. For Each objDictionary in objWord.CustomDictionaries
  226.     Wscript.Echo "Custom Dictionary:", objDictionary
  227. Next
  228. Wscript.Echo "Customization Context:", objWord.CustomizationContext
  229. Wscript.Echo "Default Legal Blackline:", objWord.DefaultLegalBlackline
  230. Wscript.Echo "Default Save Format:", objWord.DefaultSaveFormat
  231. Wscript.Echo "Default Table Separator:", objWord.DefaultTableSeparator
  232. For Each objDialog in objWord.Dialogs
  233.     Wscript.Echo "Dialog:", objDialog
  234. Next
  235. Wscript.Echo "Display Alerts:", objWord.DisplayAlerts
  236. Wscript.Echo "Display Recent Files:", objWord.DisplayRecentFiles
  237. Wscript.Echo "Display Screen Tips:", objWord.DisplayScreenTips
  238. Wscript.Echo "Display Scroll Bars:", objWord.DisplayScrollBars
  239. For Each objDocument in objWord.Documents
  240.     Wscript.Echo "Document:", objDocument
  241. Next
  242. Wscript.Echo "Email Template:", objWord.EmailTemplate
  243. Wscript.Echo "Enable Cancel Key:", objWord.EnableCancelKey
  244. Wscript.Echo "Feature Install:", objWord.FeatureInstall
  245. For Each objConverter in objWord.FileConverters
  246.     Wscript.Echo "File Converter:", objConverter
  247. Next
  248. Wscript.Echo "Focus In MailHeader:", objWord.FocusInMailHeader
  249. For Each objFont in objWord.FontNames
  250.     Wscript.Echo "Font Name:", objFont
  251. Next
  252. Wscript.Echo "Height", objWord.Height
  253. For Each objBinding in objWord.KeyBindings
  254.     Wscript.Echo "Key Binding:", objBinding
  255. Next
  256. For Each objFont in objWord.LandscapeFontNames
  257.     Wscript.Echo "Landscape Font Name:", objFont
  258. Next
  259. Wscript.Echo "Language", objWord.Language
  260. For Each objLanguage in objWord.Languages
  261.     Wscript.Echo "Language:", objLanguage
  262. Next
  263. Wscript.Echo "Left", objWord.Left
  264. Wscript.Echo "Mail System:", objWord.MailSystem
  265. Wscript.Echo "MAPI Available:", objWord.MAPIAvailable
  266. Wscript.Echo "Math Coprocessor Available:", objWord.MathCoprocessorAvailable
  267. Wscript.Echo "Mouse Available:", objWord.MouseAvailable
  268. Wscript.Echo "Name:", objWord.Name
  269. Wscript.Echo "Normal Template:", objWord.NormalTemplate
  270. Wscript.Echo "Num Lock:", objWord.NumLock
  271. Wscript.Echo "Parent:", objWord.Parent
  272. Wscript.Echo "Path:", objWord.Path
  273. Wscript.Echo "Path Separator:", objWord.PathSeparator
  274. Wscript.Echo "Print Preview:", objWord.PrintPreview
  275. For Each objFile in objWord.RecentFiles
  276.     Wscript.Echo "Recent File:", objFile
  277. Next
  278. Wscript.Echo "Screen Updating:", objWord.ScreenUpdating
  279. Wscript.Echo "Show Visual Basic Editor:", objWord.ShowVisualBasicEditor
  280. Wscript.Echo "Special Mode:", objWord.SpecialMode
  281. Wscript.Echo "Startup Path:", objWord.StartupPath
  282. For Each objTask in objWord.Tasks
  283.     Wscript.Echo "Task:", objTask
  284. Next
  285. For Each objTemplate in objWord.Templates
  286.     Wscript.Echo "Template:", objTemplate
  287. Next
  288. Wscript.Echo "Top:", objWord.Top
  289. Wscript.Echo "Usable Height:", objWord.UsableHeight
  290. Wscript.Echo "Usable Width:", objWord.UsableWidth
  291. Wscript.Echo "User Address:", objWord.UserAddress
  292. Wscript.Echo "User Control:", objWord.UserControl
  293. Wscript.Echo "User Initials:", objWord.UserInitials
  294. Wscript.Echo "User Name:", objWord.UserName
  295. Wscript.Echo "Version:", objWord.Version
  296. Wscript.Echo "Visible:", objWord.Visible
  297. Wscript.Echo "Width:", objWord.Width
  298. For Each objWindow in objWord.Windows
  299.     Wscript.Echo "Window:", objWindow
  300. Next
  301. Wscript.Echo "Window State:", objWord.WindowState
  302. objWord.Quit
  303. Modify Bookmark Text in a Word Document
  304. Demonstration script that changes the text of two different bookmarks in an existing Microsoft Word document.
  305. Set objWord = CreateObject("Word.Application")
  306. objWord.Caption = "Test Caption"
  307. objWord.Visible = True
  308. Set objDoc = objWord.Documents.Open("c:\scripts\word\bookmarkdoc.doc")
  309. Set objRange = objDoc.Bookmarks("NameBookmark").Range
  310. objRange.Text = "Bob"
  311. Set objRange = objDoc.Bookmarks("AddressBookmark").Range
  312. objRange.Text = "999"
  313. Open and Print a Word Document
  314. Demonstration script that opens and prints and existing Microsoft Word document.
  315. Set objWord = CreateObject("Word.Application")
  316. Set objDoc = objWord.Documents.Open("c:\scripts\inventory.doc")
  317. objDoc.PrintOut()
  318. objWord.Quit
  319. Read a Bookmark in a Word Document
  320. Demonstration script that retrieves the values of two different Microsoft Word bookmarks.
  321. Set objWord = CreateObject("Word.Application")
  322. Set objDoc = objWord.Documents.Open("c:\scripts\word\bookmarkdoc.doc")
  323. Set objRange = objDoc.Bookmarks("NameBookmark").Range
  324. Wscript.Echo objRange.Text
  325. Set objRange = objDoc.Bookmarks("AddressBookmark").Range
  326. Wscript.Echo objRange.Text
  327. objWord.Quit
  328. Use Word to Search for Files
  329. Demonstration script that uses Microsoft Word to locate all the .mp3 files stored on drive C of the local computer.
  330. Set objWord = CreateObject("Word.Application")
  331. Set objDoc = objWord.Documents.Add()
  332. objWord.FileSearch.FileName = "*.mp3"
  333. objWord.FileSearch.LookIn = "C:\"
  334. objWord.FileSearch.SearchSubfolders = True
  335. objWord.FileSearch.Execute
  336. For Each objFile in objWord.FileSearch.FoundFiles
  337.     Wscript.Echo objFile
  338. Next
  339. objWord.Quit
复制代码
转自:http://www.activexperts.com/acti ... ipts/msoffice/word/
作者: HAT    时间: 2009-9-22 23:56

【Excel】
  1. Add Data to a Spreadsheet Cell
  2. Demonstration script that adds the words "Test Value" to cell 1,1 in a new spreadsheet.
  3. Set objExcel = CreateObject("Excel.Application")
  4. objExcel.Visible = True
  5. objExcel.Workbooks.Add
  6. objExcel.Cells(1, 1).Value = "Test value"
  7. Add Formatted Data to a Spreadsheet
  8. Demonstration script that adds the words "test value" to a new spreadsheet, then formats the cell containing the value.
  9. Set objExcel = CreateObject("Excel.Application")
  10. objExcel.Visible = True
  11. objExcel.Workbooks.Add
  12. objExcel.Cells(1, 1).Value = "Test value"
  13. objExcel.Cells(1, 1).Font.Bold = TRUE
  14. objExcel.Cells(1, 1).Font.Size = 24
  15. objExcel.Cells(1, 1).Font.ColorIndex = 3
  16. Create User Accounts Based on Information in a Spreadsheet
  17. Demonstration script that creates new Active Directory user accounts based on information stored in an Excel spreadsheet.
  18. Set objExcel = CreateObject("Excel.Application")
  19. Set objWorkbook = objExcel.Workbooks.Open _
  20.     ("C:\Scripts\New_users.xls")
  21. intRow = 2
  22. Do Until objExcel.Cells(intRow,1).Value = ""
  23.     Set objOU = GetObject("ou=Finance, dc=fabrikam, dc=com")
  24.     Set objUser = objOU.Create _
  25.         ("User", "cn=" & objExcel.Cells(intRow, 1).Value)
  26.     objUser.sAMAccountName = objExcel.Cells(intRow, 2).Value
  27.     objUser.GivenName = objExcel.Cells(intRow, 3).Value
  28.     objUser.SN = objExcel.Cells(intRow, 4).Value
  29.     objUser.AccountDisabled = FALSE
  30.     objUser.SetInfo
  31.     intRow = intRow + 1
  32. Loop
  33. objExcel.Quit
  34. Format a Range of Cells
  35. Demonstration script that adds data to four different cells in a spreadsheet, then uses the Range object to format multiple cells at the same time.
  36. Set objExcel = CreateObject("Excel.Application")
  37. objExcel.Visible = True
  38. objExcel.Workbooks.Add
  39. objExcel.Cells(1, 1).Value = "Name"
  40. objExcel.Cells(1, 1).Font.Bold = TRUE
  41. objExcel.Cells(1, 1).Interior.ColorIndex = 30
  42. objExcel.Cells(1, 1).Font.ColorIndex = 2
  43. objExcel.Cells(2, 1).Value = "Test value 1"
  44. objExcel.Cells(3, 1).Value = "Test value 2"
  45. objExcel.Cells(4, 1).Value = "Tets value 3"
  46. objExcel.Cells(5, 1).Value = "Test value 4"
  47. Set objRange = objExcel.Range("A1","A5")
  48. objRange.Font.Size = 14
  49. Set objRange = objExcel.Range("A2","A5")
  50. objRange.Interior.ColorIndex = 36
  51. Set objRange = objExcel.ActiveCell.EntireColumn
  52. objRange.AutoFit()
  53. List Active Directory Data in a Spreadsheet
  54. Demonstration script that retrieves data from Active Directory and then displays that data in an Excel spreadsheet.
  55. Const ADS_SCOPE_SUBTREE = 2
  56. Set objExcel = CreateObject("Excel.Application")
  57. objExcel.Visible = True
  58. objExcel.Workbooks.Add
  59. objExcel.Cells(1, 1).Value = "Last name"
  60. objExcel.Cells(1, 2).Value = "First name"
  61. objExcel.Cells(1, 3).Value = "Department"
  62. objExcel.Cells(1, 4).Value = "Phone number"
  63. Set objConnection = CreateObject("ADODB.Connection")
  64. Set objCommand =   CreateObject("ADODB.Command")
  65. objConnection.Provider = "ADsDSOObject"
  66. objConnection.Open "Active Directory Provider"
  67. Set objCommand.ActiveConnection = objConnection
  68. objCommand.Properties("Page Size") = 100
  69. objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
  70. objCommand.CommandText = _
  71.     "SELECT givenName, SN, department, telephoneNumber FROM " _
  72.         & "'LDAP://dc=fabrikam,dc=microsoft,dc=com' WHERE " _
  73.             & "objectCategory='user'"
  74. Set objRecordSet = objCommand.Execute
  75. objRecordSet.MoveFirst
  76. x = 2
  77. Do Until objRecordSet.EOF
  78.     objExcel.Cells(x, 1).Value = _
  79.         objRecordSet.Fields("SN").Value
  80.     objExcel.Cells(x, 2).Value = _
  81.         objRecordSet.Fields("givenName").Value
  82.     objExcel.Cells(x, 3).Value = _
  83.         objRecordSet.Fields("department").Value
  84.     objExcel.Cells(x, 4).Value = _
  85.         objRecordSet.Fields("telephoneNumber").Value
  86.     x = x + 1
  87.     objRecordSet.MoveNext
  88. Loop
  89. Set objRange = objExcel.Range("A1")
  90. objRange.Activate
  91. Set objRange = objExcel.ActiveCell.EntireColumn
  92. objRange.Autofit()
  93. Set objRange = objExcel.Range("B1")
  94. objRange.Activate
  95. Set objRange = objExcel.ActiveCell.EntireColumn
  96. objRange.Autofit()
  97. Set objRange = objExcel.Range("C1")
  98. objRange.Activate
  99. Set objRange = objExcel.ActiveCell.EntireColumn
  100. objRange.Autofit()
  101. Set objRange = objExcel.Range("D1")
  102. objRange.Activate
  103. Set objRange = objExcel.ActiveCell.EntireColumn
  104. objRange.Autofit()
  105. Set objRange = objExcel.Range("A1").SpecialCells(11)
  106. Set objRange2 = objExcel.Range("C1")
  107. Set objRange3 = objExcel.Range("A1")
  108. List Excel Color Values
  109. Demonstration script that displays the various colors -- and their related color index -- available when programmatically controlling Microsoft Excel.
  110. Set objExcel = CreateObject("Excel.Application")
  111. objExcel.Visible = True
  112. objExcel.Workbooks.Add
  113. For i = 1 to 56
  114.     objExcel.Cells(i, 1).Value = i
  115.     objExcel.Cells(i, 1).Interior.ColorIndex = i
  116. Next
  117. List Service Data in a Spreadsheet
  118. Demonstration script that retrieves information about each service running on a computer, and then displays that data in an Excel spreadsheet.
  119. Set objExcel = CreateObject("Excel.Application")
  120. objExcel.Visible = True
  121. objExcel.Workbooks.Add
  122. x = 1
  123. strComputer = "."
  124. Set objWMIService = GetObject _
  125.     ("winmgmts:\\" & strComputer & "\root\cimv2")
  126. Set colServices = objWMIService.ExecQuery _
  127.     ("Select * From Win32_Service")
  128. For Each objService in colServices
  129.     objExcel.Cells(x, 1) = objService.Name
  130.     objExcel.Cells(x, 2) = objService.State
  131.     x = x + 1
  132. Next
  133. Open an Excel Spreadsheet
  134. Demonstration script that opens an existing Excel spreadsheet named C:\Scripts\New_users.xls.
  135. Set objExcel = CreateObject("Excel.Application")
  136. Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\New_users.xls")
  137. Read an Excel Spreadsheet
  138. Demonstration script that reads the values stored in a spreadsheet named C:\Scripts\New_users.xls.
  139. Set objExcel = CreateObject("Excel.Application")
  140. Set objWorkbook = objExcel.Workbooks.Open _
  141.     ("C:\Scripts\New_users.xls")
  142. intRow = 2
  143. Do Until objExcel.Cells(intRow,1).Value = ""
  144.     Wscript.Echo "CN: " & objExcel.Cells(intRow, 1).Value
  145.     Wscript.Echo "sAMAccountName: " & objExcel.Cells(intRow, 2).Value
  146.     Wscript.Echo "GivenName: " & objExcel.Cells(intRow, 3).Value
  147.     Wscript.Echo "LastName: " & objExcel.Cells(intRow, 4).Value
  148.     intRow = intRow + 1
  149. Loop
  150. objExcel.Quit
  151. VBS控制Excel的一些常见方法:
  152. (一) 使用动态创建的方法
  153. 首先创建 Excel 对象,使用ComObj:
  154. oExcel = CreateObject( "Excel.Application” )
  155. 1) 显示当前窗口:
  156. oExcel.Visible = True
  157. 2) 更改 Excel 标题栏:
  158. oExcel.Caption = "应用程序调用 Microsoft Excel”
  159. 3) 添加新工作簿:
  160. oExcel.WorkBooks.Add
  161. 4) 打开已存在的工作簿:
  162. oExcel.WorkBooks.Open( "C:\Excel\Demo.xls” )
  163. 5) 设置第2个工作表为活动工作表:
  164. oExcel.WorkSheets(2).Activate
  165. oExcel.WorksSheets( "Sheet2" ).Activate
  166. 6) 给单元格赋值:
  167. oExcel.Cells(1,4).Value = "第一行第四列”
  168. 7) 设置指定列的宽度(单位:字符个数),以第一列为例:
  169. oExcel.ActiveSheet.Columns(1).ColumnsWidth = 5
  170. 8)设置指定行的高度(单位:磅)(1磅=0.035厘米),以第二行为例:
  171. oExcel.ActiveSheet.Rows(2).RowHeight = 1/0.035 ‘ 1厘米
  172. 9) 在第8行之前插入分页符:
  173. oExcel.WorkSheets(1).Rows(8).PageBreak = 1
  174. 10) 在第8列之前删除分页符:
  175. oExcel.ActiveSheet.Columns(4).PageBreak = 0
  176. 11) 指定边框线宽度:
  177. oExcel.ActiveSheet.Range( "B3:D4" ).Borders(2).Weight = 3
  178. 1-左 2-右 3-顶 4-底 5-斜( \ ) 6-斜( / )
  179. 12) 清除第一行第四列单元格公式:
  180. oExcel.ActiveSheet.Cells(1,4).ClearContents
  181. 13) 设置第一行字体属性:
  182. oExcel.ActiveSheet.Rows(1).Font.Name = "隶书”
  183. oExcel.ActiveSheet.Rows(1).Font.Color = clBlue
  184. oExcel.ActiveSheet.Rows(1).Font.Bold = True
  185. oExcel.ActiveSheet.Rows(1).Font.UnderLine = True
  186. 14) 进行页面设置:
  187. a.页眉:
  188. oExcel.ActiveSheet.PageSetup.CenterHeader = "报表演示”
  189. b.页脚:
  190. oExcel.ActiveSheet.PageSetup.CenterFooter = "第&P页”
  191. c.页眉到顶端边距2cm:
  192. oExcel.ActiveSheet.PageSetup.HeaderMargin = 2/0.035
  193. d.页脚到底端边距3cm:
  194. oExcel.ActiveSheet.PageSetup.HeaderMargin = 3/0.035
  195. e.顶边距2cm:
  196. oExcel.ActiveSheet.PageSetup.TopMargin = 2/0.035
  197. f.底边距2cm:
  198. oExcel.ActiveSheet.PageSetup.BottomMargin = 2/0.035
  199. g.左边距2cm:
  200. oExcel.ActiveSheet.PageSetup.LeftMargin = 2/0.035
  201. h.右边距2cm:
  202. oExcel.ActiveSheet.PageSetup.RightMargin = 2/0.035
  203. i.页面水平居中:
  204. oExcel.ActiveSheet.PageSetup.CenterHorizontally = 2/0.035
  205. j.页面垂直居中:
  206. oExcel.ActiveSheet.PageSetup.CenterVertically = 2/0.035
  207. k.打印单元格网线:
  208. oExcel.ActiveSheet.PageSetup.PrintGridLines = True
  209. 15) 拷贝操作:
  210. a.拷贝整个工作表:
  211. oExcel.ActiveSheet.Used.Range.Copy
  212. b.拷贝指定区域:
  213. oExcel.ActiveSheet.Range( "A1:E2" ).Copy
  214. c.从A1位置开始粘贴:
  215. oExcel.ActiveSheet.Range.( "A1" ).PasteSpecial
  216. d.从文件尾部开始粘贴:
  217. oExcel.ActiveSheet.Range.PasteSpecial
  218. 16) 插入一行或一列:
  219. a. oExcel.ActiveSheet.Rows(2).Insert
  220. b. oExcel.ActiveSheet.Columns(1).Insert
  221. 17) 删除一行或一列:
  222. a. oExcel.ActiveSheet.Rows(2).Delete
  223. b. oExcel.ActiveSheet.Columns(1).Delete
  224. 18) 打印预览工作表:
  225. oExcel.ActiveSheet.PrintPreview
  226. 19) 打印输出工作表:
  227. oExcel.ActiveSheet.PrintOut
  228. 20) 工作表保存:
  229. if not oExcel.ActiveWorkBook.Saved then
  230. oExcel.ActiveSheet.PrintPreview
  231. 21) 工作表另存为:
  232. oExcel.SaveAs( "C:\Excel\Demo1.xls” )
  233. 22) 放弃存盘:
  234. oExcel.ActiveWorkBook.Saved = True
  235. 23) 关闭工作簿:
  236. oExcel.WorkBooks.Close
  237. 24) 退出 Excel:
  238. oExcel.Quit
  239. (二) 使用VBS 控制Excle二维图
  240. 1)选择当第一个工作薄第一个工作表
  241. set oSheet=oExcel.Workbooks(1).Worksheets(1)
  242. 2)增加一个二维图
  243. achart=oSheet.chartobjects.add(100,100,200,200)
  244. 3)选择二维图的形态
  245. achart.chart.charttype=4
  246. 4)给二维图赋值
  247. set series=achart.chart.seriescollection
  248. range=”sheet1!r2c3:r3c9"
  249. series.add range,true
  250. 5)加上二维图的标题
  251. achart.Chart.HasTitle=True
  252. achart.Chart.ChartTitle.Characters.Text=” Excle二维图”
  253. 6)改变二维图的标题字体大小
  254. achart.Chart.ChartTitle.Font.size=18
  255. 7)给二维图加下标说明
  256. achart.Chart.Axes(xlCategory, xlPrimary).HasTitle = True
  257. achart.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "下标说明”
  258. 8)给二维图加左标说明
  259. achart.Chart.Axes(xlValue, xlPrimary).HasTitle = True
  260. achart.Chart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "左标说明”
  261. 9)给二维图加右标说明
  262. achart.Chart.Axes(xlValue, xlSecondary).HasTitle = True
  263. achart.Chart.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "右标说明”
  264. 10)改变二维图的显示区大小
  265. achart.Chart.PlotArea.Left = 5
  266. achart.Chart.PlotArea.Width = 223
  267. achart.Chart.PlotArea.Height = 108
复制代码
转自:http://www.activexperts.com/acti ... pts/msoffice/excel/
作者: HAT    时间: 2009-9-22 23:56

【PowerPoint】

转自:。。。。。。
作者: HAT    时间: 2009-9-22 23:58

【Access】

转自:。。。。。。
作者: 5566ljlj    时间: 2010-1-9 21:46

对我很有帮助,谢谢!
作者: oneuu    时间: 2010-1-16 10:02

还好。。。。呵呵。。。。
作者: lm180180    时间: 2011-3-2 10:32

谢谢提供........!
作者: zhanglei1371    时间: 2014-5-11 11:53

好繁琐的东西!
作者: yangd2014    时间: 2014-12-25 16:10

学习了~~~~~~~~
作者: txljb    时间: 2015-4-10 15:31

学习了~~~~~~~~
作者: zrc20d    时间: 2015-12-6 09:44

好东西,多谢分享!
作者: 峰回路转    时间: 2017-6-19 01:44

我就看看,反正我也看不懂
作者: 霖僡    时间: 2018-5-28 22:35

谢谢   学习了一大把




欢迎光临 批处理之家 (http://www.bathome.net/) Powered by Discuz! 7.2