?
Solved

VBScript, Excel, Delete empty rows containing empty cells

Posted on 2007-04-04
12
Medium Priority
?
18,983 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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 2000 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Introduction to Processes

770 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