Powershell with excel.

Maybe this will help. It imports Excel into a SQL table

http://poshcode.org/1098

#Change these settings as needed
$filepath = 'C:\Users\u00\Documents\backupset.xlsx'
#Comment/Uncomment connection string based on version
#Connection String for Excel 2007:
$connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$filepath`";Extended Properties=`"Excel 12.0 Xml;HDR=YES`";"
#Connection String for Excel 2003:
#$connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=`"$filepath`";Extended Properties=`"Excel 8.0;HDR=Yes;IMEX=1`";"
$qry = 'select * from [backupset$]'
$sqlserver = 'Z002\SQLEXPRESS'
$dbname = 'SQLPSX'
#Create a table in destination database with the with referenced columns and table name.
$tblname = 'ExcelData_fill'
 
#######################
function Get-ExcelData
{
 
    param($connString, $qry='select * from [sheet1$]')
 
    $conn = new-object System.Data.OleDb.OleDbConnection($connString)
    $conn.open()
    $cmd = new-object System.Data.OleDb.OleDbCommand($qry,$conn) 
    $da = new-object System.Data.OleDb.OleDbDataAdapter($cmd) 
    $dt = new-object System.Data.dataTable 
    [void]$da.fill($dt)
    $conn.close()
    $dt
 
} #Get-ExcelData
 
#######################
function Write-DataTableToDatabase
{ 
    param($dt,$destServer,$destDb,$destTbl)

    $connectionString = "Data Source=$destServer;Integrated Security=true;Initial Catalog=$destdb;"
    $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
    $bulkCopy.DestinationTableName = "$destTbl"
    $bulkCopy.WriteToServer($dt)
 
}# Write-DataTableToDatabase

#######################
$dt = Get-ExcelData $connString $qry
Write-DataTableToDatabase $dt $sqlserver $dbname $tblname

Select allOpen in new window

Your wording is ambiguous - do you want to
1. take some data from somewhere, and put it into Excel
2. take some Excel data, and want to format it as e.g. a HTML table?

I have data coming into Excel and I would like that data to be in a Table with a green style. Not sure if its possible .

OK where is what i would like the table to look like.
Capture.JPG

Found it. Thanks for all the help.

Puts everything in WS to a Table.

#Create Table
$TableRange = $ws.Range("A1","H300")
$ws.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange, $xl.ActiveCell.CurrentRegion, $null, [Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes).Name = "Table2"
$ws.ListObjects.Item("Table2").TableStyle = "TableStyleMedium2"
$TableRange.EntireColumn.Autofit()

I think you should just do that in Excel VBA (as a macro).