CODEWITHSUNDEEP
excelvbscript
usersam
usersam

Reputation: 1245

Writing CSV data to an Excel file

I am trying to read a CSV file which is semicolon separated and writing its data to an Excel file cell by cell.

My CSV data is like below:

CATALOG;NAME   ;TYPE
---;---;---
test   ;Mapping   ;BASE
test   ;RECEPIENT    ;BASE

I am trying to append this data to an Excel using below VBScript code.

Set objShell = WScript.CreateObject ("WScript.Shell")
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(objShell.CurrentDirectory & "\" & "Data.xlsx")

'objExcel.Application.Visible = True
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
LastRow = objSheet.UsedRange.Rows.Count
WScript.Echo "LastRow "&LastRow

'objExcel.Cells(LastRow+1, 1).Value = "Test value"

Set objFileToRead = CreateObject("Scripting.FileSystemObject").OpenTextFile(objShell.CurrentDirectory & "\" & "Output.csv",1)
Dim strLine
Do While Not objFileToRead.AtEndOfStream
    strResults = objFileToRead.ReadAll
Loop
objFileToRead.Close
Set objFileToRead = Nothing

If Trim(strResults) <> "" Then
    ' Create an Array of the Text File
    arrline = Split(strResults, vbNewLine)
    'WScript.Echo UBound(arrline)
End If

For i = 0 To UBound(arrline)
    Do
        If i = 1 Then Exit Do

        If arrline(i) = "" Then
            ' checks for a blank line at the end of stream
            Exit For
        End If

        ReDim Preserve arrdata(i)
        arrdata(i) = Split(arrline(i), ";")

        For j = 0 To UBound(arrdata(i))
            WScript.Echo Trim(arrdata(i)(j))
            'objExcel.Cells(LastRow+1+i,j).Value = Trim(arrdata(i)(j))
        Next
    Loop While False
Next

objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close

objExcel.Application.Quit
WScript.Echo "Finished."
WScript.Quit

It is showing the csv data but throwing error

Execl.vbs(41, 6) Microsoft VBScript runtime error: Unknown runtime error

Line number 41 is

objExcel.Cells(LastRow+1+i,j).Value = Trim(arrdata(i)(j))

It works if I put some hardcoded value (5,6 ..) in place of j, but it's not taking j as variable. I can not put any value of j as the number of columns in the input CSV is unknown. Please let me know where I am making a mistake and how to resolve it.

Upvotes: 0

Views: 427

Answers (1)

elliot svensson
elliot svensson

Reputation: 603

I bet the problem lies with looping through the columns starting at an improper index, column 0. Please try adjusting this line:

    For j = 0 To UBound(arrdata(i))

to be

    For j = 1 To UBound(arrdata(i))

and make sure to validate that it's not overlooking real data in the far-left column!

Upvotes: 1

Related Questions