VBScript, Excel, Delete empty rows containing empty cells

Hi All,
I have been working on a script that imports users from OUs in AD to Excel.  Got it working, it formats and all.  I do not know how I can get the script to delete Rows that contain Empty Cells.
Here is the script so far.


Option Explicit

Dim dtmDate, strMonth, strYear, strFileName, objExcel, objWorkbook, objWorksheet1, objWorksheet2, objRange, objRange2, objContainer, objChild

Const xlAscending = 1
Const xlYes = 1

dtmDate = Date
strMonth = Month(Date)
strYear = Year(Date)

strFileName = "C:\" & "Month_End_" & strMonth & "-" & strYear & ".xls"
'Names XLS file


Set objExcel = CreateObject("Excel.Application")
set objWorkbook = objExcel.Workbooks.Add
'sets objWorkbook to use Excel.Workbooks.Add function
set objWorksheet1 = objWorkBook.WorkSheets(1)
'sets the name objWorksheet1 to Excel.Application.Workbooks.Worksheets(1)
objExcel.Visible = True

objExcel.ActiveSheet.Name = "JSC_Users"                  'names Active Sheet
objExcel.ActiveSheet.Range("A1").Activate                  'Selects A1
objExcel.ActiveCell.Value = "Last_Name"                        'col header 1
objWorkSheet1.Cells(1, 2).Value = "First_Name"                  'col header 2
objWorkSheet1.Cells(1, 3).Value = "Description"                  'col header 3
objExcel.ActiveCell.Offset(1,0).Activate                        'move 1 down

Set objContainer = GetObject("LDAP://OU=New Jersey,DC=JSC,DC=COM")

objContainer.Filter = Array("user")
For Each objChild In objContainer
      objExcel.ActiveCell.Value = objChild.SN
      objExcel.ActiveCell.Offset(0,1).Value = objChild.GivenName
      objExcel.ActiveCell.Offset(0,2).Value = objChild.Description
      objExcel.ActiveCell.Offset(1,0).Activate                  'move 1 down
Next

Set objContainer = GetObject("LDAP://OU=Long Island,DC=JSC,DC=COM")

objContainer.Filter = Array("user")
For Each objChild In objContainer
      objExcel.ActiveCell.Value = objChild.SN
      objExcel.ActiveCell.Offset(0,1).Value = objChild.GivenName
      objExcel.ActiveCell.Offset(0,2).Value = objChild.Description
      objExcel.ActiveCell.Offset(1,0).Activate                  'move 1 down
Next

Set objContainer = GetObject("LDAP://OU=NYC,DC=JSC,DC=COM")

objContainer.Filter = Array("user")
For Each objChild In objContainer
      objExcel.ActiveCell.Value = objChild.SN
      objExcel.ActiveCell.Offset(0,1).Value = objChild.GivenName
      objExcel.ActiveCell.Offset(0,2).Value = objChild.Description
      objExcel.ActiveCell.Offset(1,0).Activate                  'move 1 down
Next

objworksheet1.Columns.AutoFit()
'Autosize for Column Width for Work Sheet 1

'objWorkSheet1.Range("A2:C300").Activate
'highlight Column A Row2 : Column C Row 200


set objRange = objWorksheet1.UsedRange
set objRange2 = objExcel.Range("A1")

objRange.Sort objRange2, xlAscending, , , , , , xlYes
'Sorts ALL used Cells by Column 1 (A1).  xlAscending, sorts ascending, xlYes means Header Row=Yes





set objWorksheet2 = objWorkBook.WorkSheets(2)
objWorksheet2.Name = "Assentor Users"
objWorksheet2.activate                              'activates Assentor Users Work Sheet












'objWorkBook.SaveAs(strFileName)
'objExcel.Quit

____________________________________________________

Any help is appreciated.

Thanks
LVL 1
jsctechyAsked:
Who is Participating?
 
sirbountyCommented:
Oh, okay - just change this loop then:

For Each site in sites
  Set objContainer = GetObject("LDAP://" & site & ",DC=JSC,DC=COM")
  objContainer.Filter = Array("user")
  For Each objChild In objContainer
      If objChild.SN <> "" And Len(objChild.SN) > 2 Then 'Test for non-blank and greater than 2 characters in the SN property
        objExcel.ActiveCell.Value = objChild.SN
        objExcel.ActiveCell.Offset(0,1).Value = objChild.GivenName
        objExcel.ActiveCell.Offset(0,2).Value = objChild.Description
        objExcel.ActiveCell.Offset(1,0).Activate                  'move 1 down
      End If
  Next
Next
0
 
sirbountyCommented:
Something like this?

objContainer.Filter = Array("user")
For Each objChild In objContainer
      If objChild.SN <> "" Then
        objExcel.ActiveCell.Value = objChild.SN
        objExcel.ActiveCell.Offset(0,1).Value = objChild.GivenName
        objExcel.ActiveCell.Offset(0,2).Value = objChild.Description
        objExcel.ActiveCell.Offset(1,0).Activate                  'move 1 down
      End If
Next
0
 
sirbountyCommented:
You could probably put this into an array as well...

sites=Array("OU=New Jersey","Long Island", "NYC")

