I could really use some help here.
When the users clicks email (as attachment...) I need to create a cop
of the spread sheet that contains no formulas, only the data that is i
the cells. Delete hidden columns and rows, and then email the copy.

I know how to grab the email event, I can delete the hidden columns an
rows.

I do not know how to create a copy of a sheet that is only the data.

And right now, when I create a copy, the original has it's row
deleted, not the copy and the copy is emailed.
What I have:

Dim xRange As Excel.Range
Dim sname As String = "test"
Dim _cachePath As String = StingerGlobal.Config.InstallPath
Dim stuffToDelete As New ArrayList

Dim hiddenColumns As String = ""
Dim firstColumn As Boolean = True
Dim numColumns As Integer = _sheet.UsedRange.Columns.Count

'create temp copy

Dim tempSheet As Excel.Worksheet

tempSheet = DirectCast(_sheet, Excel.Worksheet)

tempSheet.Copy()
tempSheet.Name = sname
tempSheet.SaveAs(_cachePath & sname & ".xls")
'remove rows&columns
xRange = CType(tempSheet.Cells, Excel.Range)

For x As Integer = 1 To numColumns
Dim currentColumn As Excel.Range
Dim hiddenValue As Boolean
currentColumn = CType(tempSheet.Columns(x), Excel.Range)
hiddenValue = CType(currentColumn.Hidden, Boolean)
If hiddenValue = True Then
stuffToDelete.Add(currentColumn)
End If
Next

For Each col As Excel.Range In stuffToDelete
col.Delete()
Next


Thanks

--
geekoi
-----------------------------------------------------------------------
geekoid's Profile: http://www.msusenet.com/member.php?userid=80
View this thread: http://www.msusenet.com/t-187013486

Hi geekoid

Install my SendMail add-in
http://www.rondebruin.nl/mail/add-in.htm

Or see the code examples on my site
http://www.rondebruin.nl/sendmail.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"geekoid" wrote in message ...

I could really use some help here.
When the users clicks email (as attachment...) I need to create a copy
of the spread sheet that contains no formulas, only the data that is in
the cells. Delete hidden columns and rows, and then email the copy.

I know how to grab the email event, I can delete the hidden columns and
rows.

I do not know how to create a copy of a sheet that is only the data.

And right now, when I create a copy, the original has it's rows
deleted, not the copy and the copy is emailed.
What I have:

Dim xRange As Excel.Range
Dim sname As String = "test"
Dim _cachePath As String = StingerGlobal.Config.InstallPath
Dim stuffToDelete As New ArrayList

Dim hiddenColumns As String = ""
Dim firstColumn As Boolean = True
Dim numColumns As Integer = _sheet.UsedRange.Columns.Count

'create temp copy

Dim tempSheet As Excel.Worksheet

tempSheet = DirectCast(_sheet, Excel.Worksheet)

tempSheet.Copy()
tempSheet.Name = sname
tempSheet.SaveAs(_cachePath & sname & ".xls")
'remove rows&columns
xRange = CType(tempSheet.Cells, Excel.Range)

For x As Integer = 1 To numColumns
Dim currentColumn As Excel.Range
Dim hiddenValue As Boolean
currentColumn = CType(tempSheet.Columns(x), Excel.Range)
hiddenValue = CType(currentColumn.Hidden, Boolean)
If hiddenValue = True Then
stuffToDelete.Add(currentColumn)
End If
Next

For Each col As Excel.Range In stuffToDelete
col.Delete()
Next


Thanks.


--
geekoid
------------------------------------------------------------------------
geekoid's Profile: http://www.msusenet.com/member.php?userid=802
View this thread: http://www.msusenet.com/t-1870134866

thanks, however I do not know what email client our clients will b
using.

For other reasons, we can't really use an Add-in, I need to code this.

thanks again

--
geekoi
-----------------------------------------------------------------------
geekoid's Profile: http://www.msusenet.com/member.php?userid=80
View this thread: http://www.msusenet.com/t-187013486

I have code on my site also.

You can use the part that create the new workbook with only the visible cells from this macro
http://www.rondebruin.nl/mail/folder1/mail4.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl



"geekoid" wrote in message ...

thanks, however I do not know what email client our clients will be
using.

For other reasons, we can't really use an Add-in, I need to code this.

thanks again.


--
geekoid
------------------------------------------------------------------------
geekoid's Profile: http://www.msusenet.com/member.php?userid=802
View this thread: http://www.msusenet.com/t-1870134866