CODEWITHSUNDEEP
powershell
user21687905
user21687905

Reputation: 1

Method invocation failed because [System.__ComObject] does not contain a method named 'LoadFromDataTable'

When I run the script below, I receive the error "Method invocation failed because [System.__ComObject] does not contain a method named 'LoadFromDataTable'". Not sure how to fix this problem.

The script reads csv file and exports to excel file. Must keep the leading zeros in column #9.

cmdlet ConvertCSVToXLS6.ps1 at command pipeline position 1 Supply values for the following parameters: InputFile: C:\Users\xxx\Documents\powershell_scripts\inputfile.csv OutputFile: C:\Users\xxx\Documents\powershell_scripts\test.xls Method invocation failed because [System.__ComObject] does not contain a method named 'LoadFromDataTable'. At C:\Users\xxx\Documents\powershell_scripts\ConvertCSVToXLS6.ps1:20 char:1

-----------------------script--------------------------------

param (
    [Parameter(Mandatory=$true)]
    [string]$InputFile,

    [Parameter(Mandatory=$true)]
    [string]$OutputFile
)

# Load the CSV data
$data = Import-Csv $InputFile

# Create a new Excel workbook
$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Add()

# Get the first worksheet
$worksheet = $workbook.Worksheets.Item(1)

# Write the CSV data to the worksheet
$range = $worksheet.Range("A1").LoadFromDataTable($data, $true)

# Format column 9 to retain leading zeros
$range = $worksheet.Range("I:I")
$range.NumberFormat = "000000000"

# Save the workbook as XLS format
$workbook.SaveAs($OutputFile, -4143)

# Close the workbook and quit Excel
$workbook.Close()
$excel.Quit()`

Not sure how to find/fix the missing method 'LoadFromDataTable'.

Upvotes: 0

Views: 278

Answers (1)

Dávid Laczkó
Dávid Laczkó

Reputation: 1101

It seems to me that you are following a tutorial where 3rd-party stuff is used, as the range object doesn't have a method called LoadFromDataTable netiher in VBA nor in .NET, and using the search box there are 0 results for this name in the entire documentation. Always refer to the docs in these cases by the way, however I can not find a COM-specific object model, so one can only guess that it is the VBA one...
But you don't need to load the csv, prepare a new workbook, and paste: Excel can open csv. Just open it and save. And format before save if necessary.

Upvotes: 0

Related Questions