Consulting

Page 2 of 6 FirstFirst 1 2 3 4 ... LastLast
Results 21 to 40 of 117

Thread: Convert Many Invoice Formats to a Standard Format

  1. #21
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    So to start I need to get this conversion making the credit identification number the same as the invoice number with a 9 in front of it working soon as possible.
    For each BizName in Returns
    Loop thru the Credit BizName cells and if Credit BizName = Return BizName Then if Credit BizName Cell.offset (to Date) = Return Date, then Return Invoice Number = "9" & Left(Credit BizName.offset (to Invoice), n) & Return Invoice Number
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  2. #22
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    1. All the data files are emailed to me. Currently I save them in different places.
                                           C:\FlexibakeConversions\Data
                                           C:\Albany\data
                                           C:\midstate\data                     
                                           C:\ahold\Data
                                           C:\kkb\data
    2.    C:\midstate\data\08.15.20_Recap.xlsx
           C:\ahold\Data\0056919000647_0004353672.csv
           C:\FlexibakeConversions\Data\HEIDELBERG-INVCR092220.csv
    3.    .xlsx
           .csv
    Not a bad file system, but I strongly recommend that you reverse the Folder names and place all inputs in subfolders of "Data". That would make it much easier to code and easier to backup and easier to move when the time comes. You can also think about adding a SubSubFolder "Archive" to each SubFolder

    Other than the editorializing in re Locations, note the details below

    Client
    Data Store Location File Name Patteren File Type
    Flexibake C:\Data\Flexibake HEIDELBERG-INVCR & 5 digit number csv
    Albany C:\Data\Albany ? ?
    midstate C:\Data\midstate DateFormat("mm.dd.yy") & _Recap xlsx
    ahold C:\Data\ahold 13 digit numer & _ & 11 digits number csv
    kkb C:\Data\kkb ? ?
    ? C:\Data\? ? ?
    ? C:\Data\? ? ?


    Can you fill in the blanks above So I can start a Workbook for this Project? Also tell me what you intend to do with the Location directory tree.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #23
    Albany and KKB both come out of the same file. I need to filter the raw data in file Heidelberg KKB 090620-091220. In column C I select only chain 5050000. That is the data for albany.
    To get the kkb data I need to deselect 5050000 and that is the kkb data.










    Client Data Store Location File Name Patteren File Type
    Flexibake C:\Data\Flexibake HEIDELBERG-INVCR & ddmmyy csv
    Albany C:\Data\Albany Heidelberg KKB ddmmyy-ddmmyy ?
    midstate C:\Data\midstate DateFormat("mm.dd.yy") & _Recap xlsx
    ahold C:\Data\ahold 13 digit numer & _ & 11 digits number csv
    kkb C:\Data\kkb ? ?
    Client Data Store Location File Name Patteren File Type
    Flexibake C:\Data\Flexibake HEIDELBERG-INVCR092220 csv
    Albany C:\Data\Albany Heidelberg KKB 090620-091220 csv
    midstate C:\Data\midstate 08.15.20_Recap xls
    ahold C:\Data\ahold 0056919000647_0004353672 csv
    kkb C:\Data\kkb Heidelberg KKB 090620-091220 csv
    vbc C:\Data\vbc VBC HB090620-091220 xlsx


    I created the structure like you recommended. The above is the new tree created.
    The flexibake raw data is the only one that I need to edit the credit identification numbers as explained in this thread.

  4. #24
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Is there any rhyme or reason to ahold's File name digits? I am trying to determine the best way to discover the latest submissal. Is there only one file at a time in a Data Storage Folder? Should I be looking at Date Created or Date Modified attribute?

    The best possible situation (for VBA Express) is that all files are moved to an Archive folder as soon as they are processed. YMMV

    I have started Joeny_Project.xlsm and am awaiting news in re ahold's File name and the latest submissal
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #25
    That is what it is named when they send it to me. I get them all from email. I can easily name them anything I want when saving them.

    I normally find them by the date modified column within Microsoft explorer.

    I will create archive folder within each company data folder.

    The vbc and kkb raw data files are emailed to me within one email. I already planned to save the kkb in one folder and vbc in another. That is a new plan since I am following your folder tree suggestion.

    The midstate data is normally email in groups of 10 raw data xls files. Each file is a week of information.

    I can save them in different places and also give them any name if that will make things better.'


    A couple facts about the current process. I just wanted to mention I currently have a lookup file for each company conversion xslm file. Each lookup file has the item numbers of the raw data and then the item numbers I need they to match. I will attached a couple of the current xlsm files I use. I though this might be good to let you know about. Then I will need to know if I need to combine all the lookup files into one.

    Attached are the albany conversion "xlsm" and lookup files I use now. I also attached the midstate conversion "xlsm" files and lookup files. This is just to show you what I am doing now, and explain I different lookup files for each xlsm. That probably not the most efficent but is how it is done now.

    I also attached one of midstates converted files. This is how it needs to be so I can upload into the accounting program


    SamT
    Thanks for all the help and advice along the way.
    Attached Files Attached Files
    Last edited by joeny0706; 09-22-2020 at 01:47 PM.

  6. #26
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I normally find them by the date modified column within Microsoft explorer.

    I will create archive folder within each company data folder.
    Great! Then we just need to Process all new Files, then move them into Archives. This also takes care of the multiple midstate files. As well as handling things anytime someone forgets to Process the files.

    It also eliminates the need for File Names and Name Patterns.

    Business Rules question: Does it matter, if midstate files are processed in bulk, that sometimes your Outputs don't have any midstate Data and other times they're full of it?
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  7. #27
    No it does not matter. I am not understanding the reason for your question but I have processed more than 6 months of data at once. I have not had any errors in any part of the process.

  8. #28
    I read over your last question and wanted to add some info. When I do the conversions at times I add all the converted raw data together for the upload. When I do midstate I always combine all the weeks into one excel file before doing anything else

    The only raw data that will need to be done separate is the flexibake. Since that is the only one I need to fix the credit numbers. All the other raw data can be combined for the conversion and for the upload.

  9. #29
    Samt




    How are things going.

    I have one task that I will need to do also. I have 7 different stores that I need to delete out before converting. They need to be removed before going into the accounting program. I have a module that deletes any rows with 0 in the qty column. What I would do is alter that module to delete any rows that have these stores in the name column. Since I think you are working on a new process to do all my converting I wanted to see if that is something I will need to do "create a module to delete these names" or will that be a task the new process will be able to do?

    Also do you have any questions or anything I can do to help?

    Attached are the store names that I will need to delete before doing the converting. This is only with the flexibake raw data. I will not need this task for any of the other raw data.
    Attached Files Attached Files

  10. #30
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I will add a Stores_To_Ignore Table to the Project From your attachment


    My current thought is to import individual Raw Data Reports, filter them all into the proper Invoice and Return sheets, then adjust those into the final Accounting Reports. IOW, do the whole thing in one Swell Foop.

    So far, I have a folder with 35 of your uploads, I am trying to sort thru to filter out what is needed for the Project

    Do you have any more Business Rules I need to consider?
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  11. #31
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Oh, Yeah! I need copies of the two "Templates"
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  12. #32
    When you say add Stores_To_Ignore. Will that remove the complete row with that store?

    There are a couple added fields I need to depend on other factors. You should be able to see these from my macros in the xlsm files I provided.
    Ex
    Column J on the converted files has whats called a class. The class all depends on the type of customer it is. All Chains stores "The customer name will start with a 1 "ex 1Hannaford:Ha367 Albany" has a class of "1 sales". The line from the macro "AddClassAll" shows them
    "=IF(LEFT(RC[-7],1)=""1"",""1 Sales"",IF(LEFT(RC[-7],1)=""2"",""6 Institutional"",IF(LEFT(RC[-7],1)=""3"",""5 Rest"",IF(LEFT(RC[-7],1)=""4"",""1 Sales"", """"))))"
    Then for the credits all the class is "2 Returns"
    When there is a discount it has a field called line class. The midstate conversion files has a discount macro. For discounts they have a column called "line class". This is shown in the midstate invoices converted.xlsx file.
    There is also a column "K" called template. The temple is determined by the class. If the class is 1 Returns the template is "Copy of:Intuit Service Invoice" If the class is a 2 returns the template is Custom Credit Memo

    The invoice conversion xlsm file MidstateInvoice.xlsm has the discount macro. Not all of the xlsm have the discount macro.
    Another factor to consider is if the qty column has a 0 the row will need to be removed. Not all the xlsm files have the macro ZeroRemoveH. Some of the raw data does not include 0 in the qty column. When there is a 0 in qty column that row needs to be removed.

    You can see how these fields are all created and what I need by looking at all the macros. Would you also like me to explain how the macros all work in detail or are you looking at the macros and they can show what is needed?

    Again I appreciate all the work you are doing and helping me to create these tools.

    Thanks

  13. #33
    There are no templetes. That is just a column and in that column it needs to state what template is used. That tells the program I use to upload the data into the accounting program what templete to use.

  14. #34
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I have this Table in the Project Workbook. Please Fill in the blank Client & SubFolder info for Indepentants(sic)

    Client
    Store Data SubFolder File Name With Pattern Pattern File Type
    Flexibake Flexibake HEIDELBERG-INVCR&Pattern "mmddyyyy" csv
    Albany Albany Heidelberg KKB &Pattern From To "mmddyy-mmddyy" csv
    midstate midstate Pattern&_Recap "mm.dd.yy" xls
    ahold ahold 0056919000647_0004353672 csv
    kkb kkb Heidelberg KKB &Pattern From To "mmddyy-mmddyy" csv
    vbc vbc VBC HB&Pattern From To "mmddyy-mmddyy" xlsx
    Indepentents csv
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  15. #35
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    This has grown a little bit since the first request:

    So I would like to find a way to get those credits have the same invoice number as the invoice that was created on the same day for that store with the number 9 in front of it. I was thinking if a script could find where the store name and date of an invoice match the same store name and date of the credit note then populates column B with the invoice number with 9 if front of it. Then the number on the paper print out will match the number I import into the accounting program.

    If I were doing it, I'd suggest that it might be better to consider restarting/rethinking

    1. using samples of all the data inputs (with data, not a bunch of macros that insert formulas)

    2. data cleansing rules (if 'Size = blank, then make Size = N/A')

    3. a 'database' with any format templates, translation rules ('if = "C" then make it "Credit")

    4. the business rules ('delete this store', 'net invoices and credits'

    5. final output format (again with data) and any associated rules for making the output (file format, sort order, data formatting)


    That way you could ...

    IOW, do the whole thing in one Swell Foop.

    Just my 2 cents
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  16. #36
    I have different replace data files. These are what to replace the fields with. This I am adding new stores and items to all the time. I will attach the different files. You can also explain how to add new stores for converting in your final xlsm file and I can add it all.
    Each xlsm file has a replace invoice data and replace returns data file.
    The data Ahold only has invoices. No creidts with them.
    Attached Files Attached Files

  17. #37
    Paul

    My original/main task was to fix the credit issues I am having and stated in the first post. SamT has looked over my process and agreed it is not the most efficient and best way to do this all. I did not think anyone would be willing or want to help me redo this complete process. There is a lot involved in converting the data. The process does work how it is but not the best way.
    I have this all the time. When a client ask to fix a problem. Say there server is having an issue. I tell them the sever works but it is time to replace it. They tell me while it is working so lets leave it alone. That always bothers me. In this case I was the one saying it works how it is so its fine. I have been lucky and SamT has been willing to help me recreate the complete process. I am willing to do anything needed to help complete this process.

    I am going to be deploying the handhelds very soon. That is when I need the credit number fixed with a 9 in front of it. If I have a new way to convert all the data and at the same time fix the credit number that would be great. Most important is to have the credit issue fixed.


    SamT

    I did want to state again that the flexibake data is the only raw data where I need the credit number changed into the invoice number with a 9 in front of it. All the other raw data has the correct credit number and does not need to be changed.

  18. #38
    Quote Originally Posted by SamT View Post
    I have this Table in the Project Workbook. Please Fill in the blank Client & SubFolder info for Indepentants(sic)

    Client
    Store Data SubFolder File Name With Pattern Pattern File Type
    Flexibake Flexibake HEIDELBERG-INVCR&Pattern "mmddyyyy" csv
    Albany Albany Heidelberg KKB &Pattern From To "mmddyy-mmddyy" csv
    midstate midstate Pattern&_Recap "mm.dd.yy" xls
    ahold ahold 0056919000647_0004353672 csv
    kkb kkb Heidelberg KKB &Pattern From To "mmddyy-mmddyy" csv
    vbc vbc VBC HB&Pattern From To "mmddyy-mmddyy" xlsx
    Indepentents csv

    Independents are a type of customer. There is no raw data that comes from independents. All the raw data has customers that are independents. I am not sure if I am understanding you questions correctly.

  19. #39
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    @SamT

    I thnik you'd better apply for a Job at Joeny's.
    I don't think Joeny can tell you what the process in total should look like.
    He has been given a task that hardly matches his comprehension and a tool that has been designed by somenone else who hardly knew any VBA.
    Like PH suggested: redesigning should be done first.

  20. #40
    snb

    That is correct. I created the xlsm files by asking different questions to different people in this forum. I put together the ones that worked to get what I need. I do hardly know any VBA. When I first started this task was the first time I ever used VBA. When I was hired this was not part of the job description. When I was asked if it can be done I did get it to work in a timely manner. I knew it was not the best and most efficent way but In the end it does get me the correct formatted data so I am able to upload it into the accounting program and it is all correct in the end.

    What takes me 3 hours a week has been able to remove 4 data entry employees by getting this to work. So my employer see what I have done as a great job.

Posting Permissions

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