I have an excel file which i download from a site. I need to write 1 to every row in a specific column. What is the fastest way?

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

@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