Hello all, I created an app that takes an Excel workbook template and creates a report from it.

The excel workbook template has 3 tabs in it, 2 template tabs, and 1 data tab.

I wanted to know if someone could tell me the best way to code the functions (I am currently looking into doing it with ADO, but I don't know if ADO can do everything that I want to do):

Here is an example of the way I am currently coding a function:



code:--------------------------------------------------------------------------------
Public Function copySheetTemplate(Workbook as Excel.Workbook, SheetToCopy as String, NewSheetName as String) as boolean

On Error GoTo HandleError
Workbook.Sheets(SheetToCopy).Copy After:=Sheets (SheetToCopy)

Workbook.Sheets(SheetToCopy & "(2)").Name = NewSheetName

copySheetTemplate = True
Exit Function

HandleError:
copySheetTemplate = False
End Function
--------------------------------------------------------------------------------


And here is the way I did it the first time:


code:--------------------------------------------------------------------------------
Public Function copySheetTemplate(Workbook as Excel.Workbook, SheetToCopy as String, NewSheetName as String) as boolean
Dim objWorksheet as Excel.Worksheet

On Error GoTo HandleError
set objWorksheet = Workbook.Worksheets(SheetToCopy)

objWorksheet.Activate

objWorksheet.Copy After:=Sheets (SheetToCopy)

Set objWorksheet = nothing

set objWorksheet = Workbook.Worksheets(SheetToCopy & "(2)")
objWorksheet.Activate
objWorksheet.Name = NewSheetName

set objWorksheet = nothing

copySheetTemplate = True
Exit Function

HandleError:
copySheetTemplate = False
End Function
--------------------------------------------------------------------------------


Now, the first way appears to run faster than the second...but I noticed (and the first code is the way I am doing it now) when the app runs and the excel objects visible property is set to true, if I minimize the excel window or go to another window, it stops running....and then as soon as i go back to the excel window it continues. And even worse, if I set the visible property to false (so it can run in the background, which is how I want it to run) it stops....then it is stuck because there is no way to 'give it the focus'.

So, my primary question is, which is the better way to do the functions (i have quite a few more that copy rows and change values in cells, I had originally used worksheet objects and excel.range objects with those, but switched to make it faster), using the extra objects, or doing it the first way?

Thanks to anyone who has at least READ this far : ) And thanks in advance to anyone who can help.....