Consulting

Page 1 of 6 1 2 3 ... LastLast
Results 1 to 20 of 117

Thread: Convert Many Invoice Formats to a Standard Format

  1. #1

    Convert Many Invoice Formats to a Standard Format



    Hi all








    I have been working on converting a company from manual entry of invoices to being digital. This is a company with 10,000,000 in sales andstill doing everything with paper invoices and manual entry into the accountingprogram.


    I do currently have them converted just some issues I am still trying to fix.


    I have a program I use with handheld devices that they use when making delivery’s “ERP program”. I collect all the data with the ERP program and then the data is exported from that program so I can import it into the accounting program.


    I have everything all set and am able to export all the data. I then am able to import it and works well.


    One of the last issues I am having.


    We have invoices and credits. The ERP program does not have a credit note number on the paper print out. The ERP program creates the credit note number after it is uploaded to the ERP program. So the number in the software is not listed on the paper print out the customer receives.


    Since the stores do require the credit note to have a number to identify them for now, I am using the invoice number and just putting the number 9 in front of it. I was able to get company that created the ERP programto do that.


    Since the credit note does not get assigned a number till it is uploaded into the ERP program there is no way for use to locate that credit note number within the account program.


    I have attached an example of the file exported that I useto import the data into the accounting program.





    If you look you will see the credits listed on the bottom ofthe list. The credit notes numbers “629,630,631,632” are of no use to me. Each store that has a credit also will have an invoice.


    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.


    The store could be listed more than once since this report will be ran weekly. That is why the store name and date must match.





    That might be confusing. I am hoping I am explaining it so it could be understood. If anyone wants to work with me “help me out” and try to figure out a way to complete the task I need I would be very thankful.


    Or if anyone knows where I could get help to complete this task please let me know.


    For me I know sometimes I find task I like to try and find away to make it work when it is in areas I know about. Maybe the same for someone else.
    Attached Files Attached Files

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    You're pretty smart, so I am just giving hints and steps

    After importing the csv, find and name the bottom Right cell of the invoices section
    Set InvBot_R = Cells(Rows.Count, "H").End(xlUp)
    Copy the invoices to another book ("Working_" & Date & ".xlsm")
    InvBot_R.Offset(, -5)Select
    Selection.End(xlToLeft).Select
    Selection.End(xlUp).Select
    Selection.Copy '(to New Book.Sheets("Invoices"))
    Copy the returns(Credits) to a different sheet in the new book
    InvBot_R.Offset(-1, -1).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlDown).Select
    Selection.Copy '(to New Book.Sheets("Credits"))
    You're done with the ImportCSV book, Close it.
    The below refers to "Working_" & Date & ".xlsm"

    With Sheets(" Invoices")
    Columns should be Date | Inv# | Store
    Filter, in place, for Uniques
    Move Store before #Inv
    Insert new column before Store
    The formula in this column is "=Right($D1,3)"
    Copy down

    This should be enough to allow you to complete the task.
    The sheets are sorted, so an array is almost as fast as a Dictionary

    CYA: Permanently save "Working_" & Date & ".xlsm".
    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. #3





    I am not that smart when it comes to VBA. I do currentlyhave a xlsm file. I take the data from the csv and past it into that. Ireceived a lot of help to create that. Mostly people from this site worked withme and wrote the scripts for me. I learned some very simple task but not much.Mostly I just figure out things when I need small changes. So, the attachedxlsm files are what I use to convert the data.





    Since I do credits and invoice separately, I need to havea different file I use to add the credit note number. Then after the creditnote has numbers I then can split the data into the two attached files toconvert it to a format I can use to upload into the accounting program.

    When I convert the data I need to do credits and invoices separate

    Thanks for help but I do not understand any of what you are saying.


    I do want to learn but don't plan to use VBA much after this. I am just in need of this soon. If I must I can get the company to pay to have it completed.

    I will have hundreds of invoices and credits to importeach day. For over 30 routes. I am hoping for a script that can do all thetask.
    Then I will just run the macro and have it completeeverything.



    I have created the others but asking for help fromothers

    Each script people have helped me with I do go over andtry to learn how it works. This on just has nothing like any of the others. Thesearching part where it matches date and store name to take that invoice numberand move it to the bottom is where I have no clue at all
    Attached Files Attached Files
    Last edited by joeny0706; 09-10-2020 at 12:12 PM.

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    The searching part where it matches date and store name to take that invoice number and move it to the bottom is where I have no clue at all
    I'd "fix" the CSV. This does some sorting and if date and store for a credit ("C" in col 7) match to an invoice then it makes the credit invoice number to that number

    Capture.JPG


    Then you can do whatever you want with the fixed CSV data


    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
        
        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?
                    If .Cells(i, 3).Value = .Cells(i - 1, 3).Value Then         '   Same Store
                        If .Cells(i, 1).Value = .Cells(i - 1, 1).Value Then     '   Same Date
                            Cells(i, 2).Value = .Cells(i - 1, 2).Value         '   make invoice same
                        End If
                    End If
                End If
            Next i
        End With
        
        ThisWorkbook.Activate
        
        On Error Resume Next
        Application.DisplayAlerts = False
        ThisWorkbook.Worksheets("Data").Delete
        Application.DisplayAlerts = True
        On Error GoTo 0
        
        ThisWorkbook.Worksheets.Add
        ActiveSheet.Name = "Data"
        
        rCSV.Copy ThisWorkbook.Worksheets("Data").Cells(1, 1)
        
        wbCSV.Close False
        
        Application.ScreenUpdating = False
        
    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

  5. #5
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    The Module indexes for the two attachments

    Flow Control Subs listed below. The Comments in re Modules are only here in these code blocks, not in the attachments

    For Flexibakeinvoices1.xlsm
    Sub runall()               'Module21
        Call RenameActivesheet 'Module2
        Call CRemoveG          'Module41
        Call Replaces          'Module1
        Call addClassAll       'Module9
        Call Template          'Module3
        Call AddDiscount       'Module8
        Call AddRow1           'Module14
        Call ZeroRemoveH       'Module4
    End Sub
    For FlexibakeCreditMemos.xlsm
    Sub runallReturns()           'Module6
        Call RenameActivesheet    'Module2
        Call IRemoveG             'Module41
        Call ReplacesReturns      'Module31
        Call addClassReturns      'Module1
        Call Negative             'Module12
        Call Template             'Module3
        Call AddDiscountReturns   'Module10
        Call AddRow1              'Module14
        Call ZeroRemoveH          'Module4
    End Sub
    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

  6. #6
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Joeny,

    I have gone over both your attachments, and, IMO, you need a complete refactoring. I guesstimate that would take ~ 4 hours. Call the Number at: http://www.vbaexpress.com/ for assistance

    Disclaimer: I do not work for, nor am I paid by, this Forum. I do have nearly 20 years with VBA for Excel and volunteer as a Moderator here at VBAX.
    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. #7
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by SamT View Post
    Joeny,

    I have gone over both your attachments, and, IMO, you need a complete refactoring.
    Won't argue with that, but since there's no data in any of the attachments in #3, but only a bunch of macros that seem to insert formulas, it would seem that a VBA solution to just insert the results, etc. would be easier.

    Maybe adding the data would make it easier to visualize a solution
    ---------------------------------------------------------------------------------------------------------------------

    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

  8. #8
    I have attached the export file and then the two xlsmfiles I use to convert the data. Right now, I use these xlsm files daily toconvert data from other sources. I have done it this way for a couple years andworks perfect with data I receive from other distributors. From them the creditand invoice number are already correct.


    I am not sure what a complete refactoring is. But thingsare working perfect and have for years for data I receive from otherdistributors. It may not be the best way or most productive but it does the jobwithout any problems.

    I convert the invoice data and credit data separatelysince the program I use to upload the data needs it that way. Can only doinvoices or credits. Can’t upload both at the same time.
    I have attached the two files used to convert the dataalso. In both files I have a runall macro. I paste the data in and run therunall and then I am done. These two I attached have data in them that I would get from theexport file. Those will be used after I change the credit number like I need.Explained below.


    In the export file Line 178-186 has the invoice data andlines 188 and 189 are the credit data. What I need to do is create a macro thatwill change column B on line 188 and 189 “the credit number” to match theinvoice number with a 9 in front of it. So the invoice number is 111991. I needcolumn B lines 188 and 189 to say 9111991. Once I run a macro to change thecredit number, I will then split the credit data and the invoice data into thetwo xlsm files to convert it to a format needed to upload into the accountingprogram.

    I do need to keep the lines in the order they are. I cantmove them like suggested above since I need to split it “invoice data andcredit data” to convert the data
    Attached Files Attached Files

  9. #9
    Paul_Hossler

    I got thinking and there is a chance of what you say will work. But it would be a lot of moving around and a lot of stuff I have no idea how to do. I am just thinking of a concept. If there is a way to do what you say then add a 9 in front of column b for the credits after you copy the numbers from the invoice. Then once that is done move all the lines with a C in column G back to the bottom. That is a lot of things I have no idea how to do.
    Last edited by joeny0706; 09-18-2020 at 01:12 PM.

  10. #10
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    A complete refactoring means... Start from the data and convert it to a format needed to upload into the accounting program.

    To do that, we need to know the formats the accounting program needs for each.

    It will be very helpful to know what each column of the Data, both Raw and formatted, represents, or means, or is called/named.

    For Example
    Raw Data:
    Column A. Represents Date of Transaction
    Column B. Serial Number
    Et al. ...
    Column G. Transaction types; "I" means Invoice and "C" means Credit
    Etc. ...

    Credit Report:
    blah
    blah

    Invoice Report:
    blah
    blah

    An upload with three sheets, each with simple, common headers, as programmer notes, and a couple of Rows of correctly formatted data will suffice. The Columns' explanations and any other notes can be below that.

    What you will probably get back from this forum will be code that is well organized, fast and efficient, easily understandable and maintainable, and, importantly, written in only one style.
    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. #11
    I added headers to all the columns.

    The Process I use to convert the raw data into a format I can upload is outlined below.

    I will first export the data. The raw data file is what I will have from the export. I will take the data and copy all the invoice data and paste it into the invoice conversion file. “Attached file invoice conversion before has the data before running any macros.”. I will copy all the raw data and then delete the data in column G. Column G is only used for reference so I know what data to place in what file “invoice data, credit data”. Then I do the same copy and paste with all the credits into the credit conversion file.
    I also attached a file called “invoice converted and credit converted”. That is in the format I need to upload. I have macros that do a lot already. They convert the customer names to match the names within the accounting program and also convert the items to match. I have one that add the correct template. “Template is a field in the accounting program I need”. Also add the class, calculate the discount along with other fields needed to do the import. The conversion files works great now. I received a lot of help from this forum to create them.

    So what I need to do is convert the credit number to match the invoice number with a 9 in front. After I do that I will copy the data out and put it into the conversion files.

    The attached file credit converted shows both how it will convert and also under that shows how I need it to be. The only difference is the credit number “column B”.

    I have everything all working perfect.
    It is just that the credit number does not match the credit number on the paper printout handed to the customer.

    So I need the credit number on the paper print out to match within the accounting program. The paper print out with have the invoice number with a 9 in front of it to be used as a credit number.

    In the raw data file column G has I and C. "I" means Invoice and "C" means Credit

    Some of the macros reference other excel files to get item prices and convert data. I have one that looks at another excel file to find what to convert the names and items to. These are nice because it is easy to add new customers and items to when needed. Also to change prices is very simple. I attached a copy of those also so you know what the macros are referencing. Those 3 files are “flexReplaceDataInvoice.xlsx, FlexReplaceDataReturns.xlsx and Flexitemprices.xlsx”


    Thank you all for the help. I have been working on this for a year with the ERP company. I am at the final stages.
    Attached Files Attached Files
    Last edited by joeny0706; 09-18-2020 at 08:35 PM.

  12. #12
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I have attached a brief synopsis of the answers to the Questions Joeny provided.

    While I believe that a single workbook can automatically create two Printable reports in less than one minute, I think Joeny truly only wants a new Sub that will modify certain numbers.

    I give up. Someone else take over, since I refuse to do Programmer Horrors.
    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

  13. #13
    SamT


    I appreciate you working with me. I apologize if I am not answering your questions correctly. I am not sure the best way to get this done. I like that way I have since it has worked for years. I use these conversion files for 7 other distributors that I receive raw data from. I like the way I have it now because it is easy to modify when I get data from a new source. Each source gives me data a different way.

    From the raw data I receive I always just need the date, number, customer, item, qty and sometimes price. I only need price when it is from a distributor that gets discounts. Not all of them do. Since I have the excel sheets the macros references to convert their customer names and item numbers to match what I need in my accounting program it is easy to convert the process when I receive data in a new format.

    If you know of a better way to complete the process I am all about it. I did not explain I use this process for many other distributors raw data. Maybe I should have.

    I am open to any options that will work. Currently I will copy the data I need from the raw data, paste it into my conversion file and run the main macro. Then I just save the file and upload it into the accounting program.

    As I said I am open to any way to change their raw data into a matching format. I was just thinking if I could have one macro to create the creditt numbers like I explained above that would be easiest then I can keep the reset of the process the same.

    The data I get from my ERP program will be the only raw data I need to fix the credit numbers.


    I attached an excel file that has 6 sheets. Each sheet is raw data I receive from different distributors. They data comes in different order and also include a lot of data I don't need. I just pick out the columns I need then do the conversions.

    I will be getting more raw data from other distributors soon also.


    A couple facts I should explain about the uploading process.

    The converted data needs to be in two different excel files. One with credits and one with invoices. It must be an xlsx file with only one sheet.


    I am guessing the way I have things are a mess. I have had different people help at different times. I have had experts do some and then I have also modified some macros on my own. When I have done it was a lot of trail and error. I have never used excel macros before last year. I was a network engineer who worked on the road at different business then I took the current job I have now. I do little of everything here. I like the challenge at times. Excel has never been a strong point of mine. So I can guess the conversion files are probably not efficient and a mess.

    Thanks Again
    Attached Files Attached Files
    Last edited by joeny0706; 09-19-2020 at 06:51 PM.

  14. #14
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    You said: I have since it has worked for years... I have never used excel macros before last year.
    OK

    Imagine an old network, from the '90s and 00's, one built by several different IT companies. One that used a Token Ring system, for a small office with several Computers using different Operating Systems. And they want you to upgrade the system because they suddenly have 100 Desks all running several newer OSes.

    Even though you could keep using Token Rings and just add some hardware to the existing system, would you?

    That's what I feel like when I look at your current VBA.



    Please think of us here at VBA Express as VBA Software Engineers. We have a specific lingo that you have been missing.


    • Raw Data is straight from your distributors, via Email, CSV, Google Files, or the ERP.
    • Processed Data is the output of the ERP.
    • Imported Data is what is on an Excel Sheet.


    It may be that one or more are the same, but, be specific. As it is, We Don't have a clue since you have said that the Excel sheets are Raw Data. As long as you show us the earliest version you have regular access to.

    At my current understanding of your requirements, I see One Macro Enabled Workbook with 6-7 Sheets
    • Price References
    • Product Code References
    • Product Names References
    • Two Templates
    • One or two Column Cross Reference Sheets


    The Column Cross References will be used to cross the various columns of the Imported Data to the Templates/
    All this is to make it easy to add new Distributors and maintain price lists, Product name replacements and Business names reference in one place.

    It is also essential to creating fast and efficient code. Constantly referencing Worksheet Ranges in different Workbooks is as slow and error prone as Token Rings and Routers in a network. Any good code will be using Arrays for all such references
    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. #15
    SamT


    I am understanding what you are saying. That does make complete sense. When I have said this has worked for years. I started in 2018. That would be the years I meant. So only two years. I do appreciate your help. I think I am understanding the idea you are saying. If I am understanding correctly it sounds much better than having to copy and paste. If you are willing to help I am open to other ways to do it. I guess I was just worried about the change. But as you point out change is what is needed to make the process more productive and better. How I have it now is a mess I am guessing. A lot of the macro enabled excel file. some of it was just me with trial and error. So I am positive there are better ways. But at this time I have many task I am working on at once. I wish I had more time to commit to this task but that is not an option right now. So my current objective to start is getting all the raw data imported.

    If you are still willing to help just let me know what you need and I will do my best to provide you or answer your questions. The xlsx file I provided in the last post with 6 sheets was raw data that I receive from other distributors.

    Facts about the raw data.

    One is more difficult since the credit raw data is under the invoice raw data. So it is a pain when I need to search and copy out just the credits to get them alone. I do receive 8 weeks at a time from this on distributor. Each week in a different excel file. I combine them all into one to convert. With that one it does take up to an hour to do the 8 weeks and import it. I attached a file called midstate. That it the one I speak of. If you look at the two sheets within they have all the invoice data on top and the credit data underneath.

    There is another one where I need to exclude just one chain to get all the other chains I need. So my process does have a lot of work to get the correct raw data. That one attached is called albany. With that raw data I need to filer it first and exclude 5050000 in the chain column "C"

    As you see I need to alter the process slightly for each one.


    I am not sure how much work anyone would be willing to put into helping me to make the complete process automated, I believe that would be many hours of work. I don't think anyone would want to do that free of change. My company would not be willing to pay at this time since they see the process works as is. To them as long as it is working that is good enough. That is a very common thought as I am guessing you have came across. Two years ago all this was manually entered. They had more than 4 employees in data entry. Now they are down to one. Soon I will be the only one importing the data. There will be no manual entering. Each week will take me less than half a day. Compared to a week for all for former employees. To my boss that is better than expected.

    I would be happy to just resolve my main issue. That would be fixing the credit numbers to have the invoice number with a 9 in front of it. Maybe making the process more productive as it is at this time. That would complete setup of the importing process. Of course making the whole process better, faster and more productive would be great. I think it might be an option to pay to get it done right in the future. That could make it so I am not the only one who is able to do the converting and importing. I explain to my bosses they always want more than one person to do all task. It is not good to be dependent on any one person in any place of business.

    If you are interest the program I use to do the importing is "Transaction Pro Import wizard plus" It imports into Quickbooks.
    Attached Files Attached Files

  16. #16
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I would be happy to just resolve my main issue. That would be fixing the credit numbers to have the invoice number with a 9 in front of it.


    Going back to post #4 which reads the CSV and matches Credits to Invoices, if you change this


     Cells(i, 2).Value = .Cells(i - 1, 2).Value         '   make invoice same


    to this

     Cells(i, 2).Value = "'9" & .Cells(i - 1, 2).Text ' make credit = "9" plus invoice number
    would that work??
    ---------------------------------------------------------------------------------------------------------------------

    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

  17. #17
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    exclude just one chain to get all the other chains I need
    Now you're a land Surveyor? Hey, IT guy! See that big black box on my desk? The one with a thumb drive plugged into it and all the wires on the back? That's called a Modem. The TV screen in front of me is called a Computer.

    BTW a Chain is 3.999992 Rods. I have no idea what "Chains" you're talking about.


    just let me know what you need
    OK, but they will be simple explicit questions that require a simple explicit declaratory answer, No explanations, no Train of Thoughts, no nothing but simple answers. Lets start from the beginning and work towards the ending.


    1. When a Data file from a Distributor first appears on your computer, what Folder(s,) (complete Path, please,) is it [are they] located in? List all
    2. What are the Naming Patterns each Distributor uses? Provide at least two examples of each
    3. What File Types does each Distributor use for their Data 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

  18. #18
    Paul

    I have done some testing but not sure how to run that macro. When I do it ask for an erp.csv file. I select the file with the raw data but I must be doing something incorrect. Also the credit data needs to be on the bottom. It can not be mixed within the invoice data. If we can do what post 4 shows then move all the lines with C in column G back to the bottom that seems like it would work.

    Thanks Paul


    Samt

    I apologize for not being clear. I was in the middle of a task when I replied. I should have waited until I had more time. In post 13 I posted an excel file with a sheet called Albany. The sheet has a column "C" labeled chains. The point I wanted to explain is the amount of work that would be required to automate this process would be a lot of work. If someone is willing to work on the complete process I can explain everything with much more detail. Each file with raw data I receive from the distributors comes in a different format. Some of them are similar others are very much different.

    My boss is on my A** to get these handheld devices deployed to all the distributors. 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. Until I get that fixed I am not able to start the deploy process. Even if this is just a macro to resolve that issue then work on the complete process after that will be acceptable. I would love to have it all an automated easy process. Then this will not be dependent on me and take a lot of time away from my other task. It is just that I do not have the knowledge to do that on my own yet and I am not sure if anyone is willing to do that for free.

    Attached is the raw data file "export file.xls". This is the file I will use when I can convert the credit numbers before I convert all the data to import into the accounting program.
    In this export file it has two sheets. The first sheet "export file" has the raw data from the ERP system. The second sheet "fixed credit numbers" shows the goal I am trying to achieve. You will see the credits "rows 187-195" have the credit identification numbers converted with the invoice number and a 9 in front of it.

    Column G has either an I or a C. The I means that is invoice data and the C is credit data.
    Attached Files Attached Files
    Last edited by joeny0706; 09-22-2020 at 07:09 AM.

  19. #19
    I was just working on answering your questions. I am getting more detail so I have complete answers. Will have them shortly

  20. #20
    OK, but they will be simple explicit questions that require a simple explicit declaratory answer, No explanations, no Train of Thoughts, no nothing but simple answers. Lets start from the beginning and work towards the ending.


    1. When a Data file from a Distributor first appears on your computer, what Folder(s,) (complete Path, please,) is it [are they] located in? List all
    2. What are the Naming Patterns each Distributor uses? Provide at least two examples of each
    3. What File Types does each Distributor use for their Data Files?

    [/QUOTE]



    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


    I will provide some of the files and you can see the formats
    Attached Files Attached Files

Posting Permissions

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