For Each site in sites
  Set objContainer = GetObject("LDAP://" & site & ",DC=JSC,DC=COM")
  objContainer.Filter = Array("user")
  For Each objChild In objContainer
      If objChild.SN <> "" Then
        objExcel.ActiveCell.Value = objChild.SN
        objExcel.ActiveCell.Offset(0,1).Value = objChild.GivenName
        objExcel.ActiveCell.Offset(0,2).Value = objChild.Description
        objExcel.ActiveCell.Offset(1,0).Activate                  'move 1 down
      End If
  Next
Next

That should shorten your code a bit... ;^)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
jsctechyAuthor Commented:
Should I replace that code w/ a specific part?  It seems to pull the data 2x.
0
 
sirbountyCommented:
Try this version...

Option Explicit

Dim dtmDate, strMonth, strYear, strFileName, objExcel, objWorkbook, objWorksheet1, objWorksheet2, objRange, objRange2, objContainer, objChild

Const xlAscending = 1
Const xlYes = 1

dtmDate = Date
strMonth = Month(Date)
strYear = Year(Date)

strFileName = "C:\" & "Month_End_" & strMonth & "-" & strYear & ".xls"
'Names XLS file


Set objExcel = CreateObject("Excel.Application")
set objWorkbook = objExcel.Workbooks.Add
'sets objWorkbook to use Excel.Workbooks.Add function
set objWorksheet1 = objWorkBook.WorkSheets(1)
'sets the name objWorksheet1 to Excel.Application.Workbooks.Worksheets(1)
objExcel.Visible = True

objExcel.ActiveSheet.Name = "JSC_Users"                  'names Active Sheet
objExcel.ActiveSheet.Range("A1").Activate                  'Selects A1
objExcel.ActiveCell.Value = "Last_Name"                        'col header 1
objWorkSheet1.Cells(1, 2).Value = "First_Name"                  'col header 2
objWorkSheet1.Cells(1, 3).Value = "Description"                  'col header 3
objExcel.ActiveCell.Offset(1,0).Activate                        'move 1 down

sites=Array("OU=New Jersey","Long Island", "NYC")

For Each site in sites
  Set objContainer = GetObject("LDAP://" & site & ",DC=JSC,DC=COM")
  objContainer.Filter = Array("user")
  For Each objChild In objContainer
      If objChild.SN <> "" Then
        objExcel.ActiveCell.Value = objChild.SN
        objExcel.ActiveCell.Offset(0,1).Value = objChild.GivenName
        objExcel.ActiveCell.Offset(0,2).Value = objChild.Description
        objExcel.ActiveCell.Offset(1,0).Activate                  'move 1 down
      End If
  Next
Next

objworksheet1.Columns.AutoFit()
'Autosize for Column Width for Work Sheet 1

'objWorkSheet1.Range("A2:C300").Activate
'highlight Column A Row2 : Column C Row 200

set objRange = objWorksheet1.UsedRange
set objRange2 = objExcel.Range("A1")

objRange.Sort objRange2, xlAscending, , , , , , xlYes
'Sorts ALL used Cells by Column 1 (A1).  xlAscending, sorts ascending, xlYes means Header Row=Yes

set objWorksheet2 = objWorkBook.WorkSheets(2)
objWorksheet2.Name = "Assentor Users"
objWorksheet2.activate                              'activates Assentor Users Work Sheet

'objWorkBook.SaveAs(strFileName)
'objExcel.Quit

0
 
jsctechyAuthor Commented:
Great, It is working.  
What part of the code below actually removes the rows w/ blank cell in Column A.
__________________________________________________________
For Each site in sites
  Set objContainer = GetObject("LDAP://" & site & ",DC=JSC,DC=COM")
  objContainer.Filter = Array("user")
  For Each objChild In objContainer
      If objChild.SN <> "" Then
        objExcel.ActiveCell.Value = objChild.SN
        objExcel.ActiveCell.Offset(0,1).Value = objChild.GivenName
        objExcel.ActiveCell.Offset(0,2).Value = objChild.Description
        objExcel.ActiveCell.Offset(1,0).Activate                  'move 1 down
      End If
  Next
Next
__________________________________________________________

1 last thing.  If I wanted to change it from blank, to a cell w/ less than 2 characters still in column a, what would be changed?
0
 
sirbountyCommented:
It doesn't remove a blank line.
I was under the impression the code was writing blanks...this wouldn't write them because it would not write anything if the returned record was empty (SN property).
0
 
jsctechyAuthor Commented:
Oh, I see what you mean.  Would I be able to write something like that?  Doesn't neccessarily have to delete the row, if a blank cell is found, but maybe NOT pull the AD data to the spreadsheet, unless the SN has more than 2 characters?
0
 
sirbountyCommented:
Perhaps I don't know what you're trying to do here?
I thought you were writing all this to a spreadsheet, no?
0
 
jsctechyAuthor Commented:
Yes.  The data that gets pulled from AD will be put into a spreadsheet.
However, I have some user accounts that I do not want.  I only want accounts that have more than 2 characters in the SN field.  If they have LESS than 2 characters, I don't want them on the spreadsheet.

I do not want those user accounts because they are not in the DB this spreadsheet will be comapred to later on.
0
 
jsctechyAuthor Commented:
Sirbounty,
Thanks for the help.  So far this part is working, I'm sure I'll be back w/ the SQL queries I'll need.. Once I get the tables names, and whatever else I'll need.
0
 
sirbountyCommented:
Happy to help - thanx for the grade! :^)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.