Excel to Access Code Question

[foxhound]

Hello,

How would I accomplish the following? There are multiple sheets ranging from "Sheet1 (1)" to "Sheet1 (?)" that follow the pattern. I do not always know what the starting number or ending number will be. I want to select all the sheets where the first 6 characters of the sheet name is "Sheet1 (" and delete them.

Sheets(Array("Sheet1 (4)", "Sheet1 (5)","Sheet1 (6)")).delete

 

[mdmilner]

This goes slightly further and if the text 'Sheet1' is anywhere in the sheet name, it will delete it. If you'd like to still be specific and only test the first 6 characters, then you substitute a LEFT(objSheet.Name,6)

Public Sub ShowSheets()
Dim objSheet As Object   ' or you can use As Worksheet
Dim strTest As String

strTest = "Sheet1"

For Each objSheet In Sheets
  If InStr(objSheet.Name,strTest) > 0 Then
    ' Delete the Current Sheet Code
  End If
Next

End Sub

Did you, uh, goof and mean to post this in the Excel forum? Or is there more to this question that you forgot to post?

Mike

 

[foxhound]

Hi Mike,

Thanks for the quick reply. I will give it a shot. I am running this code in Access and I know that sometimes there are differences in how Access and Excel process code like "in string" versus "left". That is why I posted it here.

Thanks again

 

[mdmilner]

Got it, my mistake then. I'm fairly certain there are numerous examples of how to run xls from Access using the search utility - but it's relatively easy to repost if you need it.

Mike