Adding an existing WorkSheet to a workBook

[Mastakilla]

Hi,

i know how to create a new excel sheet in a workbook,
for example this code works fine:
(i allready have a workbook)

dim myNewSheet = workbook.worksheets.add

But what i want is to use a function that returns a workSheet which i would like to be able to add to my workbook:

imagine i have a function:

public function getSheet() as excel.worksheet

'process

getsheet = someExistingWorkSheet

end function

I want to use it like this:

myWorkBook.worksheets.add (getSheet())

i know it's not the right property for this action, but is there any at all that lets me do that??


I hope i was clear enough,

many thanks in advance

 

[Mastakilla]

Ok thanks guys for all your answers, but i've figured it out on my own, that i will share with you right now, you'll see, as most solutions one can come up with, it's a very simple one.


ok, here it is, instead of creating a function that returns a sheet i thought why not create a Sub to which i'll send a reference to my excel.Application !! i know it sounds horribly simple, almost stupid... but hey! we're not all einsteins here!


so the code looks a bit like this:

public sub insertSheet(excelAp as Excel.Application, sheetName as string)
   
     Dim xlsBook As Excel.Workbook
     Dim xlsWs As New Excel.Worksheet
     xlsBook = excelAp.ActiveWorkbook
     xlsWs = xlsBook.Worksheets.Add()
     xlsWs.Name = sheetName

     'plus a long process if you want

end sub

 

[SBendBuckeye]

Hello,

A possible refinement

Public Sub InsertSheet(wkBook As Excel.WorkBook, sheetName As String)
    Dim wks As Excel.WorkSheet       
    Dim addr As String
    'Rename an existing empty sheet if one exists
    For Each wks In wkBook.Sheets
        If wks.UsedRange.Cells.Count = 1 Then
            addr = wks.UsedRange.Cells
            If wks.Range(addr) = "" Then
                If InStr(wks.Name, "Sheet") = 1 Then
                    wks.Name = sheetName
                    Exit Sub
                End If
            End If
        End If
    Next wks
    wks = wkBook.Worksheets.Add()
    wks.Name = sheetName
End Sub

Good Luck!

Have a great day!

j2consulting@yahoo.com

 

[Mastakilla]

Thanks for the help ;-)