CODEWITHSUNDEEP
excelpowershell
Sencha718
Sencha718

Reputation: 63

How do I export just one worksheet in Excel to a single htm file?

I'm having trouble saving one worksheet from Excel workbook as a single .htm file.

I know if I open up Excel, open my workbook, select the worksheet I want, and do a "Save As" file type .htm it will work. Every time I code it I get "Hitlist.htm" plus a folder named "Hitlist_files" with all the style sheets, etc. Unfortunately, in powershell it does the "save as" with all the other data and supplemental files (extra folders, styling specifications, etc.)

Help. Code below.

#Create and get my Excel Obj
$excel = New-Object -comobject Excel.Application
$excel.visible=$false
$excel.DisplayAlerts=$false
$UserWorkBook = $excel.Workbooks.Open("e:\hitlist\hitlist.xlsx")

#Select first Sheet
$UserWorksheet = $UserWorkBook.Worksheets.Item(1)

#HitList File name and type
$hitlist = "E:\HitList\Hitlist.htm"
$xlHtml = 44

#Save, close, and clean up
#I tried this too...no go - $UserWorkBook.SaveAs($hitlist,$xlHtml)
$UserWorksheet.SaveAs($hitlist,$xlHtml)
$UserWorkBook.close()
$excel.quit()
$excel = $null

Upvotes: 3

Views: 3017

Answers (1)

gravity
gravity

Reputation: 2056

I've adjusted your code to your expectations again, after we clarified some more. In particular, look at any line denoted with #changed-grav for my modifications to your existing code, or the very end (#added-grav) for some additional steps I added to fit the exact specs:

(This was tested fully, and appears to be working exactly as you requested - but I did some modifications for my testing, so let me know if I didn't change a value back that you needed)

#Create and get my Excel Obj
$excel = New-Object -comobject Excel.Application
$excel.visible=$false
$excel.DisplayAlerts=$false
$UserWorkBook = $excel.Workbooks.Open("e:\hitlist\hitlist.xlsx")

#Select first Sheet
$UserWorksheet = $UserWorkBook.Worksheets.Item(1)

#HitList File name and type
$hitlistCSV = "e:\hitlist\hitlist.csv" #changed-grav
$hitlistHTML = "e:\hitlist\hitlist.htm" #changed-grav
$xlCSV = 6  #changed-grav

#Save, close, and clean up
$UserWorksheet.SaveAs($hitlistCSV,$xlCSV) #changed-grav
$UserWorkBook.close()
$excel.quit()
$excel = $null

#new functionality, to import the CSV and then export as HTM
#added-grav START
$htmlData = Get-Content $hitlistCSV | ConvertFrom-CSV | ConvertTo-HTML
Set-Content $hitlistHTML $htmlData
Remove-Item $hitlistCSV
#added-grav END

Upvotes: 3

Related Questions