Consulting

Results 1 to 10 of 10

Thread: Extracting data from one record into a table

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Mentor paulked's Avatar
    Joined
    Apr 2006
    Posts
    326
    Location

    Extracting data from one record into a table

    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
    Semper in excretia sumus; solum profundum variat.

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,029
    Location
    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.

  3. #3
    VBAX Mentor paulked's Avatar
    Joined
    Apr 2006
    Posts
    326
    Location
    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
    Semper in excretia sumus; solum profundum variat.

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,029
    Location
    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?

  5. #5
    VBAX Mentor paulked's Avatar
    Joined
    Apr 2006
    Posts
    326
    Location
    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
    Semper in excretia sumus; solum profundum variat.

  6. #6
    VBAX Mentor paulked's Avatar
    Joined
    Apr 2006
    Posts
    326
    Location
    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.
    Semper in excretia sumus; solum profundum variat.

  7. #7
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,029
    Location
    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?

  8. #8
    VBAX Mentor paulked's Avatar
    Joined
    Apr 2006
    Posts
    326
    Location
    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
    Semper in excretia sumus; solum profundum variat.

  9. #9
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,029
    Location
    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.
    Attached Files Attached Files

  10. #10
    VBAX Mentor paulked's Avatar
    Joined
    Apr 2006
    Posts
    326
    Location
    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
    Semper in excretia sumus; solum profundum variat.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •