|
Home > Archive > MS SQL Server > October 2006 > delete empty worksheets in excel
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
delete empty worksheets in excel
|
|
|
| I am trying to delete empty worksheets in excel.
I use the code below an it gives me errors. I got this code from
somewhere and I have also tweaked it a bit.
"Excel_Worksheet.cellsA(2,1) = isempty" in my code is throwing up the
error.
I am new to coding, so it will be great somebody can alter this code
and make it work.
Thanks in advance
Function Main()
Dim Excel_Application
Dim Excel_WorkBook
Dim Excel_WorkSheet
Dim iSheetCounter
Dim bFound
Dim sFilename
sFilename = "C:\Documents and Settings\Testing.xls"
Set Excel_Application = CreateObject("Excel.Application")
Set Excel_WorkBook =
Excel_Application.Workbooks.Open(sFilename)
bFound = False
For Each Excel_WorkSheet in Excel_WorkBook.WorkSheets
If Excel_WorkBook.WorkSheets.Count >1 and
Excel_Worksheet.cellsA(2,1) = isempty Then
Excel_WorkSheet.Delete
Excel_WorkBook.Save
bFound = True
Exit For
End if
Next
If bFound = True then
Msgbox "Outcome = Sheet Deleted"
Else
MsgBox "Outcome = No Sheet Was deleted"
End if
Set Excel_WorkSheet = Nothing
Excel_WorkBook.Close
Set Excel_WorkBook = Nothing
Excel_Application.Quit
Set Excel_Application = Nothing
Main = DTSTaskExecResult_Su
ccess
End Function
Thanks
Sam
| |
| Dave Patrick 2006-10-24, 6:29 pm |
| All air code but try something like;
For Each Excel_WorkSheet in Excel_WorkBook.WorkSheets
If Excel_WorkBook.WorkSheets.Count >1 Then
If IsNull(Excel_Workshe
et.cellsA(2,1)) OR _
Excel_Worksheet.cellsA(2,1) = "" Then
Excel_WorkSheet.Delete
Excel_WorkBook.Save
bFound = True
Exit For
End If
End if
Next
--
Regards,
Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Sam" wrote:
|I am trying to delete empty worksheets in excel.
| I use the code below an it gives me errors. I got this code from
| somewhere and I have also tweaked it a bit.
| "Excel_Worksheet.cellsA(2,1) = isempty" in my code is throwing up the
| error.
| I am new to coding, so it will be great somebody can alter this code
| and make it work.
|
| Thanks in advance
|
| Function Main()
| Dim Excel_Application
| Dim Excel_WorkBook
| Dim Excel_WorkSheet
| Dim iSheetCounter
| Dim bFound
| Dim sFilename
| sFilename = "C:\Documents and Settings\Testing.xls"
| Set Excel_Application = CreateObject("Excel.Application")
| Set Excel_WorkBook =
| Excel_Application.Workbooks.Open(sFilename)
| bFound = False
|
| For Each Excel_WorkSheet in Excel_WorkBook.WorkSheets
| If Excel_WorkBook.WorkSheets.Count >1 and
| Excel_Worksheet.cellsA(2,1) = isempty Then
| Excel_WorkSheet.Delete
| Excel_WorkBook.Save
| bFound = True
| Exit For
| End if
| Next
| If bFound = True then
| Msgbox "Outcome = Sheet Deleted"
| Else
| MsgBox "Outcome = No Sheet Was deleted"
| End if
|
| Set Excel_WorkSheet = Nothing
| Excel_WorkBook.Close
| Set Excel_WorkBook = Nothing
| Excel_Application.Quit
| Set Excel_Application = Nothing
|
| Main = DTSTaskExecResult_Su
ccess
|
| End Function
|
|
|
| Thanks
| Sam
|
|
|
|
|
|