Consulting

Page 3 of 6 FirstFirst 1 2 3 4 5 ... LastLast
Results 41 to 60 of 117

Thread: Convert Many Invoice Formats to a Standard Format

  1. #41
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    And no mention of Heidelberg KKB, VBC HB, or Indepentents

    Oh well, more files to download and research

    OK, you have a different Replace file for each Distributor and for Invoices and Returns for each Distributor.

    Do any Distributors have any matching Store names or product Identifiers? IOW, why can't I consolidate all the Replacements into two Tables, one for Store Names and one for Products.

    I just did a quick visual analysis of all the Product Replacements in one list and while there are a lot of duplicates, many just seem to be from inserting an asterisk in the Returns Replacement Product names.

    I repeated that with the store replacements and... Holy shit... I'll need to consider them on an individual basis.

    The difference in speed between pulling replacement values from one Dictionary and a gazillion sheets is phenomenal.
    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. #42
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    What takes me 3 hours a week
    This shouldn't take more than 3 minutes a week by anybody who can click a Shape/Button on a Sheet.

    Your boss may replace you.
    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. #43
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    There is no raw data that comes from independents. All the raw data has customers that are independents.
    You provided a csv attachment labeled "Indepentants".

    You keep saying that you have 7 current Distributors, but I have only seen signs of 6 + Indepentants
    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

  4. #44
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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.
    For now. Who knows what future Clients will need, This Project must be able to handle any and all inputs with minimal maintainence required.
    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. #45
    The converting only takes 15 min. The 3 hour part is when importing 10,000 rows into the accounting program.

  6. #46
    The Heidelberg kkb, vbc hb and albany all use the same item numbers. When you say no mention of them are you meaning when I supplied you with the replace data files. The VBC I do not have replace data files at this time. I have not needed to import VBC yet. I will attach the KKB replace files to this post. The flexibake data replace are not 100% completed yet. Since I have not had the credit number fixed I have not ran it through the xlsm file to find what is missing.

    I just started to create the flexibake xlsm file when I asked for help with fixing the credit numbers. So that file is not 100% yet.

    I have only been doing Albany, Midstate and Ahold as of now.

    None will have the same store names.


    They can all be combined. That is fine. I just received the data at different times so I processed it at different times. Well I was creating these xlsm i was also teaching myself what I needed to complete the task. I know I did not do things the best or correct way. This is not my field of study nor do I plan to do any more with it in the future. I did get it to work by troubleshooting and working with others.

    The returns are the same with just an * that is correct.

    I am not able to find the csv I provided with independents. What post was that in?
    6 distributors will be fine. I apologize for saying 7 and also causing confusion with the independents.

    I do appreciate the help
    I don't need to be told by others how I have done things incorrect. I know that. This was my first time working with VBA. I dont think I did terrible for the zero knowledge I had. I may have it incorrect but it does work and I did not spend a lot of time getting it done. The company is not willing to pay to have this done at this time. That leaves it up to me to do it. I dont have the time right now to learn VBA as I have many other things I have had to devote my time to.


    I only wanted to state flexibake is the only one with credit numbers that need to be fixed so I did not confuse you and you think it needed to be done with all of them. Just wanted to make sure I was clear.
    Attached Files Attached Files

  7. #47
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    How about those Templates?

    I have attached a Replacements analysis. It has become obvious that there is no truly Code usable Pattern inherent to the system your office uses.

    How much influence do you have with Accounting and with your Client distributors?
    Attached Files Attached Files
    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

  8. #48
    There are no templets. That is just a column and in that column it needs to state what template is used for that type of transaction. The accounting program needs to know what template to use "Invoice or credit".

    I apologize but I attached the wrong flexdatareplacce.xlsx files. That is why there are duplicate store names.
    I am in the process of creating the flexibake conversion tool. So I just made a copy of the midstate replacedata file and was going to fill it in with the correct flexibake data. I did not delete the old midstate data from it.

    I have attached the correct flexdatareplace.xlsx files That file has just been started. I have it ready for only two routes. Currently I only have two routes using that DSD system. It will be used for 30 routes within the next 3 months. Soon I will have a lot more store conversions to add they are just not ready yet.

    Within you xslx file you attached I have removed the duplicate stores that are not needed. The duplicate item 33 ITAL I also removed. That was an item the changed the name recently. 33 ITAL was the old name so I removed that also.
    If needed I can change the item names that do not follow the asterisk convention. When you say they dont follow the convention in what way do you mean?


    I have zero influence with the distributors. I do have infulence with the accounting department. What are you thinking about that might be good to change?
    Attached Files Attached Files

  9. #49
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    The 3 hour part is when importing 10,000 rows into the accounting program.
    I thought that was not your department.

    You have led us to believe that all you needed done was importing Distributor Reports and creating a Credit report and an Invoice Report.

    Now you tell me that that part is a very minor portion of your major complaint that "it takes too long."

    I think that Paul has offered you a method to fix Flexibake credit Invoice numbers.
    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

  10. #50
    I apologize if you think I mislead you. I am not sure how you think I did mislead you. I have explained everything I do and also how I got to the point I am at now. I did not have any knowledge with VBA. I was just to able to get help from others and troubleshoot the issues to get it working as is.

    That is all I do. I export the data. I then convert the data and upload it. Once I start the upload it takes anywhere from 30 min to 3 hours depending how much data. I don’t do anything during this time besides wait for it to finish. The converting does not take long. As I explained previously, I just copy and paste the data into my xlsm file and run the macro.

    You are the one who said my boss should replace me because I said it takes 3 hours. I was just letting you know that part of the process there is no one who can speed it up using the program I do to upload it. I have no complaint that it takes to long. I never said that. I understand with the program I use that is how long it will take.

    As I said previous I understand it is alot of work to redo the complete process and would take anyone many hours to fix. I was not looking for that. I would appreciate anyone who is willing to but that is not required. How I do it now is a quick process. It may not be the best way but it is easy and gets it done.

    My first issue was the credit numbers. To get that fixed. When you looked at my xlsm file and others I was told it is not correct and not an efficient way to do it. I would appreciate if anyone wanted to help me so I am doing it correct but like I said the way I do it does work fine. I just need to get the credit numbers to be the invoice number with a 9 in front of it. I tried doing what Paul explained but was not able to get it to work. As I said I have very limited knowledge with VBA.

  11. #51
    Again as I said in the first post. If anyone is willing to help me create a macro that will make the credit identification number match the invoice identification number from that day and put a 9 in front of it I would be very happy. If I must I would be able to pay a small fee if that is what is required. I am not able to figure this out. I have tried but still have not been able to get it to work correctly. Just a macro I can run on the raw data before I copy and paste it into my xlsm files would be perfect.

  12. #52
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Third try. See if this works for you

    test1.csv is the input you provided a long time ago, and test1-out.csv has a 9 in front of the invoice for matching stores and dates



    Option Explicit
    
    Sub FixCSV()
        Dim sCSV As String
        Dim wbCSV As Workbook
        Dim wsCSV As Worksheet
        Dim rCSV As Range, rCSV1 As Range
        Dim i As Long, j As Long
        
        sCSV = Application.GetOpenFilename("ERP File, *.CSV")
        If sCSV = "False" Then Exit Sub
        
        Application.ScreenUpdating = False
        
        Workbooks.Open Filename:=sCSV
        
        Set wbCSV = ActiveWorkbook
        Set wsCSV = ActiveSheet
        
        With wsCSV      '   Guessing
            .Cells(1, 1).Value = "Date"
            .Cells(1, 2).Value = "Invoice"
            .Cells(1, 3).Value = "Store"
            .Cells(1, 4).Value = "Product"
            .Cells(1, 5).Value = "Qty"
            .Cells(1, 6).Value = "Cost"
            .Cells(1, 7).Value = "InvCred"
            .Cells(1, 8).Value = "Something"
        
            Set rCSV = .Cells(1, 1).CurrentRegion
            Set rCSV1 = rCSV.Cells(2, 1).Resize(rCSV.Rows.Count - 1, rCSV.Columns.Count)
            
            With .Sort
                .SortFields.Clear
                .SortFields.Add Key:=rCSV1.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                .SortFields.Add Key:=rCSV1.Columns(3), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                .SortFields.Add Key:=rCSV1.Columns(7), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
            
                .SetRange rCSV
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        
            .Cells(2, 1).Select
        End With
            
        With ActiveWindow
            .SplitColumn = 0
            .SplitRow = 1
        End With
        ActiveWindow.FreezePanes = True
        
        With rCSV
            For i = 2 To .Rows.Count
                If .Cells(i, 7).Value = "C" Then    '   CREDIT?
                    j = i
                    'same store and same date
                    Do While (.Cells(j, 3).Value = .Cells(i - 1, 3).Value) And _
                        (.Cells(j, 1).Value = .Cells(i - 1, 1).Value)
                        .Cells(j, 2).Value = "'9" & .Cells(i - 1, 2).Value         '   add leading 9
                        .Cells(j, 7).Value = "-C"                                   '  add marker
                        j = j + 1
                    Loop
                End If
            Next i
        
            Call .Columns(7).Replace("-C", "C", xlWhole)
        End With
        
        i = InStrRev(wbCSV.Name, ".")
        sCSV = wbCSV.Path & Application.PathSeparator & Left(wbCSV.Name, i - 1) & "-out.csv"
        
        Application.DisplayAlerts = False
        On Error Resume Next
        Kill sCSV
        On Error GoTo 0
        Application.DisplayAlerts = True
        
        
        wbCSV.SaveAs sCSV, xlCSV
        wbCSV.Close False
        
        Application.ScreenUpdating = False
        
        MsgBox "CSV Converted as " & sCSV
        
    End Sub
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    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

  13. #53
    The credit numbers look good. I have not been to my computer to test it yet. But I need all the credits on the bottom. The final results can’t have invoice and credit data mixed. Is it possible to move all the credits back to the bottom after the number has been fixed?

    Thanks Paul

  14. #54
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Programmers/Coders are by necessity, extremely pedantic. One missed punctuation mark, a typo, or misused syntax can ruin one's entire day. Please try to be more pedantic.

    I just spent three hours analyzing the Procedures in six workbooks you use to convert the raw data from three Distributors/Clients into Invoice and Return files for uploading into the accounting program, so I am a little salty. The three clients are Albany, MidState and Flexibake. Their Raw Data files are Albany.csv, Midstate.xls, and Flexibake.unk. Flexibake has the bad Return Invoice Number.

    Each client appears to have several workbooks associated: 2 @ Importing, 2 @ Prices, 2 @ Replacements, and of course, their Raw Data Files. Are there any I missed?

    I have the Importing and Replacements workbooks for those three clients, can you upload the rest for those three? Just those three, for now, I am trying to get my head around a universal or generic Data Structure.

    What is the ultimate purpose of the Replacements lists? Are these lists needed permanently, or will the new values eventually show up in the Raw Data?



    Ideally, the Price lists should refer to the current values in the Raw Data files and include

    1. All Product codes
    2. All Product Name/Descriptions
    3. All Sell Prices
    4. All Return prices (even when Sell and Return are the same value)
    5. Discontinued Item (a single character will suffice. ["a", 1, or "T"])


    I will be modifying all that into one workbook with a sheet for each Client with the columns

    1. Product code
    2. Replacement Code
    3. Product Name/Description
    4. Replacement Name
    5. Sell Price
    6. Replacement Sell Price
    7. Return price
    8. Replacement Return Price
    9. Discontinued Item

    Then I will write some simple Procedures so anyone can import a list of changes/edits and have the list applied.

    BTW, are your Distributors/Clients child businesses of your company? If so who sets/determines Product codes/names/prices? This knowledge would change the above desires/requirements




    Finally. I am only really familiar with two accounting programs; MS Excel and GnuCash. What Accting Software do you use?
    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. #55
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by joeny0706 View Post
    The credit numbers look good. I have not been to my computer to test it yet. But I need all the credits on the bottom. The final results can’t have invoice and credit data mixed. Is it possible to move all the credits back to the bottom after the number has been fixed?

    Thanks Paul

    Option Explicit
    
    Sub FixCSV()
        Dim sCSV As String
        Dim wbCSV As Workbook
        Dim wsCSV As Worksheet
        Dim rCSV As Range, rCSV1 As Range
        Dim i As Long, j As Long
        
        sCSV = Application.GetOpenFilename("ERP File, *.CSV")
        If sCSV = "False" Then Exit Sub
        
        Application.ScreenUpdating = False
        
        Workbooks.Open Filename:=sCSV
        
        Set wbCSV = ActiveWorkbook
        Set wsCSV = ActiveSheet
        
        With wsCSV      '   Guessing
            .Cells(1, 1).Value = "Date"
            .Cells(1, 2).Value = "Invoice"
            .Cells(1, 3).Value = "Store"
            .Cells(1, 4).Value = "Product"
            .Cells(1, 5).Value = "Qty"
            .Cells(1, 6).Value = "Cost"
            .Cells(1, 7).Value = "InvCred"
            .Cells(1, 8).Value = "Something"
            .Cells(1, 9).Value = "Counter"
            
        
            Set rCSV = .Cells(1, 1).CurrentRegion
            
            'save original order
            For i = 1 To rCSV.Rows.Count
                .Cells(i, 9).Value = i
            Next i
            
            Set rCSV1 = rCSV.Cells(2, 1).Resize(rCSV.Rows.Count - 1, rCSV.Columns.Count)
            
            With .Sort
                .SortFields.Clear
                .SortFields.Add Key:=rCSV1.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                .SortFields.Add Key:=rCSV1.Columns(3), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                .SortFields.Add Key:=rCSV1.Columns(7), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
            
                .SetRange rCSV
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End With
        
        With rCSV
            For i = 2 To .Rows.Count
                If .Cells(i, 7).Value = "C" Then    '   CREDIT?
                    j = i
                    'same store and same date
                    Do While (.Cells(j, 3).Value = .Cells(i - 1, 3).Value) And _
                        (.Cells(j, 1).Value = .Cells(i - 1, 1).Value)
                        .Cells(j, 2).Value = "'9" & .Cells(i - 1, 2).Value         '   add leading 9
                        .Cells(j, 7).Value = "-C"                                   '  add marker
                        j = j + 1
                    Loop
                End If
            Next i
        
            Call .Columns(7).Replace("-C", "C", xlWhole)
        End With
        
        With wsCSV
            With .Sort
                .SortFields.Clear
                .SortFields.Add Key:=rCSV1.Columns(9), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            
                .SetRange rCSV
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        
            'get rid of order column
            .Columns(9).Delete
        
            'row 1 was originally blank
            .Rows(1).Resize(1, 8).Value = " "
        End With
        
        i = InStrRev(wbCSV.Name, ".")
        sCSV = wbCSV.Path & Application.PathSeparator & Left(wbCSV.Name, i - 1) & "-out.csv"
        
        Application.DisplayAlerts = False
        On Error Resume Next
        Kill sCSV
        On Error GoTo 0
        Application.DisplayAlerts = True
        
        
        wbCSV.SaveAs sCSV, xlCSV
        wbCSV.Close False
        
        Application.ScreenUpdating = False
        
        MsgBox "CSV Converted as " & sCSV
        
    End Sub
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    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. #56
    SamT

    Each client only has one item price file, 2 importing and two replacements. I will attach the files you asked for.

    Ahold is one other client I do weekly. I can work on that later just wanted to let you know.

    With the replacement and item price files I do make adjustments often. New stores and items are added at random times. Also, prices can change for individual distributors at random times.

    The price file refers to the price we charge the distributor. There are times the distributor can sell an item at a discount and I do not know ahead of time.
    When there is a discount or a product is sold below full price, we add the items into the accounting program at full price then we create a line item as it is called within the accounting program ‘very similar to an item”. The line item is the total discount of all the items discounts added together.

    Albany does not supply us with the price they sell the product for. I have asked them many times but they tell me it is not possible. They are in the process of deploying a new system and tell me once that gets deployed, they will be able to. They have been deploying this system new system for over a year and I am still waiting. I have not needed this feature with them yet and not sure if I will.

    The distributors are businesses we hire to deliver our products. They determine the product codes and names. The distributors do not change often. I have worked here two years and in the last two years they have stayed the same. Prices are determined when our Chief Marketing Officer talks with the store management, that is also how discounts are put into place. With flexibake I have control over prices and names.

    The distributors receive a percentage of the total sales.

    The accounting program we use is call Quickbooks Enterprise version. The program I use to upload the data is called transaction pro.
    The flexibake data comes from an ERP program I am deploying for our in-house drivers and other distributors that do not currently use handheld devices. They currently give us paper invoices. As of right now I only have flexibake handheld devices deployed to the in-house drivers. I am hoping to get this to the other distributors within the next month. I am first waiting for the ERP company “Flexibake” to add a credit identification number to the paper print out before I can deploy it to other distributors. We have 15 other distributors that all use paper invoices. These have 1-5 drivers each. We have 2 in-house drivers.



    50% of our sales are on paper invoices. This is one main reason I was hired. To eliminate the paper.
    Attached Files Attached Files

  17. #57
    Paul


    How do I use your macro. I created a new module. When I run the module it ask me to chose a file. I chose the data file then I receive a runtime error 104.

    I attached the xlsm file results after I run the macro.

    Thanks


    Attached Images Attached Images
    Attached Files Attached Files

  18. #58
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    All the macro does is

    1. Get the name of the CSV file
    2. Open the file as a new workbook (wbCSV) and a new active worksheet (wsCSV)
    3. Add column headers to wsCSV
    4. Add row referance in col 9 to go back to original sort order


    At this point THE NEW WORKBOOK that was opened from the CSV should look like this

    Capture.JPG

    This is NOT the workbook with the macro

    5. Then the macro sorts, looks for credits with the same store and date, adds a "9", etc.

    6. The the macro sorts back to original order, cleans up wsCSV

    7. Saves wbCSV as a CSV file, and then closes without Save the wbCSV


    The fact that the WB you sent with the macro has a sheet that looks like this

    Capture1.JPG

    makes it seem to me like you are making changes to the wrong workbook so that

            Set rCSV1 = rCSV.Cells(2, 1).Resize(rCSV.Rows.Count - 1, rCSV.Columns.Count)
    fails because there's only data in the first row, OR the CSV file was completely empty

    All I can suggest is that you first run the macro in the attached workbook to see if it works

    I added some debug MsgBox statements just in case
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    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

  19. #59
    Paul


    It is working from your xlsm file. The first time I copied your code into a new module I created. That is when I would get the error after I chose the csv file.
    When I run it from the xlsm you provided it worked great. Thank You much.

  20. #60
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I renamed all the modules in the 6 importing books I'm using, Then I prefixed the Sub calls so I would know which module each was in. Note: None of this effected the actual code in any way.

    ModuleNames.jpg
    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

Posting Permissions

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