Retain format of tables in from excel to access forms
I have a form built where I would like to take information that is formatted in Excel what i mean is reduced to only show the most important information for example I have a spreadsheet with 42 elements or fields but I only want to only show 5 elements so we pull out the data we want then I want to copy and paste that data in to a form I have in access which is part of a report. When I paste the information from Excel I lose all formatting ie columns so all my data is squished together in my Access form. How do keep the columns i see in the Excel sheet to remain in Access see screen images. 
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
screenshot
untitled.JPG
untitled.JPG
...so in this case you would do as jmoss111 suggested and "Link" the Excel data into Access:
External data-->Import and Link-->Excel-->Link...-->.
... and use that as the source for the listbox.
There is a listbox wizard to walk you through the steps of creating the listbox
JeffCoachman
External data-->Import and Link-->Excel-->Link...-->.
... and use that as the source for the listbox.
There is a listbox wizard to walk you through the steps of creating the listbox
JeffCoachman
ASKER
I tried both plain text and rich text, rich text separated the text too much. it added what look liked too many columns
...and what about the listbox approach?
ASKER
I am trying it see if it will work since there are multiple records associated with the database, i am trying tom come up with a solution that is easy and requires little user input or additional steps, it is one thing for me to get data in but this is being designed for people who are not very computer savvy. I will work on it this morning.
ASKER
well yes and no, yes I can get the data into the form via an excel link, but the data does not update into the source table so when I run a report of do an update there is not data.
so far the only thing that seems to sort or work is to create a separate report and insert that in my final report as sub report but that really does not work either since if the sub report grows it cover other data in the main report.
so far the only thing that seems to sort or work is to create a separate report and insert that in my final report as sub report but that really does not work either since if the sub report grows it cover other data in the main report.
ASKER
Maybe there is a different approach :
Is there function that would align the text to the left for example if I entered in Excel: So for example I could enter a function that would align data from C17 to the left so that things were ordered.
=CONCATENATE(A17," ",B17," ",C17," ",D17," ",E17)
roger 445 78675 756 4635
roger 446 465 75 234
roger 447 46 67 234
roger 448 876 45534 7
Is there function that would align the text to the left for example if I entered in Excel: So for example I could enter a function that would align data from C17 to the left so that things were ordered.
=CONCATENATE(A17," ",B17," ",C17," ",D17," ",E17)
roger 445 78675 756 4635
roger 446 465 75 234
roger 447 46 67 234
roger 448 876 45534 7
It is still not clear what the entire purpose of this system is.
Your original question was regarding "Formatting", ...now you bring up "updating source data"
So can you First, take a step back and explain to us in detail what you are trying to accomplish here...?
Your original question was regarding "Formatting", ...now you bring up "updating source data"
So can you First, take a step back and explain to us in detail what you are trying to accomplish here...?
ASKER
Well my apologize for using the word formatting as that seems to be a hang up.
I have build a database with a navigation page, a user can open the database and the navigation page will open here the user will select a project by selecting a project this build a table (the source data, (ReportSelectionTable) the user then goes through the remaining navigation pages and enters data , once completed the user clicks and update button that update the main database table (ReconData)to store the data for later use. Simultaneously a series of report are generated using the source data (ReportSelectionTable), that can be exported to a PDF.
i would like to be able to copy an paste certain data with multiple fields (assays or land records) into the records in the forms which should be populating the source data table (ReportSelectionTable)
Again I am trying to build something that is for people whom are not used to working in access so i need to be as simplistic as possible.
let em know if this does not clarify what I am trying to do.
I have build a database with a navigation page, a user can open the database and the navigation page will open here the user will select a project by selecting a project this build a table (the source data, (ReportSelectionTable) the user then goes through the remaining navigation pages and enters data , once completed the user clicks and update button that update the main database table (ReconData)to store the data for later use. Simultaneously a series of report are generated using the source data (ReportSelectionTable), that can be exported to a PDF.
i would like to be able to copy an paste certain data with multiple fields (assays or land records) into the records in the forms which should be populating the source data table (ReportSelectionTable)
Again I am trying to build something that is for people whom are not used to working in access so i need to be as simplistic as possible.
let em know if this does not clarify what I am trying to do.
Now I am totally confused.
This seems way to complicated...
Are you quite sure that creating tables on the fly and updating date via buttons is the best interface? (what happens if the user forgets to click the button? What happens if the user clicks the button twice by accident, ...etc)
How much do you know about database design?
It is still not clear what you one direct question is here...
I may have to back out now, perhaps one of the other Experts will be able to assist.
JeffCoachman
This seems way to complicated...
Are you quite sure that creating tables on the fly and updating date via buttons is the best interface? (what happens if the user forgets to click the button? What happens if the user clicks the button twice by accident, ...etc)
How much do you know about database design?
It is still not clear what you one direct question is here...
I may have to back out now, perhaps one of the other Experts will be able to assist.
JeffCoachman
ASKER
Wow sorry !!!!!
if you want to back out okay
I did not mean to cause problems I thought that is what this site was for to help figure things out.
I am not sure I have been creating databases for a few years now, maybe there is a better interface I am open to ideas.
If the user forgets to click the button the data is stored in the temp table and the use can go back and click the button again to populate the main table, if the use clicks twice it just check for any new updates.
All i want to do is allow a user to paste information into a field and not have all the text looking likethissothatthereisnospa
if you want to back out okay
I did not mean to cause problems I thought that is what this site was for to help figure things out.
I am not sure I have been creating databases for a few years now, maybe there is a better interface I am open to ideas.
If the user forgets to click the button the data is stored in the temp table and the use can go back and click the button again to populate the main table, if the use clicks twice it just check for any new updates.
All i want to do is allow a user to paste information into a field and not have all the text looking likethissothatthereisnospa
ASKER
No I am sorry there must not be a way for me to do what I would like to do Or very possibly I am not explaining it very well I apologize for you have to spend time on this I don't know if this is PC but here is the data base maybe that would help the you see what I am after. I am trying to go into 05_Mineralization and copy from Excel data and paste in to that one field.
I under the comments above that I am trying to put 5 or 6 fields in to one i thought there just might be a way a simple way to get the data parsed(if that is the correct word) or deliminated so that the strings did not all run together. i will have to come up with a different solution.
Thanks again.
ReconReport-testing.accdb
I under the comments above that I am trying to put 5 or 6 fields in to one i thought there just might be a way a simple way to get the data parsed(if that is the correct word) or deliminated so that the strings did not all run together. i will have to come up with a different solution.
Thanks again.
ReconReport-testing.accdb
ASKER
I am either not making sense or what i am asking is not possible, I will close this so as to not waste anyone time.
...ok
Is it's Text Format property set to Rich Text?