Loop through and get each cell Data

[hsingh1981]

Hi all,

I have a problem, I can open an excel sheet and retrieve a paticular cell data and store it to my table. How do i tell it to go through each cell and move down and store it excel table?

eg.
a9
a10
a11
and retrieve the data in each cell and save it to table?

This is what i got so far with one cell

DoCmd.SetWarnings False
DoCmd.Hourglass True
Dim cFso

Set cFso = CreateObject("Scripting.FileSystemObject")
Dim MyProperPath As String
Dim myRec As DAO.Recordset
Dim xlApp, xlApp2 As Excel.Application
Dim xlWrksht, xlWrkshtBBE As Excel.Worksheet

'Name of Table
Set myRec = CurrentDb.OpenRecordset("T_LOS")
'Excel sheet creat and finding path
Set xlApp = CreateObject("Excel.Application")



'Make sure the path is in the right format
MyProperPath = txtExcelFileLocation


Set xlWrksht = xlApp.Workbooks.Open(MyProperPath)




'MsgBox MyFileName
myRec.AddNew

myRec.Fields("HRGs") = xlWrksht.Worksheets("Length of Stay").Cells(9, "A")


myRec.Update

xlApp.Workbooks.Close
DoCmd.SetWarnings True

'Me.Combo14.Requery

DoCmd.Hourglass False

Many thanks

 

[ck1999]

Using a loop

'MsgBox MyFileName
for counter = 9 to 11
myRec.AddNew

myRec.Fields("HRGs") = xlWrksht.Worksheets("Length of Stay").Cells(counter, "A")


myRec.Update
next counter

there is also a fact to determine last cell used in a column if the range keeps changing.


ck1999