How to convert a .xls file to a .xlsx file using the 'Execute macro' activity

There are instances when reading a corrupt .xls file using the ‘Read Excel file’ activity gives incorrect results. To resolve this issue, we can convert the .xls file to a .xlsx file which can then later be used by the ‘Read Excel file’ activity.

Instructions

A .xls file can be converted to a .xlsx file using the ‘Execute macro’ activity by following the below steps:

1. In the Path parameter, you need to provide the path to the .xls file.

2. In the Macro file path parameter, you need to provide the path to the .vbs file.

3. In the Sub or Function name parameter, you need to specify the function name to be launched.

The VBScript

Sub Test()
Dim xlApp
Set xlApp = CreateObject("Excel.Application")
xlApp.visible = False
set objWb = xlApp.Workbooks.Open("C:\Users\Alphy\Documents\ElectroNeek\04-July\8247\8207\8207\Data 20220624 121556_ From BAS with Barcode.xls")
set oSheet = objWb.Worksheets(1)
objWb.saveas "C:\Users\Alphy\Documents\ElectroNeek\04-July\8247\8207\8207\Test.xlsx",51
xlApp.Quit
End Sub

The path to the .xls file can also be passed as an input argument as follows:

Sub Test(Path)
Dim xlApp
Set xlApp = CreateObject("Excel.Application")
xlApp.visible = False
set objWb = xlApp.Workbooks.Open(Path)
set oSheet = objWb.Worksheets(1)
objWb.saveas "C:\Users\Alphy\Documents\ElectroNeek\04-July\8247\8207\8207\Test2.xlsx",51
xlApp.Quit
End Sub

⬅️

Back to the table

How to convert strings to numbers in Studio Pro

You may be interested in adding all the numbers present in a column. By default, these numbers are contained within a string.

When you try to add two strings such as “9.00”+”13.12”, both the strings will be concatenated as “9.0013.12”.

To resolve this issue, we need to convert strings to numbers using the JS functions such as parseInt() or parseFloat().

Instructions

Let's consider a scenario where the variable temp holds the string "12". To convert this string into a number, you can use the following syntax:

parseFloat(temp, 10)

Here's an explanation of the syntax:

1. parseFloat: This is a JavaScript function used to convert a string into a floating-point number (decimal number).
2. temp: This is the variable that holds the string you want to convert. In this case, it is the string "12".
3. , 10: The , 10 part is an optional argument that specifies the radix (base) of the number being parsed. In this case, the radix is 10, which indicates that the number being parsed is in the decimal system (base 10).

So, by using parseFloat(temp, 10), the string "12" will be converted to the number 12.0.

⬅️

Back to the table

How to convert an HTML file to a PDF file using the 'Execute Python' activity

To accomplish this, you can utilize the 'Execute Python' activity, which facilitates the conversion process from HTML to PDF.

Instructions

Prerequisites:

1. In the Command Prompt, run the following command to install pdfkit:

pip install pdfkit

2. Set the PATH variable to the binary folder in Environment Variables. To do so, follow these steps:
   1. First, locate the binary folder of the application you want to add to the PATH. In this case, it is the bin folder of wkhtmltopdf, which is typically located at C:\Program Files\wkhtmltopdf\bin.
   2. Open the Start menu and search for "Environment Variables" and click on "Edit the system environment variables."
   3. In the System Properties window, click on the "Environment Variables" button.
   4. In the Environment Variables window, scroll down to the "System variables" section, and find the variable called "Path" in the list.
   5. Select the "Path" variable and click on the "Edit" button.
   6. In the "Edit Environment Variable" window, click on the "New" button.
   7. Enter the path to the binary folder (C:\Program Files\wkhtmltopdf\bin) in the "New User Variable" field.
   8. Click "OK" to save the changes and close all the windows.

By adding C:\Program Files\wkhtmltopdf\bin to the "Path" variable, you allow your system to find and access the executable files (binaries) within that folder from any command prompt or terminal window. This makes it easier to run wkhtmltopdf commands directly from any location in the command prompt without specifying the full path to the executable every time.

Python script

import pdfkit

config = pdfkit.configuration(wkhtmltopdf = r"C:\\Program Files\\wkhtmltopdf\\bin\\wkhtmltopdf.exe")

pdfkit.from_file('C:\\Users\\Alphy\\Documents\\ElectroNeek\\12-Sep\\9180\\Lakshmi S.html', 'C:\\Users\\Alphy\\Documents\\ElectroNeek\\12-Sep\\9180\\Lakshmi S.pdf', configuration = config)
 

⬅️

Back to the table

How to convert a PDF file to a docx file using the 'Execute Python' activity

The ‘Execute Python’ activity can be used to convert a PDF file to a docx file.

Instructions

Prerequisites

In the Command Prompt, run the following command to install pdf2docx:

pip install pdf2docx

Pyhton script

from pdf2docx import Converter
import sys
pdf_file = 'C:\\Users\\Alphy\\Documents\\ElectroNeek\\12-Sep\\9180\\input.pdf'
docx_file ='C:\\Users\\Alphy\\Documents\\ElectroNeek\\12-Sep\\9180\\output.docx'
print(docx_file)
cv = Converter(pdf_file)
cv.convert(docx_file)
cv.close()

⬅️

Back to the table

How to make .bas files work with the Execute macro activity

Learn how to merge .bas files with the Execute Macro activity to expand your automation capabilities.

Instructions

Follow these simple steps to bring together .bas files and the Execute Macro activity.

Creating the .vbs Framework

Begin by crafting a .vbs file that acts as a bridge for your .bas code integration. Here's a basic example:

sub runmacro()
Set Application = CreateObject("Excel.Application")
Set Excel = Application.Workbooks.Open(WScript.Arguments(0))
Set Routine = Excel.Application.Workbooks(1).VBProject.VBComponents.Import("Complete filepath of the .bas code")
Application.Wait (Now + TimeValue("0:00:02"))
Application.Run (Routine.Name & ".MethodName")
Excel.save
Excel.Application.Quit
End sub

Writing the .vbs File:

• Copy the above .vbs code example into the editor.
• Modify the "Complete filepath of the .bas code" in line 4 to match your .bas file's location.
• Adjust "MethodName" in line 6 with the desired method name from the .bas file.

📘

Note

• In line 4, place the complete .bas file path in double quotes.
• In line 6, add a period before the method (".MethodName").

Illustrative .bas Code

Consider this .bas code for a practical example.

Sub DeleteMatchingLines()
    Dim ws As Worksheet
    Dim rng As Range
    Dim i As Long, j As Long
    Dim criteria As String
    
    criteria = "Text_to_find" ' Replace with your criteria.
    
    Set ws = ThisWorkbook.Sheets("Sheet_Name") ' Replace with the sheet name.
    Set rng = ws.Range("A1:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
    
    For i = rng.Rows.Count To 1 Step -1
        If rng.Cells(i, 1).Value = criteria Then
            rng.Cells(i, 1).EntireRow.Delete
        End If
    Next i
End Sub

⬅️

Back to the table