My personal preference is to use Excel as a database and use SQL queries. I've used this on customer projects countless times and it’s fast and super reliable. We have a video here on how to do it.

To do it with Excel VBA, you’d need to write the macro in the spreadsheet and then use the Excel Advanced > Run Macro action

@OliverM108256 thank you for your reply. Since I do not have excel file as template which i can put excel vba inside, because i download from a site. I can not do it with vba script?

im going to watch the video you suggested

Hi @Semih 3318 ,

Here’s a solution for automating the task of writing “1” to every row in a specific column, tailored for both low and high-volume scenarios:

Low Volume (Excel Basic Package)

• Use Case: Suitable for files with fewer than 10,000 rows.
• Benefits:
  • No need for Microsoft Office.
  • Operates in the backend, improving performance.
• Steps:
  1. Open Excel:

     Excel Basic > Open
     - File Path:  Your Excel File Path]
     - Session Name: Session1
     

  2. Loop Through Rows:

     Loop > Each Row in Spreadsheet
     - Session Name: Session1
     - Start Row: 2 (assuming row 1 is the header)
     - End Row: dLast Row Number]
     

  3. Set Cell Value:

     Excel Basic > Set Cell
     - Session Name: Session1
     - Cell:  Column Letter]LCurrent Row]
     - Value: 1
     

  4. Save and Close Excel:

     Excel Basic > Save
     - Session Name: Session1
     Excel Basic > Close
     - Session Name: Session1
     

High Volume (Excel Advanced Package)

• Use Case: Ideal for larger datasets and more complex operations.
• Benefits:
  • Advanced functionalities for complex tasks.
  • Requires Microsoft Office.
• Steps:
  1. Open Excel:

     Excel Advanced > Open Spreadsheet
     - File Path:  Your Excel File Path]
     - Session Name: Session1
     

  2. Get Number of Rows:

     Excel Advanced > Get Number of Rows
     - Session Name: Session1
     - Output Variable: $pNumTotalRowCount$
     

  3. Set Cell Value:

     Excel Advanced > Set Cell
     - Session Name: Session1
     - Cell: A2:A$pNumTotalRowCount$
     - Value: 1
     

  4. Save and Close Excel:

     Excel Advanced > Save Spreadsheet
     - Session Name: Session1
     Excel Advanced > Close Spreadsheet
     - Session Name: Session1
     

By choosing the appropriate package based on your data volume and environment, you can ensure efficient and effective automation.

@DK 964 hi, thank you for responding. I think using excel advanced can do this with slow speed. I thought I could do it faster by using something else except excel basic-advanced.

Like vba or something

Hi @Semih 3318 

Then you can try with VBScripts.

When deciding between VBScript, Excel Basic/Advanced packages in Automation Anywhere, and Python scripts for automating Excel tasks, each option has its own advantages and use cases. Here’s a comparison to help justify the use of VBScript over the others:

VBScript

Advantages:

• Simplicity: VBScript is straightforward and easy to learn, especially for those familiar with VBA (Visual Basic for Applications).
• Integration with Windows: VBScript integrates well with Windows environments, making it a good choice for automating tasks on Windows machines.
• No Additional Software: It doesn’t require additional software installations, as it runs natively on Windows.

Here’s a step-by-step guide to create a VBScript that writes “1” to every row in a specific column (e.g., “Type”) in an Excel file:

Step-by-Step Guide

1. Open Notepad:

   • Open Notepad or any text editor of your choice.

2. Write the VBScript Code:

   • Copy and paste the following code into the text editor. This script will open an Excel file, write “1” to every row in a specified column, and then save and close the file.

' Create an Excel application object
Set objExcel = CreateObject("Excel.Application")

' Make Excel visible (optional)
objExcel.Visible = True

' Open the Excel file
Set objWorkbook = objExcel.Workbooks.Open("C:\path\to\your\file.xlsx")

' Access the first worksheet
Set objWorksheet = objWorkbook.Sheets(1)

' Get the last row with data in the specified column (e.g., column "Type")
lastRow = objWorksheet.Cells(objWorksheet.Rows.Count, "A").End(-4162).Row ' -4162 is the constant for xlUp

' Loop through each row and write "1" to the specified column (e.g., column "Type")
For i = 1 To lastRow
    objWorksheet.Cells(i, 2).Value = 1 ' Change 2 to the column number of "Type"
Next

' Save the workbook
objWorkbook.Save

' Close the workbook
objWorkbook.Close

' Quit Excel application
objExcel.Quit

' Clean up
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing

3. Save the Script:

   • Save the file with a .vbs extension, for example, WriteToExcel.vbs.

4. Run the Script:

   • Double-click the .vbs file to run the script. This will open Excel, write “1” to every row in the specified column, save the workbook, and then close Excel.

Explanation of the Script

• CreateObject(“Excel.Application”): Creates an instance of the Excel application.
• objExcel.Visible = True: Makes the Excel application visible. Set to False if you want to run the script in the background.
• objExcel.Workbooks.Open(“C:\path\to\your\file.xlsx”): Opens the specified Excel file. Replace "C:\path\to\your\file.xlsx" with the actual path to your Excel file.
• objWorksheet.Cells(objWorksheet.Rows.Count, “A”).End(-4162).Row: Finds the last row with data in column “A”. Adjust the column letter if needed.
• objWorksheet.Cells(i, 2).Value = 1: Writes “1” to each cell in the specified column. Change 2 to the column number of “Type”.
• objWorkbook.Save: Saves the changes made to the workbook.
• objWorkbook.Close: Closes the workbook.
• objExcel.Quit: Quits the Excel application.

@DK 964 i was trying to do this with another method like vbs, but tried with excel basic, and it was so fast, so did not need vbs for this.

Thanks