Cutting and Pasting from Excel

[TapeGun007]

First, let me explain how this works at my company. We use a glorified spreadsheet that goes to engineering. They figure out the systems we're going to install, fill out the parts and labor.

Currently, we use a Word template, we cut and paste the labor, hours, and parts from the spreadsheet to list all the materials and labor.

I would like to create a database that basically auto generates the proposals for us. I would to take the parts and labor portion of the spreadsheet, and just cut and paste it into a field. Then I suppose I could use a report to generate a proposal.

Is this the best way to do this?

 

[FoFa]

What about import, or linking to the spreadsheet instead?
Then you could read it directly.

 

[TapeGun007]

What about import, or linking to the spreadsheet instead?
Then you could read it directly.

Each time Engineering sends us the quote, it's a different filename. Thus why I can't just link directly to a spreadsheet.

 

[Mike375]

Is your question about the best way to get the data from Excel to Access or is the question about what to do once it is in Access.

Perhaps you could elaborate or post up a Zip attachment of a sample Excel file.

 

[TapeGun007]

Is your question about the best way to get the data from Excel to Access or is the question about what to do once it is in Access.

Perhaps you could elaborate or post up a Zip attachment of a sample Excel file.

Get the data from Excel to Access. But I think I have figured at least a decent solution for now. The db is at work, so I can't post it until Monday. However, imagine a spreadsheet that has this:

Manufacturer | Part # | Description | Qty | Price | Total
-------------------------------------------------------
RFI | xjc110 | REX | 1 | 12.99 | 12.99
-------------------------------------------------------
A/D | jc-221 | Mount | 1 | 2.99 | 2.99

This spreadsheet has other data on it as well, but I only want the above data.

So I created a form with the same titles as above and put it in DataSheet mode. I then cut and pasted into it. However, the cut and paste puts all the above into a single cell on the form. But apparently, if I do a Paste Append, it puts it on there correctly. I wish I had a little cleaner way of doing this and am open to suggestions.

Thanks.

 

[apr pillai]

Excel Data

Create a Worksheet with the following Headings:
Manufacturer | Part Number | Description | Qty | Price | Total

Highlight the Range say A1 to F100, if you are getting about 100 of less records in a different worksheet every month. Give that Range a Name using Insert-->Name--> Define and type myData in the Names in Workbook Control. Link this Worksheet Range-Name (rather than Sheet1 etc) to your Access Database. It will remain linked to your database. You can create an Append Query and add this data into an Access Table every month. In the Query give criteria Is not Null under Part Number Column to filter out empty rows, if you have pasted less than 100 items in the named range. Every month when new set of data comes in you can copy and paste into this permanent area so that you can work with it in Access in whatever way you want.

Regards,
a.p.r. pillai
www.msaccesstips.com