Extracting data from one record into a table

[paulked]

Hi there

This could be my first VBA project in Access (if it is possible!) but I am ok with VBA in Excel.

At the moment I read a table from access into excel then run this code on it:



Sub ExtractData()
Application.ScreenUpdating = False
Dim Whole As String
Dim Qty() As String
Dim StrtPos As Long
Dim EndPos As Long
Dim lr As Long
Dim I As Long
With Raw
lr = .Cells(.Rows.Count, "A").End(xlUp).Row
For I = 8 To lr
Whole = .Cells(I, 1)
'Event name
StrtPos = InStr(Whole, "Sub total") + 2
EndPos = InStr(Whole, " <")
.Cells(I, 2) = Mid(Whole, StrtPos + 10, EndPos - StrtPos - 10)
.Cells(I, 2) = Replace(.Cells(I, 2), Chr(10), "")
Reg.Cells(I + 1, 1) = .Cells(I, 2)
'First Name
StrtPos = InStr(Whole, "First Name") + 1
EndPos = InStr(Whole, "Last") - 2
.Cells(I, 3) = Mid(Whole, StrtPos + 10, EndPos - StrtPos - 10)
Surname
StrtPos = InStr(Whole, "Last Name") + 1
EndPos = InStr(Whole, "Post") - 2
.Cells(I, 4) = Mid(Whole, StrtPos + 9, EndPos - StrtPos - 9)
'Postcode
StrtPos = InStr(Whole, "Post Code") + 1
EndPos = InStr(Whole, "Phone") - 2
.Cells(I, 5) = Mid(Whole, StrtPos + 9, EndPos - StrtPos - 9)
On Error Resume Next
'eMail
StrtPos = InStr(Whole, "Email") + 1
EndPos = StrtPos + 50
.Cells(I, 6) = Mid(Whole, StrtPos + 5, EndPos - StrtPos - 5)
.Cells(I, 6) = Left(.Cells(I, 6), InStr(.Cells(I, 6), Chr(9)) - 1)
On Error GoTo 0
'Phone
StrtPos = InStr(Whole, "Phone") + 1
EndPos = InStr(Whole, "Email") - 2
.Cells(I, 7) = Mid(Whole, StrtPos + 5, EndPos - StrtPos - 5)
'Qty
StrtPos = InStr(Whole, ">")
EndPos = InStr(Whole, "First") - 1
.Cells(I, 8) = Mid(Whole, StrtPos + 4, EndPos - StrtPos - 6)
.Cells(I, 8) = Replace(.Cells(I, 8), Chr(9), "")
Qty = Split(.Cells(I, 8))
.Cells(I, 8) = Qty(1)
Reg.Cells(I + 1, "G") = Qty(1)
Next
End With
End Sub




The table in access is linked to a folder in outlook and contains the body of the emails I want to extract the data from.

The format of the email is always the same as it generated by our website on each ticket sale we make, so the above code is very reliable, but it is quite slow.

What I would like to do:
Every time a new email is received (updated in tblRegistrants in access) it extracts the elements of the email body text that I need into another table.
I can then just update my sheet in excel from the table in access when I need to without having to extract the data every time (much quicker).

Is this possible? If so I'd appreciate any pointers.

Best regards and thanks in anticipation

Paul Ked

[OBP]

Paul, you can use a query to extract just the last record, assuming the records are dated or numbered.
You can use Max of or you can use Number of records where n = 1.

[paulked]

Thank you for your response. Please forgive my ignorance, but I have never used anything but tables in Access!

If, by using a query, it only extracts from the data from the last record, what would happen when Outlook opened with, say, 5 new emails? Would it only extract the last?

I've just had a play with queries to see what they're about, but fell at the first hurdle...

I tried parse the Event Name out of the Subject field in tblRegistrants (which is always in the format "New Registration For Event : Early Bird Festival Ticket 2019") into the Title field in tblData with:



Title: Right([tblRegistrants]![Subject],Len([tblRegistrants]![Subject])-29)


