Delete worksheet from workbook using vb

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...

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.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Jeremy

I can't see why that code wouldn't work.

But I can tell you that you have some things in the code that could cause problems.

One of them is the non-qualified references to Cells.
 

GU_mr_Sand_Man

Board Regular
Joined
May 26, 2004
Messages
88
Thanks again for your comments, Norie.

The cell references would make sense if I could be a little more elaborate with what I could share (I had to cut some pieces out for confidentiality reasons). That being said, VB passes right by that "delete" line of code without doing anything; I am completely baffled.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Jeremy

Sorry meant to elaborate, but hit Submit by mistake.

Now the problem with the Cells is that they are unqualified.

If you have an unqualified reference in Excel VBA then the code will look at the active object, in this case the active sheet.

In Access VBA when you're automating Excel I'm not 100% sure how the issue would be handled, but I imagine it would be along the same lines.

Another problem when automating and not using explicit references is explained here.

Now I know this doesn't directly relate to your problem, but I can't actually see why you are getting that behaviour.

Have you tried stepping through the code?

Are you using any error handling?

PS This is how you could handle the unqualified references.
Code:
    With ShData
        .Range(.Cells(1, 1), .Cells(1, 19)).CopyFromRecordset rst
        .Range(.Cells(1, 4), .Cells(10, 10)).Copy
    End With
 

GU_mr_Sand_Man

Board Regular
Joined
May 26, 2004
Messages
88
Yes I have stepped through the code and have tested error handling; the program ends normally when stepping through and no errors popped up when I inserted "On Error GoTo TheEnd".
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Jeremy

Could the error handling be hiding an error that is causing the code to skip to the end without executing the delete?

Generally I just don't use error handling, I try to make it so the code won't error.
 

Forum statistics

Threads
1,136,596
Messages
5,676,712
Members
419,646
Latest member
ReneeDJ73

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top