Convert Many Invoice Formats to a Standard Format

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.

Quote:

---

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.

Quote:

---

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

Quote:

---

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.

The converting only takes 15 min. The 3 hour part is when importing 10,000 rows into the accounting program.

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.

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?

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?

Quote:

---

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

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.

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

Code:

---

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

---

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

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?

Quote:

---

Originally Posted by joeny0706

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

---

Code:

---

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

---

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.

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


http://www.vbaexpress.com/forum/imag...AAAElFTkSuQmCChttp://www.vbaexpress.com/forum/imag...AAAElFTkSuQmCC

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

Attachment 27226

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

Attachment 27227

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

Code:

---

        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

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.

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.

Attachment 27232

Flexibake is not only a software company specializing in Bakeries, it is also a CodeName for in house distribution agents and a code name for in house Accounts Receivable Reports, Why not name the inhouse bits "InHouse" so you don't have to keep mansplainin' it to every noob? (|;< )

A quick review of Flexibake's website indicates that they may not be offering any handheld hardware devices at this time. It's been 3 1/2 decades since I did any Retail B2B Distributing, but even in the '80s the Handheld devices we used had a builtin Modem to talk to a computer at the head office, Most such devices today are either wireless or Cloud capable, I don't understand why you are limiting your company to paper reports,

Using Transaction Pro as an intermediary (along with Access 10) to import xlsx files into Quickbooks Enterprise may be a helluvalot slower than using Quickbooks native IFF import functionality.

See: https://quickbooks.intuit.com/learn-...ders/00/201577
And download: https://support.quickbooks.intuit.co...import_kit.zip

Can I safely assume that the FlexItem price list is a complete list of all Corporate product names and Sell prices.

SamT

We are only using Flexibake for the ability to create our bakesheet "Is the production schedule" and DSD mobile app. When I say handhelds I am refereeing to cell phones and tablets. These are what they will be using to run the fleixbake app. They will then be printing to a zebra mobile printer.
We are eliminating all paper reports. I am not sure what reports you refer to when you say paper reports. I might have not explained a part of the process correctly.
The only paper will be the print out from the zebra printer of the invoice and credit memo details. The stores all require that.

The accounting ability of flexibake is very limited and does not offer many things we need.

I have used Quickbooks for many years. The IIF might be faster but the problem with IIF is it does not offer the amount of detail transaction pro does. There are many of fields and settings we need that IIF files just do not allow for. IIF just dont have the ability to import the data as I need. Also importing IIF is very touchy. I have had it cause database corruption more than once. I have worked with the Quickbooks support for many hours. One time I had to manually rebuild a company file. After days of working with Quickbooks support they said that was the only option to get things back to normal. None of their tools to fix the problem worked. I had to export everything then use tools to import it back into a new company file "database". This was 2 weeks worth of work. It took so long because the amount of data. It would take 5 hours just to export a months worth of invoices. I rebuilt it to include the past two years of data. That is what the management wanted.

The FlexItem list I provided was missing an item. I attached a updated list that does include everything.
We do add and change items at times but as of right now this is a complete list.

Quote:

---

Originally Posted by SamT

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.

Attachment 27232

---

As I previous said I am new to VBA. I did not see how to rename them. When I right click it is not an option. I never did google how to rename them but that will be helpful with detailed names. I know at times I am looking at them and not having a name does make it a pain to find the one I wanted.

I just looked it up and very simple. Just press F4

Thanks that will be helpful.

In my previous post, the "Paper Reports" I was afraid you were talking about were the Raw Data this Project of ours was dealing with. Whew. Glad I was wrong.

Welp. We Work with what we have. Which is

• Raw Data Files of csv and xls
• The Project
• Transaction Pro + Access
• Quickbooks

But we can ignore Quick Books for The Project. I am tempted to preconvert that xls file to a csv, Outlook can handle that before The Project ever needs it. Having nothing but csv files means one less file type to code for.

Having a Directory tree like C:/Data/FolderName/Archive means the code only has to worry about FolderName. All other file names are just Strings only used for SaveAs purposes.

You understand that The Project will only speed up the process of converting Raw Data to Transaction Pro inputs? I will make The Project easier to comprehend and maintain, but, that will be the only real benefit. Reducing 15 minutes to 2 minutes isn't much of a change to 3 hours 15 minutes.

