CODEWITHSUNDEEP
vbscript
Alaxander
Alaxander

Reputation: 31

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

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.

Upvotes: 3

Views: 50115

Answers (2)

Sebastin Anthony
Sebastin Anthony

Reputation: 660

'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

Upvotes: 5

user3331923
user3331923

Reputation: 49

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

Upvotes: 1

Related Questions