Importing data into Access from Excel 1

[meumax]

I have an Excel worksheet which has data I'd like to import into Access. Problem is that the data starts at around row 900 with the top part being filled with statistics on the data below.

So... I wrote a nifty little Excel macro to parse through the file until it gets to the top left cell of the data, puts the cell address into a variable then finds the bottom right cell of the data and puts its cell address into another variable. I've message boxed out the results and it all works fine.

The problem is when I try using the same code through an Access module. Basically this is what I want my module to do.

1. Create an instance of Excel
2. Run the aforementioned code putting the top left and bottom right cell addresses into two variables
3. Use the TransferSpreadSheet function to import the data into Access using the two variables as the range to import

Here's the code:

Sub GetData()
On Error Resume Next
Dim oXL As Object
Dim Range1, Range2 As String
Set oXL = CreateObject("Excel.Application&quot 'Create an instance of Excel
With oXL.Application
.Visible = False
.Workbooks.Open "licences.xls"

For i = 1 To 65536 'Find the top left cell
.Range("A" & i).Select
If .ActiveCell = "Microchip Number" Then
Range1 = .ActiveCell.Address 'Put cell address into a variable
ActiveCell.End(xlToRight).Select 'Go to the far right of the data
ActiveCell.End(xlDown).Select 'Go to the bottom of the data
Range2 = .ActiveCell.Address 'Put the bottom right cell address into another variable
Exit For
End If
Next i

.Activeworkbook.Close
.Quit
End With
Set oXL = Nothing
MsgBox (Range1 & " " & Range2) 'Output the result
End Sub

It doesn't work like in Excel. Range2 takes on the same address as Range1. E.g. I get the output: $A$891 $A$891 instead of $A$891 $Y$5200. Why if it works when I run the code as a macro in Excel doesn't it work when I'm running the same code through Access?

 

[Darrylles]

Hi meumax,

Sounds silly, but you have:

Dim Range1, Range2 as String.

Try:

Dim Range1 as String
Dim Range2 as String

Might be a 'type thang'.
Fingers crossed.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[meumax]

No, it still returns the same address as range1. I have narrowed down the problem however.

The code seems to be ignoring the lines:

ActiveCell.End(xlToRight).Select 'Go to the far right of the data
ActiveCell.End(xlDown).Select 'Go to the bottom of the data

I know this because I added the line ".Activeworkbook.Save" before I close the workbook. When I open the file in Excel, the active cell is on the "Microchip number" column header.

The code ignored my command to move to the far right then down to the bottom of the data. Now I have to figure out why that is.

 

[RobBroekhuis]

Again, this may be trivial, but at least in your post you have

ActiveCell.End(xlToRight).Select

instead of

.ActiveCell.End(xlToRight).Select

If this is also true in your code, that's probably your problem.
Rob

 

[RobBroekhuis]

You may want to explore the following faster method of determining your range:

dim cell as excel.range, sRange as string
...
set cell=.range("A:A&quot.find ("Microchip Number&quot
if cell is nothing Then
msgbox "Not found"
else
sRange=.range(cell,cell.end(xltoright).end(xldown)).address
msgbox sRange
endif

Rob

 

[meumax]

Thanks for the tips Rob,

The code you gace me still has the same problem as mine though...

When it gets to the line where it assigns the variable sRange the value .range(cell,cell.end(xltoright).end(xldown)).address it doesn't do it properly and I end up with a blank message box.

I know the LOGIC of both our codes works because if I put in a line which assigns sRange the value "Something" instead of .range(cell,cell.end(xltoright).end(xldown)).address at that point, the message box returns "Something".

I am beginning to think that perhaps Access does not like xlToRight and xlDown (possibly because they are Excel specific constants). In which case it means two long winded For loops to replicate their functionality.

 

[RobBroekhuis]

Aha! You hit the nail on the head, I believe. So just use:

sRange=.range(cell,cell.end(-4161).end(-4121)).address

Rob

 

[meumax]

Yes! That did the trick.

But how did you find out what values to use in place of those constants?

 

[TomCarnahan]

I know you already have your solution, but I encourage you to "think outside the box". I have a similar application that works back and forth between Access and Excel. On the Excel side, I use code something like you to find the upper-left and lower-right. But then my Excel code "selects" the range, and names it. When I am in Access using the TransferSpreadsheet function, I merely feed the "named range" to the function ... saves a lot of hassle.

Hats off to the person that was able to answer your specific question ... just sounds like a lot of work to do it in Access.

Cheers! -- Tom --- Tom

 

[meumax]

That was in fact the original way the application worked except that the users had to select the range manually and name it. Then they pressed a button on an Access form to import the data.

I wanted to give the users a solution which would allow them to just use the Access application and not have to do anything Excel.

 

[RobBroekhuis]

Meumax,
Just go into the VBE for Excel, and get the values from the immediate window:
?xlToRight
?xlDown
It's not pretty - would be better to use constants in your Access code (you could even name them the same, since they are not known to Access VBA):

const xlToRight=-4161, xlDown=-4121


Rob