and all sorts of variations of tables and fields (also tried append) but I get syntax errors that don't match the code (eg sq brackets where I haven't used them) and questions (eg "Enter Parameter Value ?")

I'm confused by the logic that Access seems to use (or lack of it?) but will continue play and learn... can you recommend a website I can start learning from?

Many thanks for your patience

[OBP]

Paul, I know how confusing Access is compared to Excel, especially Query SQL.
But Queries are very powerful, as is VBA, plus you can actually combine them.
I think I must have misunderstood your requirement, as I though you just wanted the very last record in the table.
So can you provide some dummy data, preferably in an Acces 2007 format database or an Excel Spreadsheet of how you store the data and which parts from which records you want to extract?
I suppose you know Excel can link directly to Access Queries?


ps I see that you are also in Wales, but what are you doing posting at 4 am?

[paulked]

Good afternoon! Yes, I am in Wales, Llangollen. I couldn’t sleep last night as i was afraid of not waking up in time for the F1 Grand Prix but I slept like a log once it finished!

Again, thanks for prompt reply and your very valuable help. I will post an example tomorrow as we are out partying today.

Cheers

[paulked]

Hi

I didn't know that Access queries could be linked to Excel, interesting.

The DB attached is a copy of the real one with personal data changed and the linked table converted to a local table. In the real one the table is linked to a folder in Outlook.
24070

The SS attached extracts the data from the above DB. This is just a mock-up of the real SS, it's the data on the Data sheet that we use.
24069

Obviously, change the DB location (strFilePath in the Sub UpdateRegistrants())

Many thanks for your help.

[OBP]

Sorry for the delay in responding, my broadband has been in and out all day today and the Virgin Service Engineer is coming to have a look at it tomorrow.
Unusually I also didn't get an email telling me you had posted.

I will take a look at your database and get back to you, have you included an example of the output you want?

[paulked]

That's very kind. The output (presumably a table) would be like the Data sheet in the file above so:



Event
Forename
Surname
Postcode
eMail
Telephone
Qty
Date


Early Bird Festival Ticket 2019
xxxxe
xxxby
xxxre
xxxxxxxby@gmail.com
xxxxxxx91
1
01/04/2019


Early Bird Festival Ticket 2019
xxx
xxxxxxxxxe
Lxx Zxx
xxxxxx@gmail.com
7xxx56x789
1
02/04/2019


Early Bird Festival Ticket 2019
xxd
xxxxe
xxx7rh
xxxxxxe@hotmail.com
0xxxxxx68
2
04/04/2019


The Electric Swing Circus
xxxxxxn
xxxxxa
LxxxTP
zaxxxxxxxi@aol.com
01978000000
2
05/04/2019


Dub Pistols
xxn
xxxe
Lxxx7lu
xxxxxxxx3@gmail.com
xxxxxx433
1
06/04/2019


Tunng
xxxxxrt
xxxxxxrts
LxxxxxUY
exxxxx0@btopenworld.com

2
08/04/2019


Bruce Parry
xxcy
Wxxxxbb
LxxxxHE
lxxxxxxxb@aol.com
07xxxxxx678
2
09/04/2019


Comedy Club Finale 2019
xxxxxan
xxxxxes
LxxxxGD
sxxxxxxx@me.com
07xxxxx635
3
10/04/2019


Dub Pistols
xxxxxd
xxxxxh
YOxxxQN
xxxxxxxxxx@btinternet.com
xxxx146
2
14/04/2019


Early Bird Festival Ticket 2019
xxxn
Mxxxxxon
LxxxxAD
jxxxxnion@fluxxxxxxe.co.uk

2
14/04/2019

[OBP]

Paul, here is the Access Database, there are 3 queries due to number of Character limits. The query for you to link to is tblRegistrants Output.
The other 2 queries show how you get there.

[paulked]

That is brilliant! Thank you. I've had a quick look at the queries and am beginning to understand the logic, I'll spend some time going through it all and, hopefully, learn from the master :yes

If you're ever around my way, dinner's on me!

Cheers