Reading all rows of a specific column in excel with VB Script

Question

I have a spreadsheet that has all of our customer information. So we have Customer 1,2,3,4,5 and each customer has an EMAIL_ADDRESS column with one or more email addresses. I need to read all of those addresses and output them in some form or fashion. We have column AJ labeled Email_Addresses and I need to pull all of the emails for every Customer or Row.

I found this code

Dim xlApp 
Dim xlBook 
Dim xlSheet 
Set xlApp = CreateObject("Excel.Application") 
xlApp.visible = true 
Set xlBook = xlApp.Workbooks.open("D:\Macro\BirthDay.xls") 
Set xlSheet = xlBook.Worksheets("Sheet1") 
MsgBox  xlSheet.Cells(1, A1).Value

However my question is how do I get this or some code to read ALL ROWS in Email_Address Column AJ.

Where should the email addresses be outputted e.g. file, webpage and etc...? — WorkSmarter, Apr 09 '15 at 14:19
It can be outputted to a text file, to a AS400 table... I am more concerned with pulling in everything in the 'AJ' or Email_Address columnfor all customers — Alaxander, Apr 09 '15 at 15:39
I actually now have the file down to two columns. Customer # and Email_Addresses. Sometimes a customer may have 3 email addresses separated by semi-colons — Alaxander, Apr 09 '15 at 15:54
I need to say that my big issue now is figuring out how to separate multiple email addresses from a column... So customer1 has aa@aa.com;bb@aa.com in their Email_Address column, but when I do my output how could I even separate those? — Alaxander, Apr 09 '15 at 18:35
use the split command to separate the email addresses delimited by a semi colon. Take a look at this article for more information http://www.w3schools.com/vbscript/func_split.asp — WorkSmarter, Apr 12 '15 at 07:32

score 5 · Answer 1 · edited Nov 19 '17 at 08:39

'ReadExcel Using Search                    
Set objExcel = Wscript.CreateObject("Excel.Application")   
Set objWorkbook = objExcel.Workbooks.Open("D:\VBScriptTrainee\Email.xlsx")   
objExcel.visible=True
rowCount=objExcel.ActiveWorkbook.Sheets(1).UsedRange.Rows.count
colCount=objExcel.ActiveWorkbook.Sheets(1).UsedRange.Columns.count  
Msgbox("Rows    :" & rowCount)
Msgbox("Columns :" & colCount) 
a=inputbox("Enter the serial number","Search") 
 intRow = 2
 intCol = 2
  for intRow=2 to rowCount  step 1
     if ( CInt(a) = CInt(objExcel.Cells(intRow, 1).Value) ) then        
       for intCol=1 to colCount step 1  
           c = c & "    " & (objExcel.Cells(intRow, intCol).Value) 
        next 
           sp=Split(c,";")
            b=ubound(sp)
         for i=0 to b
            Msgbox(sp(i))
         Next
     End if
        c=null
  next
'objExcel.Quit

Please add some explenation to your answer. – André Kool May 17 '16 at 09:55 — André Kool, May 17 '16 at 09:55

score 1 · Answer 2 · answered Apr 14 '15 at 19:55

row_cnt = objSheet.UsedRange.Rows.Count
MsgBox "number of rows is --> " & row_cnt
col_cnt = objSheet.UsedRange.Columns.Count
MsgBox "number of columns is --> " & col_cnt

For i = 1 To row_cnt
     value = objExcel.Cells(i,column_number_having_emailids).Value
     no_of_emailid = Split(value,";")
    for j = 0 to UBound(no_of_emailid)
        MsgBox no_of_emailid(j)
    Next
Next

try something like this

Reading all rows of a specific column in excel with VB Script

2 Answers2