You have a Data Field Map from the Excel generated Reports to Transaction Pro, I would like to see it. It can be in the same XL Book as the next request. The Tab Name can be T-Pro.

I need Column Import Maps for each Raw data file you use when you import them into Excel. I know that some Raw Data files don't have Header Rows so column numbers are OK. Please put them all into one XL Book and use FolderNames for Tab Names

The T-Pro tab

• The first Row should be the Map Field name (indicate empty columns)
• The second Row (Required) should be the XL Report Column Header name
• The third row (Required) should be the Data Type required by T-Pro
• The Fourth Row(Required) should be an example of any Formatted Inputs (Ie Dates... "yyyy/mm/dd" ???)

The Column Import Map Tabs

• The first Row should be the Map Field name (indicate empty columns), or your best guess. This is for informational use, the code won't use it.
• The Second Row (required) will be the Field number of the CSV file. This is what the code will use
• The Third Row (Required) will be the XL Report Column Header name. There will be blanks in this row, because you must map Columns to used Fields.
• The Fourth Row(Required) will be the Data Type required by T-Pro. Same as the T-Pro tab

Once I have all that, I think I'll have enough to get started, I will be creating at least two xlsm books, named ProjectName_Master.xlsm and ProjectName_References.xlsm.

Use your imagination and give me a good ProjectName (|;>)

SamT


Speeding up the process has not been a goal at all. Of course, that would help. It would be nice to not have to copy and paste all the data into the xlsm file.
The main goal was originally to get the credits fixed. We then started the process of fixing my errors and making the process more efficient and correct.
Now after working with you one of the goals would be to make the process easier to comprehend and maintain. After that I can possible have others do the converting and importing. It is just with the way I do it now would be difficult to teach an office staff how to do it. Even to make it easier to maintain would be great for me also. I am still unsure if I would want anyone else to do it since making a small mistake during the upload process could cause very serious problems. I can worry about that in the future. Also, once I see how you process works will help me in deciding that.

T-Pro allows me to create data-map .dat files. When I am uploading the data, I can choose the type and it populates the mapping. I will attach the 4 .dat files.
I am working on the excel sheets for you. I am not sure if I am understanding how you want it. I will fill it out to my best and you can let me know what I need to fix

Open a .dat with Notepad, copy the ext and paste it, Transposed, into Excel. Annotate that Row so I know what is what about it.

The only info I have at the moment is what T-Pro provides with it's Helps. 37 generic Sample Maps.

What I need to do is Map the transformation from Raw file to Quickbooks via T-Pro. Technically all I absolutely need are the Raw Data Field numbers Mapped to the nine Columns of the T-Pro input Reports that ThisProject creates. In reality, the more I know, the more comprehensible the system I create can be and the easier it will be to use and teach.

