Create form where i can paste from excel

[mdarsot]

Hi Everyone

I am new to Access. I am designing Shipment tracking database. I have created the table according to all the fields i require. Now i am at the point where i need to design data entry into those forms. On our side of things the shipment is initiated by another department which send an email out which has excell attachment containg all the shipment info.

What i wanted to do is instead of user entring the info manually field by field into Access. Create forms in access where you can paste from excel all the info into this forms which will be an input to fileds in tables.

Is it doable.

 

[cghoga]

If you could store the Excel doc on the network or centralized location you could link to the Excel document from the Access tables section (in table view, right click - link tables...)

In your form set the Record Source to the linked table and display objects/fields as needed.

Hope this helps.

 

[MajP]

You can link the worksheet in directly or you can import the data to a new or existing table. I do not think you want to paste. Check the help file "import data" or "link tables

 

[mdarsot]

thanks for the info. The email notification i get is the input to enter into Access. Now i can do the input the following way.

1) Opent the spreadsheet --> link it to Access
2) Do the data entry manually in Access
3) Copy and paste all column at once from Excel to Access

I know the first 2 optins are doable but cumbersome.
I know the 3rd option is doable but directly into Access table. However That is a risk.
I want the user to paste info into a form not directly into a table.

Thanks

 

[fneily]

I am designing Shipment tracking database." Out of curiosity, you're not just importing the data and then not normalizing it, are you? If you're not normalizing the data, then you sure aren't going to have a shipment tracking database. Not knowing what the excel spreadsheet looks like, but I'll bet the data should go into more then one table.

 

[gwinn7]

How about an option 4...

Write an interface in Access that imports the data from your spreadsheet. In our office we have successfully programmed such tools. For the user, all they would have to do is select an Import option, grab the path to the file, and your code import the data.

My advice is to forget the copy/paste option.

Gary
gwinn7

 

[mdarsot]

I like the option 4 but it is asking too much from a normal user.

+ the spreadsheet we get is not always in same format.
+ It involves detaching the spreadsheet from email. Importing the data and then validating.

 

[gwinn7]

Are there a few select formats or is it a "random" format?

If its random, then obviously there will be some manual labor involved.

If there are hundreds of records in these spreadsheets, then you may have to have your people manipulate the data into a format acceptable to a custom function to import this.

In that case, hiring a programmer to create this interface for you may be very cost effective assuming we are dealing with a lot of records.

Hope this helps,

Gary
gwinn7