Solved

VBScript, Excel, Delete empty rows containing empty cells

Posted on 2007-04-04
12
18,737 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:jsctechy
  • 7
  • 5
12 Comments
 
LVL 67

Expert Comment

by:sirbounty
ID: 18851657
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
 
LVL 67

Expert Comment

by:sirbounty
ID: 18851683
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
 
LVL 1

Author Comment

by:jsctechy
ID: 18852050
Should I replace that code w/ a specific part?  It seems to pull the data 2x.
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18852185
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
 
LVL 1

Author Comment

by:jsctechy
ID: 18852210
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
 
LVL 67

Expert Comment

by:sirbounty
ID: 18852226
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:jsctechy
ID: 18852263
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
 
LVL 67

Expert Comment

by:sirbounty
ID: 18852355
Perhaps I don't know what you're trying to do here?
I thought you were writing all this to a spreadsheet, no?
0
 
LVL 1

Author Comment

by:jsctechy
ID: 18852373
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
 
LVL 67

Accepted Solution

by:
sirbounty earned 500 total points
ID: 18852434
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
 
LVL 1

Author Comment

by:jsctechy
ID: 18852500
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
 
LVL 67

Expert Comment

by:sirbounty
ID: 18852550
Happy to help - thanx for the grade! :^)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This is an explanation of a simple data model to help parse a JSON feed
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now