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.
VBAX1.zip

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.
VBAX1.xlsm

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

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

Cheers