import a range in a column to an access table

[Guest]

Hi,
How do i import a range of cells (e1,e7,e13,e19,e25) to an access table.
I don't need to import the whole worksheet only particular cells.
I can get a session of excel thru automation and get to the worksheet, but
want to select a range and import it to a temporary table or recordset to
manipulate the data. The data is in the cells and on the worksheet and can
alter so this is why I was wondering how to get this particular range when it
is needed.

 

[John Nurick]

Hi Grunt,

Having launched Excel and set an object variable oWks to point to the
worksheet - you can do stuff like this:

Dim oRange As Excel.Range
Dim rsR As DAO.Recordset
Dim arIndices As Variant
Dim j as Long

Set rsR = CurrentDb.OpenRecordset("TempTable")

'Specify the range and the cells within that range
Set oRange = oWks.Range("E1:E25")
arIndices = Array(1,7,13,19,25)

rsR.AddNew
For j = 0 to UBound(arIndices)
rsR.Fields(j).Value = oRange.Cells(arIndices(j))
Next j
rsR.Update

rsR.Close

 

[Guest]

Hi John,
Slight Glitch.
Table is called "PowderTable" it has 22 feilds and is existing.
Ended up with 22 cells in the range to be copied to the table.

I have the following,

Dim xlapp As Excel.Application
Dim xlbook As Excel.Workbook
Dim ExcelRunning As Boolean
Dim oRange As Excel.Range
Dim rsR As DAO.Recordset
Dim arIndicies As Variant
Dim j As Long

Select Case Frame149

Case 1 ' 2 tonne

ExcelRunning = IsExcelRunning
If Not ExcelRunning Then
Set xlapp = CreateObject("Excel.Application")
Else
Set xlapp = GetObject(, "Excel.Application")
End If
'Open worksheet.
Set xlbook = xlapp.Workbooks.Open("C:\Database\25KgPowder\2TPH.xls")

Set rsR = CurrentDb.OpenRecordset("PowderTable")

'Select the range which is constant for all 4 plant capacities.
'Specify the range and the cells within that range
Set oRange = xlbook.Sheets(1).Range("L79:L268")


arIndicies = Array(79, 87, 103, 109, 115, 120, 125, 130, 136, 144, 149,
156, 163, 168, 176, 181, 186, 192, 253, 258, 263, 268)

rsR.AddNew
For j = 0 To UBound(arIndicies)
rsR.Fields(j).Value = oRange.Cells(arIndicies(j))
Next j
rsR.Update

rsR.close

I've stepped thru the routine and it appears to be working.

When I open the Table to check if anything has happened, I get a random
figure in a feild that doesn't relate to the xl sheet or any cells - I
couldn't find a match for the value when I searched the original worksheet.

With each attempt I got a new line in the table and either one or two feilds
populated with information that has no baring on whats in the original xl
sheet.

Any ideas where I've gone wrong?

 

[John Nurick]

You're creating a range variable pointing to "L79:L268", and then
setting the first element in arIndices to 79. So when j = 0 and you do
this
rsR.Fields(j).Value = oRange.Cells(arIndicies(j))

the value of arIndicies(0) is 79 and you're setting the first field in
rsR to the value of the 79th cell in oRange.

Solutions include:
-make your range L1:L268

-adjust the values in arIndicies() by subtracting 78 (e.g. 1, 9...)

-make the adjustment automatically, e.g.
Dim lngVertOffset As Long

...
lngVertOffset = oRange.Rows(1).Row - 1
...
rsR.Fields(j).Value = oRange.Cells(arIndicies(j) - lngVertOffset)
...

 

[Guest]

Cheers John.
Will do.
Many thanks, really appreciated.

 

[Guest]

Hi John,
Took the easy solution and extended the range.
I thankyou sincerely.