Hi Team,
Had multiple “.csv” files with me that need to convert as a single “.xlsx” file.
’ Ensure Excel Interop is properly referenced in UiPath (through Manage Packages → Install Microsoft.Office.Interop.Excel)
’ Declare variables with explicit types to avoid late binding and use CType for type conversion
Dim xlApp As Microsoft.Office.Interop.Excel.Application
xlApp = CType(New Microsoft.Office.Interop.Excel.Application(), Microsoft.Office.Interop.Excel.Application) ’ Explicitly create Excel application
Dim xlWorkbook As Microsoft.Office.Interop.Excel.Workbook
Dim xlWorksheet As Microsoft.Office.Interop.Excel.Worksheet
Dim directoryPath As String = “C:\path\to\csv\files” ’ Replace with your actual folder path
Dim outputExcelFile As String = “C:\path\to\output\combined.xlsx” ’ Output path for Excel file
’ Get the list of CSV files in the directory
Dim csvFiles As String() = CType(System.IO.Directory.GetFiles(directoryPath, “*.csv”), String())
’ Initialize the current row in Excel
Dim currentRow As Integer = 1
’ Initialize Excel application
xlApp.Visible = CType(False, Boolean) ’ Set Excel to run in the background
xlApp.DisplayAlerts = CType(False, Boolean) ’ Disable Excel prompts (e.g., SaveAs prompt)
’ Create a new workbook and get the first worksheet
xlWorkbook = CType(xlApp.Workbooks.Add(), Microsoft.Office.Interop.Excel.Workbook)
xlWorksheet = CType(xlWorkbook.Sheets(1), Microsoft.Office.Interop.Excel.Worksheet)
’ Loop through all CSV files in the directory
For Each csvFile As String In csvFiles
’ Read all lines from the CSV file
Dim csvData As String() = CType(System.IO.File.ReadAllLines(csvFile), String())
' Loop through each line in the CSV
For Each line As String In csvData
' Split the CSV line by commas to get the values
Dim values As String() = CType(line.Split(","c), String())
' Write the values to the Excel worksheet
For col As Integer = 0 To values.Length - 1
xlWorksheet.Cells(currentRow, CType(col + 1, Integer)).Value = CType(values(col).Trim(), Object)
Next
' Move to the next row in the Excel sheet
currentRow = CType(currentRow + 1, Integer)
Next
Next
’ Save the workbook as Excel file (.xlsx)
xlWorkbook.SaveAs(outputExcelFile)
’ Close the workbook and Excel application
xlWorkbook.Close(CType(False, Boolean))
xlApp.Quit()
’ Release the COM objects to avoid memory leaks
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorksheet)
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook)
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
’ Optional: You can log or display a message to inform that the process is complete
MsgBox(“CSV files have been successfully combined into an Excel file.”)
Note - Had a requirement to do this task in VB.net script only.
use of invoke code activity.
to resolve this already declare with Ctype still unable to resolve the issue
for more insight i had used chatgpt to get the code facing issue at line “37” as “option strict allow late binding”