I need to create a name range in Excel, so I can import the data into Access. The reason that I'm going to need the name range is because the data is scattered, so my ranges will be like "A1:D30,A35:D38" etc. As far as I'm aware the only way that you can import data into Access like that is by defining a named range, I can't just import the actual ranges separated by commas.

What I'm thinking I'm going to have to do is to define the name in Excel and then save the workbook and import the newly saved range. Sounds, pretty simple, not su much. I keep getting an error that the object can't be found. Does anyone know how I can accomplish this or even a better approach that I could take?

This code will go to A1 - then go down to the last row of the worksheet to get the range needed. I have data sets that will always be 21 columns wide - I just dont know how many rows.
Hope this helps -

Thanks for the response but that's Excel code and it looks like it's just really picking up one one range and setting a name, e.g. A1:C21.

I have a function in Access that creates several ranges that would be like below.

A1:H35
A38:H43
A48:H71

I need to create a name for those ranges, so I can import into Access. Doing this in Excel itself is fairly easy to accomplish, however in Access, I just can't get it to set the range correctly or import with that range. Right now, I have it setup, so it does several imports into the table and bases this on a Select Case statement to determine which ranges to grab. I know this is definitely not the best way to do this and theres a better way, which would be to just simply define a named range, so it would be dynamic and not hardcoded like that.

Any suggestions?

Hello,

I am not sure if you can import a named range that contains several areas...

In any case, you can always automate Excel and use DAO or ADO to append the values to the target table - it might take more time but your ranges don't seem to be so huge...

If you need help, please post a sample of your worksheet with the names and a mdb with the target table... it is not quite a trivial task and I wouldn't want to throw you air-code

Martin

Martin,

Thanks for the response. It is possible to import several areas that are defined in a single range. I think what you might be referring to is importing it into Access something like this.

A1:C15, A10:C23, A28:C38

In that regard I believe you're correct but if you define it as a named range then it can be done, the problem is defining the name from Access, which you wouldn't think would be too difficult to do but it's proving challenging.

The way you're suggesting would be similar to how I'm doing it now with the several different import actions occurring but I think the way that I'm doing it now with the import might be a little more efficient then appending the values to a table though I could be wrong. Either way, I think we can both agree that both ways are definitely not the most efficient ways to go.

I know theres a way to do this, the trick like everything else is figuring out the "How"

Darn, Think I'm going to have to reevaluate this now. Theres databases at work that are bringing in named ranges that are set in Excel and I thought that these were multiple cell refs because of the format of the files that we get. I just ran a quick test to see if I could import a range from Excel that has multiple cell refs and it doesen't show up as an option in the Access import wizard as a named range.

Why is it difficult? What is the code you're using to create the name?

Larry,

I'm at home right now, so I don't have the code. If it's not going to be possible, I'm not going to need to define a named range. The function that I have works great, it sets the ranges to import. I was just hoping that there would be a more efficient way to do it. Thanks, for your assistance.