Fordraiders
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)
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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).Valu e = 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
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).Valu
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 ...
ASKER
John, How are you defining the "lastcolumn" variable ?
ASKER