At 3:00 AM, your Returns invoice number woke me out of a sound sleep. Returns have no correlation to sales invoices. It seems to me (but I haven't been paying too much attention to that issue,) that you are trying to force a correlation there. QB-Enterprise is very customizable. IMO, it would be best if you merely added a Unique "Returns" Prefix to FlexItem's Return short Invoice numbers and added that function to QB.

I will send you some screen shots also of T-pro. I am able to setup the mapping by choosing the columns I want to import and match up to the fields in QB wihtin T-pro. The order of the columns on the import file does not matter since I map it once I get to that step. In the screen shot below you also see load map. I have different maps. One for midstate and Flexibake. “Those only two with discounts. I then have another map for the other two. “Ahold,Albany”.
I can have the map and if others do the import all they will have to do is load the map and it will configure all the settings for them. If it is setup correctly so the Column header names match on all the import files I could use the same Map for all of them. If the same xlsm file is used to convert all the raw data only one saved map will be needed.
In the zip folder it has the .dat files and also xls files.
Also the word file has the screen shots within it.


Yes Return identification numbers Flexibake creates have no correlation to sales invoices. That is the original reason I started this post. To create the correlation and a number that matches what is on the DSD print out.
At first there was no credit memo number on the print out that is handed to the customers. They all require one.

So since this needs to be on the print out it needs to be created before it gets to QB. Flexibake said the credit number is not created until the credit is within Flexibake. They only thing I was able to get Flexibake to do was put the invoice number on the credit print out.
We did have a prefix to the invoice number. The first thing the print out had was CM-12456789
CM-“Invoice Number” was an issue the for the drivers when they got to the stores some of them would not allow letters. It had to be all numbers. So, if it is the invoice number with a 9 in front of it is easy for Flexibake to add to credit memo print out and it will also create a correlation between invoice and credit.



Albany Map
[IMG]file:///C:/Users/JoeF/AppData/Local/Temp/msohtmlclip1/01/clip_image001.png[/IMG]
Albany Map
[IMG]file:///C:/Users/JoeF/AppData/Local/Temp/msohtmlclip1/01/clip_image002.png[/IMG]

Midstate
[IMG]file:///C:/Users/JoeF/AppData/Local/Temp/msohtmlclip1/01/clip_image003.png[/IMG]

In the future their may come a time where PO is needed. I will be able to choose what column to export it to out of Flexibake. If you are able to setup a column for PO in the conversion tool that could be helpful. As long as I know what column to export the PO to if that does become a needed column that would be great.
I am trying to get Flexibake to give the driver the ability in the DSD app to enter a PO. As of right now that can only be added from within Flexibake and that is not helpful. They said in might be possible in a future update.

I have a question about adjusting one of the macros I have in my conversion xlsm files. Should I start a different post or asking in this one is fine?

I have the macro code below look within my xlsm file and replaces text. It has a different excel file it uses to know what to replace. I am having an issue that it is replacing items it should not. I have attached the xls files it uses to know what to replace and the raw data file that it searches.

The issue is it is searching all the columns. I only need it to search column D for "Call ReplaceAllSheets(Worksheets("Data").Range("A1"))". Then for both "Call ReplaceAllSheets(Worksheets("Data").Range("D1")) and Call ReplaceAllSheets(Worksheets("Data").Range("G1")) it only needs to look in column C. It is replacing items in the wrong column. How can I restrict it to only search column D for products and only search column C for customers?












Option Explicit

Code:

---

Sub Replaces()
    Dim wbData As Workbook
   
    Application.ScreenUpdating = False
   
    'delete Data is if still exists
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("Data").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
   
    'open Replaces workbook and copy data sheet in
    Workbooks.Open Filename:="C:\FlexibakeConversions\FlexreplacedataInvoice.xlsx"  '  <<<<<<<<<<<<<<<<<
    Set wbData = ActiveWorkbook
    wbData.Worksheets("Data").Copy Before:=ThisWorkbook.Worksheets(1)
    wbData.Close False
   
    ThisWorkbook.Activate
   
    'do the replaces
    Call ReplaceAllSheets(Worksheets("Data").Range("A1"))
    Call ReplaceAllSheets(Worksheets("Data").Range("D1"))
    Call ReplaceAllSheets(Worksheets("Data").Range("G1"))
    'get rid of Data
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("Data").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
   
    Application.ScreenUpdating = True
End Sub

---

Code:

---

'this sub is Private so that it's only usable in this module
Private Sub ReplaceAllSheets(R As Range)
    Dim i As Long
    Dim ws As Worksheet
    Dim r1 As Range
   
    Set r1 = R.CurrentRegion
   
    If r1.Rows.Count < 2 Then Exit Sub
   
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name = "Data" Then GoTo GetNextSheet
        If ws.UsedRange.Cells.Count < 2 Then GoTo GetNextSheet
       
        For i = 2 To r1.Rows.Count
            ws.UsedRange.Cells.Replace What:=r1.Cells(i, 1).Value, Replacement:=r1.Cells(i, 2).Value, _
                LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
                SearchFormat:=False, ReplaceFormat:=False
        Next i
GetNextSheet:
    Next
End Sub

---

Quote:

---

The order of the columns on the import file does not matter since I map it once I get to that step.

---

LOL To quote you: Your system is a "mess."

Your current system might not, but this comprehensive and logical system I'm trying to create does. If you don't want to give me what I think I need, just let me know and I will send you a Tarball of the 93 MB of stuff that I already have.

I think I need Maps for every distributor. Preferably from Raw data to T-Pro Import Report to QB. At least from RawData to T-Pro Import memo

Quote:

---

In the future their may come a time where PO is needed.

---

Both Credit and Invoice T-Pro reports/Memos already have a PO Header. When ThisProject is done, it will be trivial to add that to the RawData Import function. Actually, that functionality will be inherent to the way ThisProject_Reference.xlsm is parsed by ThisProject_Master.xlsm

BTW, Have you imagineered a name for ThisProject?

_________________________________________________________

On a new note: While developing a master Store Replacement list I noticed that different Distributor/Clients replace the same store names with different Client specific Store names. When I started, I thought that a replacement name like 1Midstate:10155001 Mohawk Valley CC could be parsed as CorporationCode:Store. But after manipulating, filtering, and sorting the completed list I also saw 4Independent Stores:10155001 Mohawk Valley CC. Same Store, different Replacement. Is it possible for these to be standardized? If not let me know, it will add a bit of difficulty maintaining ThisProject_Reference.xlsm. Whichever way you decide, I can make it usable by ThisProject_Master.xlsm.

As a cross check, I show 852 different stores, including the Double Entries.


_________________________________________________________________

The way ThisProject will work is that everything references the name of the DataStore folder in the current Directory Tree C:\Data\DataStore\Archive. This Naming Pattern is what will keep the code and Reference information so simple.

When a DataStore is looked at, The Master will read a set of Data Containers from the Reference. As The RawData is processed all modifications to the data will be made in accordance with the Reference. Finally, the total data from all stores will be presented as an Invoice or a Return xls(x) for input to T-Pro

At this time, I know that I will be adding a folder (Test or Testing) to the Directory tree, I will need it for testing my code and you can use it for testing any edits or additions to ThisProject_Reference.xlsm.

I have not yet decided how to handle Errors and Omissions effecting the parsing of RawData files.

Quote:

---

I have a question about adjusting one of the macros I have in my conversion xlsm files. Should I start a different post or asking in this one is fine?

---

This one is fine, Any Procedure you are using is too specific to be of a whole lot of general interest,

HEIDELBERG-INVCR092920-9-26-out.csv (49.7 KB, 0 views)

Joeny, PLEASE prefix all attachments with the DataStore Name they belong to.

Example: Flex-HEIDELBERG-INVCR092920-9-26-out.csv
You have uploaded three Heidelberg CSVs and this one is the only one I can assign to a DataStore folder.

1. HEIDELBERG-INVCR091820
2. Heidelberg KKB 083020-090520
3. Flex HEIDELBERG-INVCR092920-9-26-out (This latest one that I renamed)

I apologize I was not clear on my reasoning when I said “The order of the columns on the import file does not matter since I map it once I get to that step.” I was just trying to explain you can put the columns in the order you would like and a map can be made to have it upload correctly. I was no aware if you might have though there was a static order the data columns needed to be in.
I am not sure how your tool works but I do think you would need a different map to go from the raw data to the conversion tool for each. Since they are all in a different order.

I am not sure on a good name yet. Do you have any suggestion? Or maybe once I see how it works that could help. I will work on that.

The first Midstate replace file I sent you was not correct. Once I found out I did adjust it and posted it again. Within our accounting program we have customers the under the customer there is what they call jobs. You can have many jobs under one customer. With all independent stores they are under a customer 4Independant Stores. For midstate we took all the independent stores and put them under the customer 1Midstate. So, where you see a duplicate store name the one with Independent if front needs to be removed. Where you see “4Independent Stores:10155001 Mohawk Valley CC” was changed to “1Midstate:10155001 Mohawk Valley CC” I must have missed that one when I sent the updated midstae replace file. If you remove “4Independent Stores:10155001 Mohawk Valley CC” that should remove any duplicate. The same if there are any other duplicate names just remove the one with 4Independant in front of the store name. Or if you would like me to help you can send me the list you have and I will make sure the duplicate names are removed and send it back to you.

Again, I do want to let you know I appreciate all the work and effort you are putting into this project. There are still good people within the world. The number has dropped significantly but they are still here

1. Flex HEIDELBERG-INVCR091820
2. KKB Heidelberg KKB 083020-090520
3. Flex HEIDELBERG-INVCR092920-9-26-out (This latest one that I renamed)

In my current xlsm file I have having an issue with the replace macro. A product from flexibake raw data has appeared for the first time with the item code of "80". The replace macro is replacing everywhere it finds an 80. If there is a 80 in the invoice number it is replacing the 80. If the invoice is 1248043 it is changing it to 124Itemcode43. So if I was able to adjust that macro to only search the item column for the item replace data that would not happen. As I stated in that post.

I am needing to use this tool while you create the new one. I was hoping this is an easy adjustment to the current code.
--------------------------------------------------------------------------------------------------------------
I only need it to search column D for "Call ReplaceAllSheets(Worksheets("Data").Range("A1"))". Then for both "Call ReplaceAllSheets(Worksheets("Data").Range("D1")) and Call ReplaceAllSheets(Worksheets("Data").Range("G1")) it only needs to look in column C. It is replacing items in the wrong column. How can I restrict it to only search column D for products and only search column C for customers?
---------------------------------------------------------------------------------------------------------------


I also attached the .bas file in the zipped folder






Option Explicit


Sub Replaces()
Dim wbData As Workbook

Application.ScreenUpdating = False

'delete Data is if still exists
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Data").Delete
Application.DisplayAlerts = True
On Error GoTo 0

'open Replaces workbook and copy data sheet in
Workbooks.Open Filename:="C:\FlexibakeConversions\FlexreplacedataInvoice.xlsx" ' <<<<<<<<<<<<<<<<<
Set wbData = ActiveWorkbook
wbData.Worksheets("Data").Copy Before:=ThisWorkbook.Worksheets(1)
wbData.Close False

ThisWorkbook.Activate

'do the replaces
Call ReplaceAllSheets(Worksheets("Data").Range("A1"))
Call ReplaceAllSheets(Worksheets("Data").Range("D1"))
Call ReplaceAllSheets(Worksheets("Data").Range("G1"))
'get rid of Data
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Data").Delete
Application.DisplayAlerts = True
On Error GoTo 0

Application.ScreenUpdating = True
End Sub


'this sub is Private so that it's only usable in this module
Private Sub ReplaceAllSheets(R As Range)
Dim i As Long
Dim ws As Worksheet
Dim r1 As Range

Set r1 = R.CurrentRegion

If r1.Rows.Count < 2 Then Exit Sub

For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "Data" Then GoTo GetNextSheet
If ws.UsedRange.Cells.Count < 2 Then GoTo GetNextSheet

For i = 2 To r1.Rows.Count
ws.UsedRange.Cells.Replace What:=r1.Cells(i, 1).Value, Replacement:=r1.Cells(i, 2).Value, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next i
GetNextSheet:
Next
End Sub

Paul


Is it possible to make the macro execute on the csv file I have open. I saved it to the personal workbook. So when I have the CSV open I want to convert I can just run you macro. When I do it now the first thing it does is ask what csv file I want to convert.
Can it just execute on the csv I have open when I run the macro rather than ask me to open the file?

Also if rather than saving the converted file it just makes the changes and I can save the file where and how I want


The macro does work great. If that is alot of changes dont worry it is good as is.


Thanks Much

The way I envision the ThisProject system in use is for three classes of persons, Common Users, Power Users, and VBA Coders.

1. A Common User would open ThisProject_Master and click a Button or Shape Captioned "Create T-Pro Input Memos"
2. A Power User, (That is you and anybody you trained,) Would maintain the data in ThisProject_Reference, which data would be laid out in a fairly self explanatory manner with hints and tips abound, for each individual Distributor (Client).
3. A VBA Coder would be able to easily comprehend the logic and program flow of the VBA in both Master and Reference.

_____________________________________________________________________

Quote:

---

you can put the columns in the order you would like and a map can be made to have it upload correctly. I was no aware if you might have though there was a static order the data columns needed to be in.
I am not sure how your tool works but I do think you would need a different map to go from the raw data to the conversion tool for each.

---

That is an issue for a PowerUser, the question is when and how. Remember the edits must be tested before the system is used for producing an actual T-Pro InputMemo.

There are only two instances when the Reference book needs editing, for changes in Replacements, (which see above,) is pretty straight forward, and when a new Distributor (Client) is added.

Adding a Client is done in the Reference book by:

1. copying the existing sheet "Template_Client"
2. Renaming the copy to suit,
3. changing the Copies' CodeName to "Test," (F4 again.)
4. Then filling out the information in Copies' tables exactly the same as in any other Distributor's Sheet.

Lastly,

1. edit the Options Sheet's "Uses Test" Cell to read True vice the standard False.
2. Finally, click the Button or Shape Captioned "Create T-Pro Input Memos"
3. and review the results in the folder C:\Data\Test.

When satisfied with the Results,

1. Add a new DataStore Folder, named to suit,
2. Change the CodeName of the Reference Copied sheet to suit
3. Add the new Client's Sheet CodeName to the Options Sheets "Distributors" List
4. and resetting "Uses Test" to False.

Note that the Name of the DataStore folder and the Codename of each Clients information sheet must be identical and have no spaces and cannot begin with a number. The sheet tab name is not used in any code.
_________________________________________________________________

The code in the Masterbook has three main sections

1. Read RawData files, following details in the Reference book sheets
2. Manipulate and edit the ReadIn data, again following details in the Reference book sheets
3. Collate the data and create T-Pro Input memos. Once again following details in the Reference book sheets

I would like to request some changes to the T-Pro InputMemo(s)

The purpose of of these suggestions is to make that part of the process more transparent to SuperUsers so They have that much less to learn and remember. I have tried to make these suggestions very reflective of what I Imagine the QB is using so that the Memo Headers are intuitive to SUs.

Another purpose is to make the T-Pro Map the same for all Distributors, since they will all be input using the same T-Pro Input Memo(s). This would mean that the only individual unique Maps are from Raw Data to Input Memos. I don't have a clue as to what is going on with T-Pro and QB, that knowledge is still strictly in your domain.

I don't know how T-Pro and QB handles empty or partially empty fields. If there is a code that tells QB to ignore this, that code can easily be inserted into empty fields.

I am aware that these recommendations will have very little negative effect on the code of ThisProject and will even be of some benefit to any future VBA Coders.

________________________________________________________

It is a prime tenet of Good Programming Practices that Names are consistent from beginning to end. The real beginning of ThisProject is the Raw Data from Distributors, which, unfortunately, you have no control over at this time. The real End of ThisProject is QB, which you have in hand. Unfortunately, T-Pro is in the muddle with it's very own Field names, which no one can control.

Fortunately for us, You are creating Maps between Distributors and Memos, and, Memos and T-Pro, and, T-Pro and QB


As an example of using consistent names, you have provided a list of Stores to ignore, Consistent names would have you providing a list of Customer+Jobs to ignore. Minor, I know, but it's the only real example I can come up with off the top of my head.

All Names in ThisProject should have their genesis in your QB Charts of Accounts. That is so important that you should have the relevant COAs on your desk for easy reference

_____________________________________________________

At this time, you are using three flags to tell QB a record is a Return Item: An Asterisk in a Product Code, a Memo type (Return Memo) and a Template Field in the Memo. The Asterisk is almost impossible to code for since its usage does not follow a pattern; It is not always the third character; It does not always precede a space; It does not always follow numerical characters. An Asterisk has dedicated meanings, purposes, and functionality in many, including VBA, Programs. It is so critical in VBA that one can not use it as a character, one MUST use a special character code instead.

That sounds good.


I looked at the memo headers and looks good. I did want to make sure you knew that columns 4 and 5 need to be combined into one column with : between the customer name and the job. The same need to be with the product code and product description. They need to be combined with : between the two.

Would it be helpful if all the return items are two digits followed by a *. We only accept returns for the items below. I would only need to change the top 3 if it would be helpful to have two digits * "23*,24*"

Q000212	2Pk*Cracked Wheat
Q000262	2Pk*French Peasan
Q000452	2Pk*Multigrain
Q00021	21* CW
Q00023	23* HR
Q00026	26* FP
Q00022	22* RYE
Q00024	24* PUMP
Q00025	25* WW
Q00033	33* Biaggio Asti Italian
Q00028	28* WB
Q00027	27* SD
Q00031	31* OAT
Q00034	34* JR
Q00045	45* Multi Grain
Q00068	68* Hamburg Buns
Q00069	69* Hot Dog Buns
Q00621	21* CW
Q00622	22* RYE
Q00626	26* FP
Q00645	45* Multi Grain
Q00030	30* RS
Q00029	29* DR
Q00050	50* Wheated 12 Grain
Q00040	40* Flaxseed
Q00070	70* Heidelberg Italian

Joeny, I need your explicit approval of Memo Headers v2 for approval.xlsx. Look it over carefully before you say yes. This is what I intend to code for.
SamT. 10.02.2020

@SamT

You have mine :yes:whistle:

:thumb