Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cleaning up shipping files (2002)

    I

  2. #2
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cleaning up shipping files (2002)

    Arage

    The short answer is YES . <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> The slightly longer one is MAYBE <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16> .

    It all rather depends on what needs to be achieved specifically and how strongly structured your data is.
    Can you post a (sanitized if necessary) example of what you receive, and what you want it to become.

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cleaning up shipping files (2002)

    Hi Andrew,
    Here

  4. #4
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cleaning up shipping files (2002)

    <img src=/S/burga.gif border=0 alt=burga width=40 height=15>
    OK - should be straightforward.
    A design question though.
    Presumably your ideal solution would automatically detect the right kind of attachment - if so, how?

  5. #5
    Lounger
    Join Date
    Feb 2001
    Location
    London UK, Gtr London, England
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cleaning up shipping files (2002)

    Question: despite the attachment suffix (csv, txt,001), they are all comma seperated text files?
    if this is true then you can treat them the same way, the "type" doesn't matter, you can write somethig to read and edit all of them

    Hope this helps

  6. #6
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cleaning up shipping files (2002)

    I can only assume that there is something in code that could make such a specification.

  7. #7
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cleaning up shipping files (2002)

    Yes, save for the errors that occur, all values are comma separated. A few more of the types of error I need to know if I can fix this way include:

    -deleting an entire record (b/c it contains shipping info for an item my company doesn

  8. #8
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cleaning up shipping files (2002)

    Arage

    OK - try the following code. <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    Cut and paste this to the Outlook "ThisOutLookSession" code space.
    Note that by default Outlook disables macros and doesn't tell you it has - you'll need to enable them to at most 'Medium' security.

    I've had to make some assumptions for the purposes of design that you will need to review.

    This routine triggers on the NEWMAIL event, and looks for mail with file attachments
    If it sees file types CSV TXT or 001 it then asks if it should convert them.
    You can change the file types looked for by modifying the variable 'MyExts' - you can add more by just typing .EXT inside the quotes where EXT is your new extension.
    For testing I also used directory C:TEMP to create reformatted files in - change MyPath as needed for your network drive.
    I have coded changes to data as follows -
    Column 2 has 001 added
    Columns 4 & 5 have quotes
    Column 4 has R00 at front
    Column 5 has S045 at front
    The LAST column has date extended. Note that I specifically coded it as the last, rather than 10th column because this protects the code if you text fields ever contain commas
    The program is designed so that similar changes could be made without a big deal
    <img src=/w3timages/blackline.gif width=33% height=2>


    Public Myolapp As Variant
    Public I As Long
    Public J As Long
    Public MyExt As String
    Public MyExts As String
    Public MyFile As String
    Public MyPath As String


    Private Sub Application_NewMail()

    ' Identifies incoming mail that has an attachment that matches our target type
    ' if so, asks, and translates it to new format and save

    ' Change the following two constants as required to get things working right
    ' First is simply a list of valid extensions to trigger reformating action

    MyExts = ".CSV.TXT.001"

    ' Second is the path for re-formatted files to go to

    MyPath = "C:TEMP"

    ' Preliminary setup to view new mail

    Set Myolapp = CreateObject("Outlook.Application")
    Set myNameSpace = Myolapp.GetNamespace("MAPI")
    Set Myolapp.ActiveExplorer.CurrentFolder = _
    myNameSpace.GetDefaultFolder(olFolderInbox)

    ' Identify unread mail and deal with it

    With Myolapp.ActiveExplorer.CurrentFolder
    For I = 1 To .Items.Count
    If .Items(I).UnRead = True Then
    For J = 1 To .Items(I).Attachments.Count
    MyFile = .Items(I).Attachments(J).DisplayName
    MyExt = Right(MyFile, 4)
    If InStr(1, MyExts, MyExt, vbTextCompare) > 0 Then
    If MsgBox("Reformat file '" & MyFile & "'?", buttons:=vbOKCancel) = 1 Then
    MyFile = MyPath & "" & Left(MyFile, Len(MyFile) - 4) & "._B4"
    .Items(I).Attachments(J).SaveAsFile MyFile
    Reformat MyFile
    End If
    .Items(I).UnRead = False
    End If
    Next J
    End If
    Next I
    End With

    End Sub
    Sub Reformat(target As String)
    Dim I As Long
    Dim J As Long
    Dim DateCol As Long
    Dim MyRec As String
    Dim MyFields(100) As String

    ' This would have been easier in Excel but it cannot refrain from re-formating quotes
    ' so - lets just do it the hard way ;-)
    ' Just read the file line by line, break it out into fields, and then rewrite

    Open target For Input As #1
    Open Left(target, Len(target) - 4) & ".CSV" For Output As #2
    Line Input #1, MyRec
    While Not EOF(1)
    ' it is easier and less bug-prone to split into fields than try to be clever
    J = 1
    I = InStr(1, MyRec, ",")
    While I > 0
    MyFields(J) = Left(MyRec, I - 1)
    MyRec = Mid(MyRec, I + 1)
    J = J + 1
    I = InStr(1, MyRec, ",")
    Wend
    MyFields(J) = MyRec


    ' This is the section that makes the requested changes

    MyFields(2) = Left(MyFields(2), Len(MyFields(2)) - 1) & "001"""
    MyFields(4) = """R00" & MyFields(4) & """"
    MyFields(5) = """S045" & MyFields(5) & """"
    ' Set the column for date as the last column.
    ' Why? to protect from possible commas in the text fields ;-)
    MyFields(J) = Left(MyFields(J), Len(MyFields(J)) - 3) & "20" & Right(MyFields(J), 3)

    ' then we put it all back together and write it out
    For I = 1 To J - 1
    Print #2, MyFields(I); ",";
    Next I
    Print #2, MyFields(J)
    Line Input #1, MyRec
    Wend
    Close #1
    Close #2

    End Sub

  9. #9
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cleaning up shipping files (2002)

    Oops

    Reading between the lines of this message I detect that the changes requested are not as 'mindless' as I coded.

    I begin to think that things like "S045" only have to be added if they are not already there.

    My suggestion is that we send direct emails for the fine-tuning - rather than bother the board.

Posting Permissions

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