+ Reply to Thread
Results 1 to 11 of 11

problem in reading new text file in VB script macro

  1. 01-26-2011, 10:29 AM #1
    kamlesh_waghela2000 is offline
    Registered User
    Join Date
    07-02-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    9

    Unhappy problem in reading new text file in VB script macro

    Hi There,

    I have macro in place, which is working fine, but now file format has changed and I am unable to run the macro, therefore could you please fix the problem for me. Please.

    1. With OLD format macro is working very fine. Please see below example.
    This below format is in text file format.
    UNA:+,? '
    UNB+UNOA:1+MAEU+5036716300:12+110117:1407+234'
    UNH+23400001+INVOIC:D:99A:UN'
    BGM+935:::IMPORT INVOICE+5419613410-XXX-001+9'
    DTM+149:20110117:102'
    RFF+ADJ:730'
    RFF+IV:5419613410-XXX-001'
    RFF+IT:MAEU'

    Then what macro does is it reads the data and keep the required data and it converts it into tabular format into excel. I have attached file for your easy going. Please refer "OLD_Format" file.

    2. Now format has changed, which comes in just one string therefore Macro doesn't work. I am not that much expert so need your help in this regard.
    This below new format is in text file format. This is in just a one string.
    UNA:+,? 'UNB+UNOA:1+MAEU+5036716300:12+110117:1407+234'UNH+23400001+INVOIC:D:99A:UN'BGM+935:::IMPORT INVOICE+5419613410-XXX-001+9'DTM+149:20110117:102'RFF+ADJ:730'RFF+IV:5419613410-XXX-001'RFF+IT:MAEU'RFF+BM:MSKU4591988'MOA+39:3151.00:USD'TDT+11+1102+VE++MAEU:101:166'ALC+C++++FC'FTX+PAI+++NEON-NIKE EUROPEAN OPS NETHERLANDS'RFF+VA:WK'MOA+304:406.00:USD'ALC+C++++FC'FTX+PAI+++NEON-NIKE EUROPEAN OPS NETHERLANDS'RFF+VA:WK'MOA+304:2745.00:USD'UNS+S'UNT+19+23400001'UNZ+1+234'

    As you now see that macro can't read data bcoz it comes in one string.

    Please could anyone look at this urgently. I would appreciate your help in this regards. Please.

    Best Regards,
    Kam
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-26-2011, 10:35 AM #2
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,099

    Re: problem in reading new text file in VB script macro

    The VBA Project is password protected.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 01-26-2011, 06:49 PM #3
    kamlesh_waghela2000 is offline
    Registered User
    Join Date
    07-02-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    9

    Talking Re: problem in reading new text file in VB script macro

    Sorry. Password is speed2191
    Reply With QuoteRegister To Reply
  4. 01-26-2011, 08:01 PM #4
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,099

    Re: problem in reading new text file in VB script macro

    Can you not go back to the original supplier of the code ... as identified on the closing form?

    Is this a typical file? One record? One Container? One Invoice? Several line items?

    The options would seem to be:
    1. pre-process the new format file and produce an old format file
    2. process the single record in the code and split it into sub records equivalent to the original

    Each line in the original ends with a single quote "'" and those single quotes exist in the new format. It should be possible, therefore, to split the records.

    I think, of the two options, I'd go for the first as it involves least (in fact, no) hacking of the original code.

    I'll see if I can produce something tomorrow unless someone else provides a solution in the meantime.

    If there are more complex examples, it might be worth sharing one now rather than later.

    Regards
    Reply With QuoteRegister To Reply
  5. 01-27-2011, 08:02 PM #5
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,099

    Re: problem in reading new text file in VB script macro

    For the sake of simplicity, I have produced a free-standing conversion in Excel.

    It works in much the same way as your current Excel application.

    However, rather than taking the text file and converting it to Excel, it takes a "new format" text file and converts it to an "old format" text file. This can then be read by the original converter.

    Please Login or Register  to view this content.

    It probably wouldn't be too difficult to combine this with the original but I think it's better, at this stage, to "prove the theory" before hacking the original about.

    I trust it will help.

    Regards
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 01-27-2011, 08:08 PM #6
    kamlesh_waghela2000 is offline
    Registered User
    Join Date
    07-02-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: problem in reading new text file in VB script macro

    Hi,

    The person who created the macro has now left our company, therefore I am ask author to make the changes.

    Yes there will several line items...please see attached example. There can be 100s invoice, container number and amount...

    It would be great if you can create code to convert NEW format into OLD format and this should happen before macro starts reading text file. Is it possible?

    No there are no complex examples...only there can be several line item with the same ID such as RFF+IV, RFF+BM and so on...

    Please help me as I need this macro badly...I would appreciate your help.

    Best Regards,
    Kam.
    Reply With QuoteRegister To Reply
  7. 01-27-2011, 08:12 PM #7
    kamlesh_waghela2000 is offline
    Registered User
    Join Date
    07-02-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: problem in reading new text file in VB script macro

    Sorry I didn't see your message and replied by then. Thank you for the below help. It really works...

    Can we add this script into original macro????

    Yes there will several line items...please see attached example. There can be 100s invoice, container number and amount...

    No there are no complex examples...only there can be several line item with the same ID such as RFF+IV, RFF+BM and so on...

    Best Regards,
    Kam.
    Last edited by kamlesh_waghela2000; 01-27-2011 at 08:27 PM.
    Reply With QuoteRegister To Reply
  8. 01-28-2011, 01:28 PM #8
    kamlesh_waghela2000 is offline
    Registered User
    Join Date
    07-02-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    9

    Unhappy Re: problem in reading new text file in VB script macro

    Hi, I really appreciate your help on this regard. The macro really works.

    Is it possible to include this new macro into original macro so I don;t have to use two different macros.

    Please advise.

    Thank you in advance.

    Best Regards,
    Kam.
    Reply With QuoteRegister To Reply
  9. 01-28-2011, 06:55 PM #9
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,099

    Re: problem in reading new text file in VB script macro

    I have attached an updated workbook. This will process the "new format" text file. It will, if you change a cell (G2) on the Config tab, still process "old format" text files.

    It outputs to sheet1 as before.

    Enjoy ;-)

    Regards


    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 02-05-2011, 02:18 PM #10
    kamlesh_waghela2000 is offline
    Registered User
    Join Date
    07-02-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: problem in reading new text file in VB script macro

    Thank u very much mate. I really appreciate for your help. You have saved me thank u again.
    Reply With QuoteRegister To Reply
  11. 02-05-2011, 03:19 PM #11
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,099

    Re: problem in reading new text file in VB script macro

    You're welcome. Thanks for the feedback.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1