PDA

View Full Version : delete a sheet



Felipe Dasi
12-11-2008, 10:58 AM
Hi,
In bad english: How do I delete a worksheet automatically by reading her name in a txt file?

In Portuguese: Como eu deleto uma planilha automaticamente lendo o nome dela num arquivo txt?

Demosthine
12-13-2008, 07:31 PM
Good Evening.

I sincerely hope this doesn't offend anyone, but that "bad English" sounds like the start of a bad divorce joke!

But seriously, the English wasn't bad at all.

This solution has two primary parts:
- Opening and reading the text file.
- Verifying and deleting the appropriate Worksheets.

I'll start by explaining one of the easiest ways to read data from a Text File. It's not necessarily the best or most up-to-date option, but it's simple.

The code is well commented, so see below:


Option Explicit
Public Sub Delete_Sheets()
' Declare a Variable to store the Location of the Text File.
Dim strFile As String
' Assign the Variable a Path to the desired File. This is
' for easy modification.
strFile = ThisWorkbook.Path & "\DelSheets.txt"
' Determine if the Text File exists.
' If it does not exist, we will create it for demonstration
' purposes.
If Dir(strFile) = "" Then
' Using the Open Statment, we will create a File.
' Open <pathname> For <accessmode> As #<filenumber>
' - <pathname> - Reason we defined strFile
' - <accessmode - One of Four Options
' - Append - Write to the file saving previous data.
' - Binary - Write to it in Binary.
' - Output - Write to the file as if it were new.
' - Random - Read/Write to the file
Open strFile For Output As #1
' Write definitions to delete certain Worksheets.
Print #1, "Sheet2"
Print #1, "Sheet3"

' Close the File
Close #1
End If

' Since the file either existed prior to our Dir Check or after
' we created it, we will now continue processing the data.
' Because the file is closed, we must open it first.
Open strFile For Input As #1
' Define a Variable to store the Worksheet Names as we
' read them.
Dim strSheet As String
' We need to ignore Errors for the Error Checking, so we will
' Resume Next.
On Error Resume Next
' Using a Do ... Loop, Read the file until the End pf tje File.
Do While Not EOF(1)
' Read the Line and store it in the Variable.
Input #1, strSheet

' If the Worksheet does not exist, we will not do anything.
If Worksheets(strSheet) Is Nothing Then
' No further processing.
' The worksheet exists, so we'll delete it.
Else
Worksheets(strSheet).Delete
End If
Loop
' Close the File.
Close #1
' Delete the File so that it will not be processed again.
Kill strFile
End Sub


Scott

Felipe Dasi
12-14-2008, 04:40 AM
Hi, Scott, morning

In portuguese:

Obrigado, scott, o que precisava era isso mesmo.Adoro este f?rum, se n?o fosse pela minha dificuldade de linguagem, eu estaria mais presente. Agrade?o muito sua aten??o

In English:
Thanks, Scott, what he needed was this same. I adore this forum, if he was not for my difficulty of language, I would be more present. I thank its attention very.

Gossioii3
04-17-2009, 01:21 PM
I feel good that there are people like you too. Thanks for this great weblog of yours. Its surely going to get me to go to higher places!