Updating file X with data from file Y

[Shinano]

On a monthly basis I receive a delimited txt file, file Y, that I import into an Excel workbook in order to be able to manipulate the data. This is file X.

The file contains a fixed number of columns.

The number of rows in the monthly txt files, however, increases as the number of business cases accumulate over the year.

Based on invoicing, exchange rates, payment of outstanding invoices, etc. data for cases already existing in file X, may have changed in file Y, just as there are new cases in file Y that needs to be added to file X.

My question is basically: How do I get around this merge/import/search and replace task, and add new cases to workbook X from file workbook y, while at the same time updating existing information in workbook X with information from workbook Y.

Any help is highly appreciated.

Thanks.

 

[brian.wethington]

Without more information about where certain things are located in your data, for example, what column would a unique identifier that would be in both files be in, what range needs to be included... the best I can do is ask you one question. Do you know much about VBA? You could completely automate the process. If you don't know much about VBA you can learn alot about it quickly using the macro recorder and then making adjustments to the code (this may take longer than you are hoping for a macro that will do what you are wanting as you will need to use loops and such. Or you could post more information and we might be able to help you out. I must say this I would import the data into the file then have the update occur in that file on another sheet.

This may help you get started.

Sub Untested()
    Dim LstRw,LstRw2,x as Long
    LstRw = Cells.Find(What:="*", After:=cells(1,1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For x=1 to LstRw
        Range(Cells(x, 1), Cells(x, 4)).Copy 'This copies row x column A to D
    Next x
End Sub

There is alot more to this code as you notice I have not pasted into the new sheet. But this gives you an idea of how to start I hope. I don't have excel on my home computer and so I don't want to give you too much code that I am not completely sure of.

HTH

Edit added my end code tag

 

[Shinano]

Hi there,

Sorry for the silence.

I am new to VBA. I only began discovering it recently, and while fascinated by its potentials, I am somewhat overwhelmed by the possibilities at the same time.

I will have a look at the code.

Thank you very much. I appreciate it.

 

[Shinano]

Hi again Brian,

Well, I guess this is still beyond me...

The file A contains the following columns.

SalesOfficeNo, SalesOfficeName, ClientNo, ClientName, OrderNo, OrderDetails, OrderMonth, ColumnA, ColumnB, ColumnC, ColumnD, ColumnE, ColumnF.

The file B has the exact same columns, however, every month the values in the columns A - F may change depending on the accounting process, invoicing, etc. In case these values have changed in file B, file A needs to be updated with these new values.

At the same time file B contains new lines with new order numbers, etc. that also needs to be added to file A.

Any advice is much appreciate.

Thanks again.

 

[brian.wethington]

What column would contain the unique identifiers for the data?

 

[Shinano]

OrderNo is the unique identifier.

Thanks.