By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,283 Members | 1,709 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,283 IT Pros & Developers. It's quick & easy.

word vba

rcollins
100+
P: 234
I have made a form on my website that emails me when submitted. I want to turn this into a readable format for HR. as now, I have:
date:1/17/2007
position:any
last:blow
first:joe
What is the best way for me to do this, xml, import to form? any help would be great. Thanks
Jan 8 '07 #1
Share this Question
Share on Google+
11 Replies


nico5038
Expert 2.5K+
P: 3,072
I assume you want to import this into a table.
Access will allow you to create a linked table to an outlook folder.
The problem hoever will be the fact that the data is in multiple rows.
Having a format:
Data,: 01-01-2007, Joe, Blow
Would allow to use the Split() function to get the pieces and drop that into a table.
Multiple rows complicate this.

Nic;o)
Jan 8 '07 #2

NeoPa
Expert Mod 15k+
P: 31,661
I have made a form on my website that emails me when submitted. I want to turn this into a readable format for HR. as now, I have:
date:1/17/2007
position:any
last:blow
first:joe
What is the best way for me to do this, xml, import to form? any help would be great. Thanks
I can't see how Access or Word come into this.
Would this not be about the capabilities of the server side script engine (of which I sadly know v little)? VB Script or Java?
I may be on the wrong lines completely - I'm afraid the question itself isn't very clear. If I'm wrong then please ignore me, otherwise I would think that posting in the Java forum might help. If you're using VB Script then they may have some expertise in there for that too.
They may be able to tell you what is the simplest format to attach it to the e-mail with. It may be that it's MS Word I suppose - if libraries are available from there. I don't know.
Jan 8 '07 #3

rcollins
100+
P: 234
these fields actually come in on seperate lines. I never thought about bringing it into Access. I could build a simple database for HR to use so they can import and make a report to our liking. I will look into the Split function
Jan 10 '07 #4

nico5038
Expert 2.5K+
P: 3,072
The split will fill an array, use:

dim arr
dim arr2

arr = split(messagebody,vbCRLF)
This will give an array containing all lines.
With:
Expand|Select|Wrap|Line Numbers
  1. for intI = 0 to Ubound(arr)
  2.     if left(arr(intI),5) = "Data:" then
  3.       arr2 =split(arr(intI),",")
  4.       ' here process arr2(1) (being the first field when you have "Data:," as start
  5.     endif
  6. next intI
Nic;o)
Jan 10 '07 #5

rcollins
100+
P: 234
Is there a way I can import this info into a table that would be workable?
Jan 22 '07 #6

nico5038
Expert 2.5K+
P: 3,072
Yes, just link a table to the Outlook folder. (Chose the link table option when opening a new table and navigate to the folder)
Show this table in a datasheet subform and add a button to process the current record like:

Expand|Select|Wrap|Line Numbers
  1. dim arrLine
  2. dim intI as Integer
  3. dim rs as dao.recordset
  4.  
  5. set rs = currentdb.openrecordset("your table")
  6. arrLine = split(me.subformname.form.body,chr(13) & chr(10))
  7.  
  8. rs.addnew
  9. for intI = 1 to ubound(arrLine)
  10.     select case left(arrSplit(intI),INSTR(arrLine,":"))
  11.      case "Date:"
  12.       rs!DateIn =  mid(arrSplit(intI),INSTR(arrLine,":")+1)
  13.      case "Name:"
  14.       rs!DateIn =  mid(arrSplit(intI),INSTR(arrLine,":")+1)
  15.     end select
  16. next
  17. rs.update
That's the drill for one row per message.

Nic;o)
Jan 22 '07 #7

rcollins
100+
P: 234
What about multiple row? All my stuff comes in on individual lines.
Expand|Select|Wrap|Line Numbers
  1. date:    01/22/2007
  2. position:    Any
  3. last:    Doe
  4. first:    John
  5. middle:    
  6. former:    
  7. street:    123 anywhere
  8. city:    nowhere
  9. state:CO
Jan 22 '07 #8

rcollins
100+
P: 234
So I have modified the code to fit , here is the error I get "Object doesn't support this property or method"
Table is Applications
form is Applications
Subform is Applications subform
Expand|Select|Wrap|Line Numbers
  1. Dim arrLine
  2.  
  3. Dim arrSplit
  4. Dim intI As Integer
  5. Dim rs As dao.Recordset
  6.  
  7. Set rs = CurrentDb.OpenRecordset("Applications")
  8.  
  9. arrLine = Split(Me.Applications_subform.Form.Application, Chr(13) & Chr(10))
  10. rs.AddNew
  11. For intI = 1 To UBound(arrLine)
  12.     Select Case Left(arrSplit(intI), InStr(arrLine, ":"))
  13.      Case "Date:"
  14.       rs!DateIn = Mid(arrSplit(intI), InStr(arrLine, ":") + 1)
  15.      Case "Name:"
  16.       rs!DateIn = Mid(arrSplit(intI), InStr(arrLine, ":") + 1)
  17.     End Select
  18. Next
  19. rs.Update
Jan 22 '07 #9

nico5038
Expert 2.5K+
P: 3,072
Try:

Expand|Select|Wrap|Line Numbers
  1. Dim arrLine
  2. Dim intI As Integer
  3. Dim rs As dao.Recordset
  4.  
  5. Set rs = CurrentDb.OpenRecordset("Applications")
  6.  
  7. arrLine = Split(Me.Applications_subform.Form.Application, Chr(13) & Chr(10))
  8. rs.AddNew
  9. For intI = 1 To UBound(arrLine)
  10.     Select Case Left(arrLine(intI), InStr(arrLine, ":"))
  11.      Case "Date:"
  12.       rs!DateIn = Mid(arrLine(intI), InStr(arrLine, ":") + 1)
  13.      Case "Name:"
  14.       rs!DateIn = Mid(arrLine(intI), InStr(arrLine, ":") + 1)
  15. ' add here the other fields you need to fill in the table
  16.     End Select
  17. Next
  18. rs.Update
  19.  
Would be better to use:
frmApplication
tblApplication
sfrmApplication
etc. to make the code easier to check.

Nic;o)
Jan 22 '07 #10

rcollins
100+
P: 234
I got them named right. Your code had no error thistime. Now, for the other part. In the table, there is a field 'Contents". I am assuming this is where all of the message would go if it was one line message. What about the multiple line?
Jan 22 '07 #11

nico5038
Expert 2.5K+
P: 3,072
The Contents should hold all lines, thus the Split looking for the different lines.
Just check the linked outlook table by making the row heigher or create a form with large textboxes to see multiple lines appear.

Nic;o)
Jan 22 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.