Hi: Can any one please tell me how to i read the excel file from column A2 to AA2 into a array. I want to read upto the line when it starting blank rows as there is no blank rows between the records. I atleast open the excel file but dont know after that how to i read the file one by one or creating an array?
Private Sub readExcelFile()
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim rngColumn As Excel.Range
Dim rngCell As Excel.Range
Dim strDestination As String
Dim mysheet As String
Dim lngRow As Long
Set xlApp = New Excel.Application
strSource = "C:\Remit\Remitence.xls"
Set xlWb = xlApp.Workbooks.Open(strSource)
mysheet = xlApp.Worksheets(1).Name
Set xlWS = xlWb.Worksheets(mysheet)
'Now Read records one by one and pass into an array from Column A2 to Column AA2 as first row is header
Are you aware that you can connect to an excel spreadsheet using ADO, and construct a recordset of the data in the spreadsheet? (as well as inserting new records directly into the wprksheet...) This approach is much faster than automating an Excel spreadsheet.
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert