data not importing - help!!

[TechyTemp]

I have already set up tables in access and want to import specific named
ranges into specific tables. The "show named ranges" option doesn't show the
named ranges I created and the ones it does show, the Microsoft jet database
engine can't find. It says to make sure the object exists and that I spelled
its name and path correctly.
What does that mean? Is there something else I should have done when
creating the named ranges other than hi-liting the cells and entering the
range name in the name box?

 

[Gina Whipp]

TechyTemp,

Did you set up Import Specifications under the Advanced tab, is that where
you set up the 'named ranges'? Are you using a TransferSpreadsheet method
or are you just using File | Import? Please provide a wee bit more info...

 

[TechyTemp]

Gina Whipp,

I set up the named ranges by hi-liting the specific areas and creating a
name in the name box then hitting enter. I was just using the file/import.
What is the transfer spreadsheet method?

Techy Temp

 

[Gina Whipp]

TechyTemp,

First cup of coffee, so bear with me....

1. You are using Access, what version?
2. Are you trying to import an Excel spreadsheet or some other type of data?
3. What and where is this??? "I set up the named ranges by hi-liting the
specific areas and creating a name in the name box then hitting enter..."

Remember, no one here can see what you are doing and "specific area" doesn't
tell much, going to need more detail. When I want to import I have to go
File|Get External Data|Import OR if I'm using Excel I might set up Import
Specifications after I convert the file to .txt or .csv

 

[TechyTemp]

Gina Whipp,

First let me say 'thank you' for helping me with this.

1. I'm using Access 2003
2. Yes, I'm trying to import an excel spreadsheet but not all at one
time....just portions at a time into specific access tables I've set up.
3. The named ranges are in the excel spreadsheet. The tables I have set up
in Access are "Customer", "Monthly Open Orders", "Order Detail", "Orders",
"Profit", "Shipping" and "Shipping Detail". The ranges in excel are hilited
columns that pertain to these access tables. I followed the directions in
excel to name the ranges.

The method for importing I was using was the File|Get External Data|Import
method but none of my ranges appeared in the box. What is 'set up Import
Specifications' ? Is it necessary to convert the file to .txt or .csv? and
what is that exactly?

Techy Temp


TechyTemp

 

[Gina Whipp]

TechyTemp,

So when you go to Import the speadsheet, do you see a check box that says
show worksheet, try that one? If you select that then Next should give you
the option 'First row contains column headings'. You will get an
opportunity towards the end to import the data to a specific table. I have
never used the named ranges so that might be where I'm getting confused.

As for Import Specification, are you planning on doing this import on a
weekly basis? If yes, then you might want to look-up Import Specifications
in Access, then it would just be the push of a button on a form.

I find it's easier to import an Excel file if I do a 'Save As' .csv (in
Excel) but it's not necesary. Another thing you should note, if you have
calculated 'cells' in Excel they may not come into Access the same way.

 

[TechyTemp]

btw.....the named ranges in excel correspond to the table names in
access....is that a problem?

 

[Gina Whipp]

As I said earlier, I never used Named Ranges to know, I always used Fil|Get
External Data|Import or TransferSpreadsheet. Perhaps someone, who used
Named Ranges,might see this thread and offer some advice.

 

[TechyTemp]

How do you use the transferspreadsheet method? I'm open to another
way...lol...I just have never used that method and don't know how it's done.

Techytemp

 

[Gina Whipp]

I see you can using in VBA:
DoCmd.TransferSpreadsheet acImport, 3, "Employees","C:\Lotus\Newemps.wk3",
True, "A1:G12"

I just don't know how to use it any other way.

 

[Gina Whipp]

I sent one sample in the next answer but to get a detailed explanation, see
Microsoft Access help for TransferSpreadsheet, select the VBA method.

 

[TechyTemp]

Please pardon my ignorance.....is that a command code?

 

[Gina Whipp]

Pardon me for not explaining... Yes it would go behind a Command Button or
be placed somewhere behind a form where the code would run, like on the
opening of a form.

I also have been doing searches in Microsofts' group for 'named ranges and
keep coming up with Excel but nothing for Access... I'm on a personal
mission now

 

[Gina Whipp]

I found this link and it looks like it answers how to use 'Named Range':
http://help.lockergnome.com/office/Dynamic-Named-Range-Worksheet-ftopict189835.html

Watch out for word wrap.

 

[John Nurick]

Hi Gina and TechyTemp,

Normally there's no problem importing data from a named range in an
Excel workbook.

But the range must refer to a contiguous range of cells: if you've
defined ranges with gaps in them (e.g. selecting columns A, B, D and E)
they won't show up in the Access import wizard. Could this be the
problem?

 

[Gina Whipp]

John,

Thanks for insight. Hopefully, TechyTech is still watching this thread.