How do i read an excel sheet rowwise and update it to access datab

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi,

I have the following situation,
I have got an excel sheet, it consists of financial analysis, the first
column is the details and there 4 columns representing the Quarters, as shown
below.

A B C
D E
1 Description Q1 Q2
Q3 Q4
2
3 Sale of item 1 12 22
10 23
4 Sale of item 2 24 10
55 26

these are the kind of data, also inbetween these rows i might have sections.
each row of data is identified by a unique identifier,

My requirement is i need to read the identifier from each row and have
transfer the data for that row and load it to an access table.
How do i read data row by row from the excel sheet.
I tried using the docmd.transferspreadsheet by specifying the range as
"Sheet1!A3:E3" to specify the 3rd row, but nothing happens, no data is loaded.
If i specify "Sheet1!A:E" then all the data from A to E cells are copied for
all rows into the access table.

Is there any way to acomplish the above task?
any inputs will be highly helpful for me.

thanks and regds
Kans.
 
Some sample code that may get you started (you can add loops to this code to
loop through each worksheet in a book if you'd like). This code opens an
EXCEL workbook and reads data from it and writes the data into a recordset.
You can modify this as needed to do things directly on the spreadsheet, etc.


Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
Set xlw = xlx.Workbooks.Open("C:\Filename.xls"), , True
Set xls = xlw.Worksheets("WorksheetName")
Set xlc = xls.Range("A1")
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("TableName", dbOpenDynaset, dbAppendOnly)
Do While xlc.Value <> ""
rst.AddNew
For lngColumn = 0 To rst.Fields.Count - 1
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
Next lngColumn
rst.Update
Set xlc = xlc.Offset(1,0)
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Set xlc = Nothing
Set xls = Nothing
xlw.Close False
Set xlw = Nothing
xlx.Quit
Set xlx = Nothing
 
hi Ken,

This piece of code is surely goin to take me in the right direction.
Thanks a lot for this wonderful help.

regds
Kannan.V
 
Back
Top