Link to home
Start Free TrialLog in
Avatar of Fordraiders
FordraidersFlag for United States of America

asked on

Importing Data from excel in a defined range but rows undetermined

Excel 2010 vba

What I have:
I have a defined range that i need to import data into an Access table
In the excel data  the range is starting at  Column "K row 5  TO  S"
the rows in that data range are never the same


What I need:
I need to make sure the code captures and imports K5 to  S(whatever)

' Replace WorksheetName with the actual name of the worksheet
' in the EXCEL file
Set xls = xlw.Worksheets("WorksheetName")

' Replace K5 with the cell reference from which the first data value
' (no-header information) is to be read
Set xlc = xls.Range("K5") ' this is the first cell that contains data  and Goes to  S5



Set dbs = CurrentDb()

' Replace QueryOrTableName with the real name of the table or query
' that is to receive the data from the worksheet
Set rst = dbs.OpenRecordset("QueryOrTableName", dbOpenDynaset, dbAppendOnly)

' write data to the recordset
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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Fordraiders

ASKER

Thanks John, Another question to follow.
John, Would this be close then.
With xls
            .Columns.AutoFit
            While Len(.Cells(11, lastColumn)) > 0
                lastColumn = lastColumn + 1
            Wend
            While Len(.Cells(lastRow, 1)) > 0
                lastRow = lastRow + 1
            Wend

            For i = 1 To lastRow
                For j = 1 To lastColumn
                 
                 'Here you put code to edit the recordset and map Fields to Cells
                '     Trim (.Cells(i, j).Value)
                 rst.AddNew
                    For lngColumn = 0 To rst.Fields.Count - 1
                        rst.Fields(lngColumn).Value = Trim(.Cells(i, j).Value) 'xlc.Offset(0, lngColumn).Value
                    Next lngColumn
                 rst.Update
                Next ' last column Next
            Next ' lastrow Next

End With ' xls With
I think it looks good...do some testings and to ensure that nothing is missed ...
John, How are you defining the     "lastcolumn"  variable  ?