Adding Excel cells to a form

[Sticky99]

Could I ask if anyone could answer if I can "add" excel cells to a form. The process we currently have is an engineer emails a single line of cells from an excel spreadheet containing details of parts required (Part No. etc.) we them add this to fulfilment spreadsheet and issue the parts, any parts not in stock will be marked as needing to be ordered. What I would like to do is add the emailed excel cells directly into a form which I can then add to a table. Any ideas on this would be very gratefully received!

Thanks

 

[theDBguy]

Hi. I'm not sure I get it, but I'll take a stab. You might be able to use Outlook Automation, to manipulate the email and get the data you want into a table or form. That is of course assuming you're using Outlook. Hope it helps...

 

[The_Doc_Man]

The trick would be how you planned to add the info to the table as the end step. (That is a rhetorical comment.) You have to have the cells in a format that allows addition in some appropriate way.

My question though is exactly what you mean by "form" in this context? If we are talking a real Access form but your idea was to e-mail them, perhaps you want to NOT attach them to a form. Instead, make them a small file that can become an attachment so that when the (Access) form generates the mail, it attaches the file AS AN ATTACHMENT before mailing. Then, when you actually receive the message, you remove the attachment and process it more normally.

My thought here is that too many conversions between transmission will lead to a partly or completely garbled message. Sort of in the category of that old saying about "too many cooks spoil the broth."

 

[zeroaccess]

Maybe take a look at this.

 

[Uncle Gizmo]

I always wondered about doing this type of thing with persistent recordsets...

 

[Sticky99]

The trick would be how you planned to add the info to the table as the end step. (That is a rhetorical comment.) You have to have the cells in a format that allows addition in some appropriate way.

My question though is exactly what you mean by "form" in this context? If we are talking a real Access form but your idea was to e-mail them, perhaps you want to NOT attach them to a form. Instead, make them a small file that can become an attachment so that when the (Access) form generates the mail, it attaches the file AS AN ATTACHMENT before mailing. Then, when you actually receive the message, you remove the attachment and process it more normally.

My thought here is that too many conversions between transmission will lead to a partly or completely garbled message. Sort of in the category of that old saying about "too many cooks spoil the broth."

Hi, the ultimate aim is just to get the emailed excel cells into my Access table in the simplest way possible for the end user. Does this clarify the process.?

 

[Sticky99]

Hi. I'm not sure I get it, but I'll take a stab. You might be able to use Outlook Automation, to manipulate the email and get the data you want into a table or form. That is of course assuming you're using Outlook. Hope it helps...

Thanks, I’ll have a look at this later.

 

[The_Doc_Man]

OK, suddenly I'm not clear on something. After re-reading, my question is: How much of this process do you control and how much is done manually by your engineers in the field? Are you defining a solution end-to-end or only at the receiving end? That will govern how you should be answered.

 

[zeroaccess]

Hi, the ultimate aim is just to get the emailed excel cells into my Access table in the simplest way possible for the end user. Does this clarify the process.?

If the above solution doesn't get you there, the best way would be to have the engineers send you a .csv (comma separated values) file instead. It's just a .txt file and you would tell Access what the delimiting character is.

If the engineers send you somewhat-normalized data.

 

[Sticky99]

OK, suddenly I'm not clear on something. After re-reading, my question is: How much of this process do you control and how much is done manually by your engineers in the field? Are you defining a solution end-to-end or only at the receiving end? That will govern how you should be answered.

Sorry The_Doc_Man, I probably didn't explain very well. The cells emailed rom the engineers always come in the same format (they select the cells from a standard spreadsheet) and consists of 8 cells, all formatted as Text. We are only at the receiving end.

 

[bastanu]

If you are using Outlook feel free to download and use the utility mentioned by zeroaccess in post #4, you can set it up to loop through a certain Outlook folder (I usually set up a rule in Outlook to move the emails with a certain subject or from a certain address to a "to be processed" subfolder) import them in your Access application then move them to a "processed" subfolder.

Cheers,
Vlad

 

[The_Doc_Man]

So .... you get the e-mail. It has a text-only attachment? Or is the text embedded in the body of the e-mail?

If it is an attachment, it shouldn't be too hard to make Outlook export the attachment as a file, after which you could just import the spreadsheet to a temp table. Then an INSERT INTO ... SELECT FROM type of query would finish the job. If this row has been embedded in the file, it becomes a bit trickier.

So I'm considering why this is an issue? The manual process is easy enough. Are you looking to automate the process completely because your users can't be trusted to do it right manually? (That would be a valid reason; I'm just trying to identify the context.)

If your problem is automating the import, the first hurdle you face is this: Your automation has to be able to identify the message in the Outlook Inbox, which usually means that you have to assure a fixed "Subject" format. OR you would have to open the inbox and list the subject lines so you could select one of them. If you are looking for suggestions, my first one is to decide the achievable. Can you assure a SUBJECT line format? Or will you have to present a list for selection?

Once you have select the message, it isn't that hard to manipulate it to save the attachment as a file. Since it is a one-liner spreadsheet, you can easily import that into a temporary table for examination, validation, and final disposition via an append query.

My best advice is to design the steps you think you want to do and treat this as a step-by-step recipe. This is also called the "divide and conquer" approach in that you break the problem down into little pieces and attack the pieces one at a time.

 

[Cronk]

@Doc wrote

Can you assure a SUBJECT line format? Or will you have to present a list for selection?

Alternatively, the body of the email might provide a means to sort between data emails and other ones.

If several cells of an excel row are pasted into the body of an Outlook message, the body (not the HTMLbody property of the email item) will have the format

<Text preceding the pasted data if any> First cell contents Second cell contents ... Last cell contents <Any text after the pasted data if any>

If you follow this route, I'd strongly suggest you display the data extracted prior to importing into your data base. I've learned the hard way not to rely on users always following providing data in any agreed method.

 

[zeroaccess]

@Doc wrote

Alternatively, the body of the email might provide a means to sort between data emails and other ones.

If several cells of an excel row are pasted into the body of an Outlook message, the body (not the HTMLbody property of the email item) will have the format

<Text preceding the pasted data if any> First cell contents Second cell contents ... Last cell contents <Any text after the pasted data if any>

If you follow this route, I'd strongly suggest you display the data extracted prior to importing into your data base. I've learned the hard way not to rely on users always following providing data in any agreed method.

Maybe they could be provided with an Excel macro/vba button or something that automates some of the process.