Print Mutiplication Table From 1 to 100 on Excel Sheet 

*Its preferrable to use Notepad++

1. Make a new Excel file with  sheet "Sheet 1".

2. Make a Log file "Log.txt" for storing Logs .

3.Open Notepad++ , open a new blank file and Name it say "PrintMulti1to100.vbs " . Note : ".vbs" extension and file type "All" is important to write in vbs scrpit

4. Now Write Following Code in the notepad++ file created in Step 3 :

___________________________________________________________________________________    

On error resume Next

set args = WScript.Arguments

vExcelFilePath = args.Item(0)

vLogPath = args.Item(1)

 

'!!!!!!! STATIC PATH_INFO

 

vExcelFilePath = "C:\Users\Lav Tiwari\Documents\practiseOL1\Print.xlsx"

vExcelTabName = "Sheet1"                                                                                                   

vLogPath = "C:\Users\Lav Tiwari\Documents\practiseOL1\Log.txt"

 

MsgBox("Email To :" & vExcelFilePath & vbCrLf &  " vExcelTabName :" & vExcelTabName & vbCrLf &  " vLogPath :" & vLogPath)

 

'!!!!!!!!!!!!!! Create Object of Log File

 

Set objFS=CreateObjects("Scripting.FileSystemObject")

Set FSO_Handle = objFS.OpenTextFile(vLogPath,8,True)

 

 Set objPasteData = Nothing

Set objRawData = Nothing

 

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

 

objExcel.DisplayAlerts = False

 

Set ObjRawData = objExcel.Workbooks.Open(vExcelFilePath,False)

 

FSO_Handle.WriteLine(Now &"Excel Operation has been completed successfully ")

objExcel.windowstate = -4137

 

ObjRawData.Worksheets(vExcelTabName).Activate

 

‘ !!!!!!!!  Logic 1 : Print 1 to 100 

 

For i=1 to 100

' ObjRawData.Worksheets(vExcelTabName).Cells(i,1).Value = i         ' Print Along the Column

' ObjRawData.Worksheets(vExcelTabName).Cells(1,i).Value = i         ' Print in the Row            

ObjRawData.Worksheets(vExcelTabName).Range("A"& i).Value = i  ' Using Range Syntax

' MsgBox(i)

Next

 

 ______________________________________________________________________________

‘ !!!!!!!! Logic 2 : To print multiplication table from 1 to 100

 

For j=1 to 100

For i=1 to 10

                                                                                                                                       ObjRawData.Worksheets(vExcelTabName).Cells(i,j).Value =  i*j                   

' MsgBox(i)

Next

Next

______________________________________________________________________________ 

‘ !!!!!!! Logic 3 : Table from 1 to 100 in  a * b = c format with “ Table of “ Title

                                                                                                   

For j=1 to 100

ObjRawData.Worksheets(vExcelTabName).Cells(1,j).Value = "Table of =" & j

 For i=1 to 10

' ObjRawData.Worksheets(vExcelTabName).Cells(i,1).Value = i

' ObjRawData.Worksheets(vExcelTabName).Cells(1,i).Value = i

ObjRawData.Worksheets(vExcelTabName).Cells(i+1,j).Value = i &" * "& j &" = "& i*j    

' MsgBox(i)  

Next

Next

 

______________________________________________________________________________ 

‘ !!!!! Logic 4 -:  with condition - odd multiples only

 

For j=1 to 100

ObjRawData.Worksheets(vExcelTabName).Cells(1,j).Value = "Table of =" & j

 

For i=1 to 10

 if i Mod 2 <> 0 Then    ‘ (if i Mod 2 = 0 for Even )

  'ObjRawData.Worksheets(vExcelTabName).Cells(i,1).Value = i

  'ObjRawData.Worksheets(vExcelTabName).Cells(1,i).Value = i

ObjRawData.Worksheets(vExcelTabName).Cells(i+1,j).Value =  i & " * " & j & " = " & i*j 

End if                                                                           

' MsgBox(i)

 Next

 Next

 

_____________________________________________________________________________ 

‘ !!!!! Logic 5 : Table from 1 to 100 in a * b = c format with “ Table of “ Title Coloured

 

For j=1 to 100

 

ObjRawData.Worksheets(vExcelTabName).Cells(1,j).Value = "Table of =" & j

ObjRawData.Worksheets(vExcelTabName).Cells(1,j).Interior.ColorIndex = 37

 

For i=1 to 10

 

 'ObjRawData.Worksheets(vExcelTabName).Cells(i,1).Value = i

 'ObjRawData.Worksheets(vExcelTabName).Cells(1,i).Value = i

 

ObjRawData.Worksheets(vExcelTabName).Cells(i+1,j).Value = i &" * "& j &" =" & i*j    

 

 ‘MsgBox(i)

 Next

 Next

 

ObjRawData.Save

 ObjRawData.Close True

 

'Error Handling in VBS

 

If Err.Number <> 0 Then

 

    FSO_Handle.WriteLine(Now & "Excel Operation | ExcelPrint1to100.vbs | Error | " &                  Err.Number & ":" & Err.Description)

              vStatus = "Fail"

Else

    FSO_Handle.WriteLine(Now & "Excel Operation | ExcelPrint1to100.vbs has been  completed succesfully ")

               vStatus ="Pass"

              

End If

    MsgBox("Status-: " & vStatus )

    WScript.StdOut.WriteLine vStatus

   

On Error Goto 0

   

    WScript.Quit

   

    FSO_Handle.Close()

   

'Autoclose Message Box In Vbs Script

 

    Function MegaBox_QC(p1)

 

                     CreateObject("Wscript.Shell").Popup p1 , 3 , "Qc Robo" , 4 + 32

    End Function

 

 

_________________________________________________________________________________

Note:  '  Sign before a code line makes it comment line , shortcut " ctrl + k " in Notepad++ to                    make  something comment or "Shift + ctrl + k" reverse it .

_________________________________________________________________________________

 RESULT :