Access help II

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

kromes

Thread Starter
Joined
Jun 12, 2005
Messages
13
I am trying to import a spread sheet in Access, I have a macro setup to transfer spread sheet. I have two problems, one is when I run the macro i get a message that the file is already open or i do not have permission. The file is not open and it is a personal pc? Next when I am able to run the macro on another pc one column does not import correctly - as text. The spread sheet is set up as text and the table i am importing it to is also setup as text. But when I import the date i only get the numeric data and not the text data?
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
Can you give an example of exactly what you put in and exactly what's coming out?

I mean... look at one of the cells in Excel, and look up at the formula bar to see its value.

Then tell me what's coming out in Access?
 

kromes

Thread Starter
Joined
Jun 12, 2005
Messages
13
I import A spread sheet, A column in that spread sheet has order numbers, some are numeric, and others are alpha numeric. The column (in excel) is formated as text. ( although the #s are right justified and the alpha#s are left justified.) When the macro runs the records are imported, however the order # (alpha#'s) do not import. Is this the info you need?
 
Joined
Oct 2, 2001
Messages
1,227
I do not follow the purpose of the macro (Access macro?). I have imported data lists in Excel to tables in Access many times using
File/Get External Data/Import...
I then bring it into my existing database as a new table or append to an existing table.

You may be having trouble with data types. For example, the dates you have in Excel may need to be converted to text before importing to Access. Then change that field to the Date/Time data type. You should not get a numeric value for the dates you imported if they were truly changed to text.
 
Joined
Jun 30, 2005
Messages
1
In order to import an Excel file that has mixed alpha numeric you have to sort the excel column with the mix, I cant remember which has to be first, I believe it's the numeric. Are all the data items the same length in the mixed field?
 

kromes

Thread Starter
Joined
Jun 12, 2005
Messages
13
wolfworx - I setup a macro which will be on a switchboard, it will be run with a command button. I did this so other users can import the data, then view and/or print the data for specific areas. They are not able to import the data them self's.
 
Joined
Oct 2, 2001
Messages
1,227
Interesting. It would be a good application to front end using Visual Basic. You could put code behind a form to do just what you want the macro to do.
 
Joined
Jul 29, 2001
Messages
21,334
Access will ignore the format in Excel and bring in the data and format it based on the first few rows in the column. There was a way to import it so Access disregards the rows and bring it in as is. I think you need to put a ' in from of every item to get it to work. I believe this can also be done in a query if you link directly to the spreadsheet.
 

kromes

Thread Starter
Joined
Jun 12, 2005
Messages
13
Ok - I tried sorting the column with the text first, text in the column was ok but then the numbers would not improt as text. I teried linking and ran into the same problem until I opened the spread sheet slected the column, and from the data menu selected text to columns, in step 3 i selected text. Now the linked data to the table is text.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top