Importing CSV Into New Worksheet

Question

I'm trying to import a CSV file into a newly created Excel worksheet, but haven't been able to utilize existing Q&A here (and elsewhere) to solve this issue.

Also, I'm sure I'm making this WAY more difficult than it should be, so please feel free to correct this bloated code if you see fit:

$varOneSheet = "OneSheet"

$xlsNewFile = New-Object -ComObject Excel.Application
$xlsNewFile.SheetsInNewWorkbook = 3
$xlsNewFile.displayAlerts = $false
$xlsNewFile.Visible = $false
$xlsWorkbook = $xlsNewFile.Workbooks.Add()

$sheetToRename = $xlsNewFile.Sheets.Item("Sheet1")
$sheetToRename.Name = $OneSheet

#this all works below, we need to push the data over to #xlsWorkbook now
$xlsCSVFile = New-Object -ComObject Excel.Application
$xlsCSVFile.displayAlerts = $false
$csvFilename = (".\DATA.CSV")
$xlsCSVFile.Workbooks.OpenText($csvFilename, 2, 1, 1, 1, $false, $false, $false, $true)
$xlsCSVFile.Visible = $true 
$tmpSheetTOFile = $xlsWorkbook.Sheets.Item(1)
$tmpWorksheet = $xlsCSVFile.Sheets.Item(1)
#########################above is fine

#########################below should copy into $xlsWorkbook Sheet 1
$tmpWorksheet.Copy($tmpSheetTOFile)

The last line above is the failure point, which yields:

Exception calling "Copy" with "1" argument(s): "Copy method of Worksheet class
failed"
At line:1 char:23
+     $tmpWorksheet.Copy <<<< ($tmpSheetTOFile)
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ComMethodTargetInvocation

Do I really need to create a separate/temporary workbook? Why was I not able to simply use OpenText on the already Saved/existing Workbook?

EDIT: I used this previous question to get to where I am now, so that's why I'm uncertain as to where the failure point lies. As the answer was accepted and signed-off on.

Answer 1

You're creating two different Excel application instances:

$xlsNewFile = New-Object -ComObject Excel.Application
...
$xlsCSVFile = New-Object -ComObject Excel.Application

Copying only works within the same instance, and using two instances isn't required anyway. Simply remove

$xlsCSVFile = New-Object -ComObject Excel.Application
$xlsCSVFile.displayAlerts = $false
...
$xlsCSVFile.Visible = $true 

and change

$xlsCSVFile.Workbooks.OpenText($csvFilename, 2, 1, 1, 1, $false, $false, $false, $true)
...
$tmpWorksheet = $xlsCSVFile.Sheets.Item(1)
...
$tmpWorksheet.Copy($tmpSheetTOFile)

to

$xlsNewFile.Workbooks.OpenText($csvFilename, 2, 1, 1, 1, $false, $false, $false, $true)
...
$xlsNewFile.Workbooks.Item(2).Sheets.Item(1).Copy($tmpSheetTOFile)

---

Having that said, Excel auto-creates a workbook with a new sheet when it imports a CSV anyway (which is also the reason why OpenText doesn't import a CSV into an existing workbook), so why do you need to copy it to another new workbook in the first place? Normally it would suffice to just save the imported CSV as an Excel workbook. If you require additional sheets you can just add them.

$wsName      = 'OneSheet'
$csvFilename = '.\data.csv'
$xlsFilename = '.\data.xlsx'

$xl = New-Object -ComObject 'Excel.Application'
$xl.DisplayAlerts = $false
$xl.Visible = $false

$xl.Workbooks.OpenText($csvFilename, 2, 1, 1, 1, $false, $false, $false, $true)

$wb = $xl.Workbooks.Item(1)
$ws = $wb.Sheets.Item(1)
$ws2 = $wb.Sheets.Add([Type]::Missing, $ws)
$ws3 = $wb.Sheets.Add([Type]::Missing, $ws2)
$ws.Name = $wsName

$wb.SaveAs($xlsFilename, 51)