Scribd
Upload
82 views6 pages

Excel VBA Script for File Management

This document contains scripts in VBScript for performing various automation tasks like creating and reading Excel and text files, sending emails, working with Word documents, checking checkboxes in Yahoo mail, and more. It includes over 30 scripts with comments explaining what each script does.

Uploaded by

amereetto
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
82 views6 pages

Excel VBA Script for File Management

This document contains scripts in VBScript for performing various automation tasks like creating and reading Excel and text files, sending emails, working with Word documents, checking checkboxes in Yahoo mail, and more. It includes over 30 scripts with comments explaining what each script does.

Uploaded by

amereetto
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd

'''Script to create a new excel file , write data

'''save the file with read and write protected


'''''pwd1 is for read protected pwd2 is for write protected

Set xl=CreateObject("[Link]")
Set wb=[Link]
[Link]=False
Set ws=[Link]("sheet1")
[Link](1,1)=100
[Link](1,2)=200
[Link] "e:\[Link]",,"pwd1","pwd2"
[Link]
Set xl=nothing

'''Script to open excel file ,which is read and write protected write data
'''''pwd1 is for read protected pwd2 is for write protected

Set xl=CreateObject("[Link]")
Set wb=[Link]("e:\[Link]",0,False,5,"pwd1","pwd2")
[Link]=False
Set ws=[Link]("sheet1")
[Link](1,2)="hello"
[Link](2,2)="new data"
[Link]
[Link]
Set xl=nothing

''Script to get the list of links in Google and do spell check


=================================================
==============
dim d
set mw=CreateObject("[Link]")
set d=[Link]
d("micclass").value="Link"
set a=Browser("Google").page("Google").childobjects(d)
for i=0 to [Link]-1
[Link]
s=a(i).getROProperty("innertext")
[Link] s
if [Link]>0 then
[Link] 1,"Spelling","spelling error :"&s
end if
[Link](False)
next
[Link]
set mw=nothing
=========================================
''''Script to check ON the checkboxes in yahoo mail inbox
=========================================
Dim d
Set d=[Link]
d("micclass").value="WebCheckBox"
Set c=Browser("Inbox (17) - Yahoo! Mail").Page("Inbox (17) - Yahoo!
Mail").ChildObjects(d)
For i=1 to 10
c(i).set "ON"
Next
========================================
'''script to select a mail having subject 'hi' or 'HI'
========================================
n=Browser("yahoo").Page("yahoo").WebTable("Inbox").RowCount
For i=2 to n
s=Browser("yahoo").Page("yahoo").WebTable("Inbox").GetCellData(i,7)
If lcase(trim(s))="hi" Then
Browser("yahoo").Page("yahoo").WebCheckBox("index:="&i-1).set "ON"
End If
Next
========================================
'''''Function to send a mail
========================================
Function SendMail(SendTo, Subject, Body, Attachment)
Set otl=CreateObject("[Link]")
Set m=[Link](0)
[Link]=SendTo
[Link]=Subject
[Link]=Body
If (Attachment <> "") Then
[Link](Attachment)
End If
[Link]
[Link]
Set m = Nothing
Set otl = Nothing
End Function
Call SendMail("nagesh.rao46@[Link]","hi","This is test mail for
testing","")
'''''''''''''''create a new text file
=================================================
====
Dim fs,f
Set fs=CreateObject("[Link]")
Set f=[Link]("e:\[Link]")
[Link] "hello"
[Link] "this is sample data"
[Link]
Set fs=nothing
=================================================
====
'''''''''''''''read data from a text file
=================================================
====
Dim fs,f
Set fs=CreateObject("[Link]")
Set f=[Link]("e:\[Link]",1)
While [Link]<>True
msgbox [Link]
Wend
[Link]
Set fs=nothing
=================================================
====
''''''''''create a new excel file and write data
=================================================
====
Dim xl,wb,ws
Set xl=CreateObject("[Link]")
Set wb=[Link]
Set ws=[Link]("sheet1")
[Link](1,1)=10
[Link](2,1)=20
[Link](3,1)=50
[Link] "e:\[Link]"
[Link]
Set xl=nothing
=================================================
====
'''''''open existing file and write data in second column in Sheet1
=================================================
====
Dim xl,wb,ws
Set xl=CreateObject("[Link]")
Set wb=[Link]("e:\[Link]")
Set ws=[Link]("sheet1")
[Link](1,2)="mindq"
[Link](2,2)="hyd"
[Link](3,2)="ap"
[Link]
[Link]
Set xl=nothing
=================================================
====
'''''''''''read data from excel from rows and columns
=================================================
====
Dim xl,wb,ws
Set xl=CreateObject("[Link]")
Set wb=[Link]("e:\[Link]")
Set ws=[Link]("sheet1")
r=[Link]
c=[Link]
For i=1 to r
v=""
For j=1 to c
v=v&" "& [Link](i,j)
Next
print v
print "-----------------------"
Next
[Link]
Set xl=nothing
=================================================
=====
''''''''''''''''get the bgcolor in a cell in excel
=================================================
=====
Dim xl,wb,ws
Set xl=CreateObject("[Link]")
Set wb=[Link]("e:\[Link]")
Set ws=[Link]("sheet1")
r=[Link]
c=[Link]
For i=1 to r
For j=1 to c
x=[Link](i,j).[Link]
msgbox x
Next
Next
[Link]
Set xl=nothing
=================================================
======'
'''''''''''''''''''''create word and write data
=================================================
======
dim mw
set mw=CreateObject("[Link]")
[Link]
[Link] "hello"
[Link] "e:\[Link]"
[Link]
set mw=nothing
=================================================
======
''''''''''script will display all the doc files in all the drives in the system
=================================================
=======
Dim mw
Set mw=CreateObject("[Link]")
Set fs=createobject("[Link]")
Set d=[Link]
[Link]="*.doc"
For each dr in d
msgbox dr
[Link]=dr
[Link]=True
[Link]
For each i in [Link]
print i
Set f=[Link](i)
print [Link]&" "&[Link]&" "&[Link]
print "-------------------------------------------------------------------"
Next
Next
[Link]
=================================================
=========
'''''''''Open Internet Explorer and navigate to yahoomail
=================================================
=========
Dim ie
Set ie=CreateObject("[Link]")
[Link]=True
[Link] "[Link]"
x=Browser("CreationTime:=0").GetROProperty("title")
msgbox x
=================================================
=========
''''''Create word, Create table and write all the services names
=================================================
=========
Set mw = CreateObject("[Link]")
[Link] = True
Set dc = [Link]()
Set objRange = [Link]()
[Link]
objRange,1,3
Set objTable = [Link](1)
x=1
strComputer = "."
Set wms=GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = [Link]("Select * from Win32_Service")
For Each s in colItems
If x > 1 Then
[Link]()
End If
[Link](x, 1).[Link] = True
[Link](x, 1).[Link] = [Link]
[Link](x, 2).[Link] = [Link]
[Link](x, 3).[Link] = [Link]
x=x+1
Next

Common questions

Powered by AI

The script uses Word's Application object to create a new document and add a table with three columns. It queries the Win32_Service class to retrieve services' names, display names, and states. It writes these details into the table, adding a bold font for the first column, and iteratively adds rows to accommodate each service .

The script opens the Excel file and accesses the worksheet. It determines the used range and iterates over each cell, retrieving the 'interior.colorindex' property for each cell, which provides the background color index of the cell. The result is displayed in a message box .

The script uses the Word Application's 'FileSearch' method to search for Word documents ('*.doc') across all system drives. It sets the 'LookIn' property to each drive and enables 'SearchSubFolders'. It then executes a search and iterates over found files, displaying their names, sizes, and creation dates .

The script creates a description object for web checkboxes by setting the 'micclass' property to "WebCheckBox". It then retrieves all child objects that match this description within the Yahoo Mail inbox and iteratively sets the 'ON' state for the first ten checkboxes using a loop .

The script defines a 'SendMail' function using the Outlook Application object. It creates a new mail item, sets the recipient, subject, and body from the parameters passed to the function. If an attachment is specified, it adds it to the mail. Finally, the mail is sent using 'm.Send' and the Outlook Application is quit .

The script uses the 'Saveas' method of the Excel Workbook object to apply read and write protection to an Excel file. It passes two password parameters, 'pwd1' for read protection and 'pwd2' for write protection, as part of the Saveas method call .

The script opens an existing Excel file using 'CreateObject' for the Excel Application and 'Workbooks.Open'. It accesses 'sheet1' and assigns values to specific cells in the second column directly by setting the 'cells' properties with new text values. After writing, the workbook is saved and closed .

The script interacts with Microsoft Word to check for spelling errors. It uses the 'WordBasic.insert' method to add the text of each link into a new Word document. If the document contains any spelling errors, identified via 'ActiveDocument.Spellingerrors.count', a report is generated with the details of the misspelled words .

The script opens an Excel file using 'CreateObject' for the Excel Application and 'Workbooks.Open'. It accesses the worksheet 'sheet1' and calculates the used range of rows and columns. A nested loop then reads and concatenates the values cell by cell, printing each row's values before moving to the next row .

The script employs 'CreateObject' to instantiate the Internet Explorer Application. It sets the 'Visible' property to True to make the browser window visible and uses the 'Navigate' method to open the Yahoo Mail webpage .

You might also like