Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Retain format of tables in from excel to access forms

Posted on 2011-10-19
20
Medium Priority
?
378 Views
Last Modified: 2012-05-12
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. original data excel format access format notice the lack of fromatting
0
Comment
Question by:briancellura
  • 8
  • 8
  • 2
  • +2
20 Comments
 
LVL 36

Expert Comment

by:Norie
ID: 36994157
What type of field/control is it you are pasting into?

Is it's Text Format property set to Rich Text?
0
 
LVL 77

Accepted Solution

by:
peter57r earned 250 total points
ID: 36994385
I would say that this is impossible without using substantial code to build a formatted string for each line of the Excel table.  

To see the current information in an Access control you would have to save the required data as its own workbook and then use an OLE Object control in the Access form to Insert the workbook into it.
0
 
LVL 18

Assisted Solution

by:jmoss111
jmoss111 earned 250 total points
ID: 36994415
you are pasting 6 columns from Excel into 1 column in an Access table; you'd be much better off if you would import or link the excel file into the Access database.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 36

Assisted Solution

by:Norie
Norie earned 250 total points
ID: 36995078
If you are just pasting this manually if it's a textbox with the Text Format set to Rich Text you will retaiin some formatting.

Not much though - maybe the number formatting but not much else.

Why not actually import the data into Access and create a 'real' report?
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
ID: 36995747
The Access screenshot seems to display this data in a "text File" type output.

The look you are after can be achieved if the data were in a "Listbox" (would look like what you have in Excel, with divisions between the columns)

Sample attached

Also what you are referring to as "Formatting" seems to be really the Spacing of the fields,(all my data is squished together in my Access form.)  not really "Formatting" (Font color, Bold, Italic, ..etc)

JeffCoachman
Database49.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36995779
screenshot
untitled.JPG
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36995844
...so in this case you would do as jmoss111 suggested and "Link"  the Excel data into Access:
External data-->Import and Link-->Excel-->Link...-->...etc
... 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
0
 

Author Comment

by:briancellura
ID: 36996401
I tried both plain text and rich text, rich text separated the text too much.  it added what look liked too many columns
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36997391
...and what about the listbox approach?
0
 

Author Comment

by:briancellura
ID: 36999277
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.
0
 

Author Comment

by:briancellura
ID: 36999968
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.  
0
 

Author Comment

by:briancellura
ID: 37000573
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37000723
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...?
0
 

Author Comment

by:briancellura
ID: 37000832
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37001439
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
0
 

Author Comment

by:briancellura
ID: 37001995
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 likethissothatthereisnospaces.

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37002590
...Then how about just using a datasheet subform?


Database50.accdb
0
 

Author Comment

by:briancellura
ID: 37002909
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
0
 

Author Closing Comment

by:briancellura
ID: 37002930
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37003106
...ok
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question