[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBScript, Excel, Delete empty rows containing empty cells

Posted on 2007-04-04
12
Medium Priority
?
19,115 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
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

Independent Software Vendors: 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!

Question has a verified solution.

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

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
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…
Progress

873 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