GU_mr_Sand_Man
Board Regular
- Joined
- May 26, 2004
- Messages
- 88
Good morning all. I am trying to figure why a piece of code I recently wrote is not working...
When I run the line "ShData.Delete", nothing happens (the sheet is not deleted and no errors occur). Does anyone know how to delete this sheet from workbook "Wb"? As always, any help would be greatly appreciated. Thanks in advance.
Code:
Dim Wb As Excel.Workbook
Dim ShReport As Excel.Worksheet
Dim ShData As Excel.Worksheet
Dim rst As ADODB.Recordset
Dim SQL As String
Dim DistTemplateFileName As String
Set Report = CreateObject("Excel.Application")
'Confidential - I define a file to open using "DistTemplateFileName"
Set Wb = Report.Workbooks.Open(DistTemplateFileName)
Set ShReport = Wb.ActiveSheet
Set ShData = Wb.Worksheets.Add
ShData.Activate
'Confidential - I create a big SQL statement
Set rst = GetRecordset(SQL)
ShData.Range(Cells(1, 1), Cells(1, 19)).CopyFromRecordset rst
ShData.Range(Cells(1, 4), Cells(10, 10)).Copy
ShReport.Cells(5, 5).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Report.CutCopyMode = False
ShData.Delete
End Sub
____________________________________________________________
Public Function GetRecordset(SQL As String) As ADODB.Recordset
Dim rst As ADODB.Recordset
On Error GoTo ExitFunction
Set rst = New ADODB.Recordset
rst.Open SQL, CurrentProject.Connection, adOpenForwardOnly
Set GetRecordset = rst
ExitFunction:
End Function
When I run the line "ShData.Delete", nothing happens (the sheet is not deleted and no errors occur). Does anyone know how to delete this sheet from workbook "Wb"? As always, any help would be greatly appreciated. Thanks in advance.