delete worksheet where row2 is empty

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

delete worksheet where row2 is empty

Author

Topic

srxr9
Starting Member

15 Posts

Posted - 2006-09-18 : 21:54:27

I am trying to delete worksheets where row 2 is empty.
I use the code below and it gives me errors. I got this code from
somewhere and I have tweaked it a bit.
"Excel_Worksheet.cellsA(2,1) = isempty" in my code is throwing up the error.

Actually this is what I want the code to do.
1) Delete all the worksheets that have row2 empty
2) If all the worksheets in the workbook has row2 empty, then delete the workbook.
It will be really useful for me if someone could help me with this.
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_Success

End Function

Thanks
suresh

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-09-18 : 23:05:42

If you have Visual basic installed on your machine I would cut & paste this code into VB and step through it using the F8 key. That way you can easily tell where you're going wrong (remove the DTSTaskResult constants as VB won't recognise them)
I always write my VBA scripts in VB and it saves loads of time.

btw IsEmpty is a function not a constant as you're referring to it. You would normally use it like IsEmpty(yourParamName). But it will only work correctly on some data types, and I'm not even sure if it can be used in VBA. Your best bet is probably to compare it to an empty string (and use the .Value property of the cell just to be safe)

HTH,

Tim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 01:42:46

For Each Excel_WorkSheet in Excel_WorkBook.WorkSheets
If Excel_WorkBook.WorkSheets.Count >1 and
trim("" & Excel_Worksheet.cells(2,1)) = "" Then
Excel_WorkSheet.Delete
Excel_WorkBook.Save
bFound = True
Exit For --- WHY? If there are more than one sheet where row 2 is empty?
End if
Next

Peter Larsson
Helsingborg, Sweden

srxr9
Starting Member

15 Posts

Posted - 2006-09-19 : 13:08:36

Thanks to Tim and Peter for your replies.

Peter,

Using what you have suggested, I am able to find the worksheet that has the cell(2,1) empty, but I guess Excel_Worksheet.Delete in the code, does not work. It is not deleting the worksheet. Why is it so? Doesn't Excel_Worsheet in the loop have the name of the worksheet it is processing.

For Each Excel_WorkSheet in Excel_WorkBook.WorkSheets
If Excel_WorkBook.WorkSheets.Count >1 and
trim("" & Excel_Worksheet.cells(2,1)) = "" Then
Excel_WorkSheet.Delete
Excel_WorkBook.Save
bFound = True
End if
Next

Thanks
Suresh

srxr9
Starting Member

15 Posts

Posted - 2006-09-19 : 16:29:50

Thanks for all your help.
The code below works fine. I am posting the code just in case if somebody is looking to solve similar issue.
Though I have not yet included the logic to delete the entire workbook if all the worksheets with cell(2,1) is empty.
Any help in solving this will be appreciated.

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 trim("" & Excel_Worksheet.cells(2,1)) = "" Then
sSheetname = Excel_Worksheet.Name
Excel_Application.DisplayAlerts = False
Excel_WorkBook.Worksheets(sSheetName).Delete
Excel_Application.DisplayAlerts = True
Excel_WorkBook.Save
bFound = True
End if
Next
bFound = True

Set Excel_WorkSheet = Nothing
Excel_WorkBook.Close
Set Excel_WorkBook = Nothing
Excel_Application.Quit
Set Excel_Application = Nothing

Main = DTSTaskExecResult_Success

End Function

Thanks
Suresh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources