Copy and Paste a range of cell in Excel using VBScript

Using Below code you can copy and paste the range of cell from one place to another

Code:

Set ObjExcel= createobject("Excel.Application")
Set ObjWorkBk=ObjExcel.Workbooks.Open("D:\Pulkit.xls")
ObjExcel.Visible = True
ObjExcel.DisplayAlerts = False
Set ObjWorkSht=ObjWorkBk.worksheets("Sheet1")
ObjWorkSht.range("A1:B6").Copy
ObjWorkSht.Range("C1:D6").PasteSpecial
ObjWorkBk.Save
 ObjExcel.Quit

Please share your feedback on this :)

Add,Count,Rename and delete a sheet of an Excel using vbscript

Excel - Vbscript

'Add a New worksheet into an existing excel

Set ObjExcel= createobject("Excel.Application")
Set ObjWorkBk=ObjExcel.Workbooks.Open("D:\Pulkit.xls")
ObjExcel.Visible = True
ObjExcel.DisplayAlerts = False
Set ObjWorkSht=ObjWorkBk.Sheets.add

'Count the sheets

Set ObjExcel= createobject("Excel.Application")
Set ObjWorkBk=ObjExcel.Workbooks.Open("D:\Pulkit.xls")
ObjExcel.Visible = True
ObjExcel.DisplayAlerts = False 
Msgbox "Total " & ObjWorkSht=ObjWorkBk.Sheets.count &" WorkSheets are present in the excel."

'Rename all the Sheet present in the excel

 Set ObjExcel= createobject("Excel.Application")
Set ObjWorkBk=ObjExcel.Workbooks.Open("D:\Pulkit.xls")
ObjExcel.Visible = True
ObjExcel.DisplayAlerts = False

for i = 1 to  ObjWorkBk.Sheets.count
ObjWorkBk.Sheets(i).Name = "Blogger"&i
Next

'Delete all worksheets

Set ObjExcel= createobject("Excel.Application")
Set ObjWorkBk=ObjExcel.Workbooks.Open("D:\Pulkit.xls")
ObjExcel.Visible = True
ObjExcel.DisplayAlerts = False

for i = 1 to  ObjWorkBk.Sheets.count
ObjWorkBk.Sheets(i).delete
Next

Creating a new Excel Sheet Via VbScript

Excel - Vbscript

To Create an Excel workbook via Vbscript, again first you need to create the COM of Excel and then provide the location where you want to save your file.

Step 1 - Create COM of Excel


Set ObjExcel = CreateObject("Excel.Application")   'Create COM of excel

Step 2 - Add a naive workbook





Set objWorkbook = ObjExcel.Workbooks.Add (1)   'Add WorkBook
objExcel.Application.DisplayAlerts = False 
objExcel.Application.Visible = True     

Step 3 - Specify the Name of WorkSheet

Set ObjWorkSheet=objWorkbook.Worksheets("Sheet1")  'By Default 1 sheet is name as Sheet1



Step 4 - Enter the value in Cell

  ObjWorkSheet.Cells(1,1).Value="My Sheet"

Step 5 - Save your workbook

 objWorkbook.SaveAs("D:\MySheet.xls")
 ObjExcel.Quit

Step 6 - Free the memory used by Excel

Set ObjExcel = Nothing
Set objWorkbook = Nothing
Set ObjWorkSheet = Nothing

You can even add the more than one sheet at Run Time and update the values up there.

Set ObjExcel = CreateObject("Excel.Application")  

Set objWorkbook = ObjExcel.Workbooks.Add (1)    '
objExcel.Application.DisplayAlerts = False  
objExcel.Application.Visible = True                                                       
Set ObjWorkSheet=objWorkbook.Sheets.add

  ObjWorkSheet.Cells(1,1).Value="My Sheet" ' Now value is entered in New sheet

 objWorkbook.SaveAs("D:\MySheet.xls")
 ObjExcel.Quit

Set ObjExcel = Nothing
Set objWorkbook = Nothing
Set ObjWorkSheet = Nothing


 

Update the value in Excel using VbScript

Excel - VbScript

To Modify/Update the value from Excel sheet you first need to connect to the excel worksheet and then need to specify the cell whose value you want to change.

Again, To connect to the Excel sheet you have couple of options like by creating COM of Excel, By connect to the Excel using ADODB(Activex DataObject DataBase).

Here we are using the most simple and common method COM.

Step 1 - Connect to the excel

Set ObjExcel = CreateObject("Excel.Application")   'Create COM of excel

Set objWorkbook = ObjExcel.Workbooks.Open("D:\Pulkit.xls")    'Provide the location of your file
objExcel.Application.DisplayAlerts = False   'to suppress any run time error
objExcel.Application.Visible = True        'To display the Excel,
Set ObjWorkSheet=objWorkbook.Worksheets("Sheet1")  'Name/Index of the worksheet which user want 'to open, You can either use ObjWorkSheet=objWorkbook.Worksheets("Sheet1")


Step 2 - Specify the cell where you want to update the value

ObjWorkSheet.Cells(1,1).value  = "My Sheet"
ObjWorkSheet.Cells(1,2).value  = "Update Excel"

Step 3 - Save your Excel file before closing the connection
Note - Excel can save the updated values automatically even without using Save command, but for safer side always, Save the excel sheet before quit the application

objWorkbook.Save
 ObjExcel.Quit

Now you are can free the memory allocated to the excel.

Set ObjExcel = Nothing
Set objWorkbook = Nothing
Set ObjWorkSheet = Nothing

Read Excel from Vbscript

Excel - VbScript

In order to open the excel from vbscript, you first need to create COM object of Excel then you need to provide the location of your excel file and then finally the name or index of the worksheet.

Once the above details are provided, you can read the content of your Excel.

Here is the code to open the excel from vbscript:

Set ObjExcel = CreateObject("Excel.Application")   'Create COM of excel

Set objWorkbook = ObjExcel.Workbooks.Open("D:\Pulkit.xls")    'Provide the location of your file

objExcel.Application.DisplayAlerts = False   'to suppress any run time error

objExcel.Application.Visible = True      'To display the Excel, if this field is mark as 'false',then user                                                       is unable to see excel file by default objExcel.Application.Visible = 'False'

Set ObjWorkSheet=objWorkbook.Worksheets("Sheet1") 'Name of the worksheet which user want to open

Read content of excel

So for reading the content of excel, we first need to know how many rows(Containing data) and columns(Containing data) present in the excel.

To Get the No of row we used:

RowCount = ObjWorkSheet.UsedRange.rows.count

To Calculate No of columns, We used:

ColCount = ObjWorkSheet.UsedRange.Columns.count

Now to get the values, we need to loop the script from cell (1,1) to cell(RowCount,ColCount)

For i=1 to RowCount  step 1

 For j = 1 to ColCount step 1

  CellValue = objExcel.Cells(i,j).Value

MsgBox "The Cell " & i & "," & j & " having value " &  CellValue

 Next

Next

So for each cell, value is displayed in a msgbox.

Now the last step, free the memory allocated to excel Workbook, Worksheets.

Set ObjExcel =Nothing
Set ObjWorkBk = Nothing
Set ObjWorkSheet = Nothing