Matching Fields for import/export procedure.

[Trenard]

Hello,

My organization moves data between 2 different applications by exporting and importing csv files. I'll call the programs app1 and app2. app2 has an import file template that we use to match the fields from app1's export file. So I've been manually copying the columns from the export file from app1 into the appropriate columns of app2's template to "sync" the data between both applications.

I told a friend about how much of a pain this is becoming and he suggested I look online for a solution. Well here I am, and I'm wondering if it's possible to create a macro that would, at the click of a button, automatically move the columns from app1's export to app2's template file? There are other things that have to be done to the data before app2 will accept the template, but for now I'd just like to know if it's possible to create a macro that would move the columns from app1's export file into the appropriate columns in app2's import file template?

Please let me know if you need anymore information, or if there are more practical solutions for this problem.

 

[b.downey]

Please provide a sample of the Columns from App1 and how You would like to see the columns re-arranged for App2. Depending on what these two samples look like, VBA should be able to hande the task as a Macro that would be easily accessable from the Excel Menu

 

[Trenard]

Here is a sample of the columns from app1's export.

So is it possible to copy the columns from above into the corresponding columns in the following image? For example, the "Last Name" column from app1 maps to "lname" column of app2's template and so forth.

 

[b.downey]

Thanks for the examples: The following code needs to be pasted into a VBA Module.

The Code expects the Source (Export) Data to be in a worksheet (Tab) called "Sheet1"). It also expects a second Spreadsheet to be present and named "Import File" (per your example)

Option Explicit
Function Process()
    Dim ColNo As Integer
    Dim I As Integer
    Dim arrSrcCol As Variant
    Dim arrDstCol As Variant
    
    Dim wb As Workbook
    Dim WsDst As Worksheet
    Dim WsSrc As Worksheet
    
    arrSrcCol = Array(1, 2, 3, 4, 5, 6)
    arrDstCol = Array(1, 2, 3, 4, 6, 8)
    
    Set wb = ThisWorkbook
    Set WsSrc = wb.Worksheets("Sheet1")
    Set WsDst = wb.Worksheets("Import File")
    WsDst.Cells.Clear
    
    For ColNo = 0 To UBound(arrSrcCol)
        WsSrc.Columns(arrSrcCol(ColNo)).Copy Destination:=WsDst.Cells(1, arrDstCol(ColNo))
    Next ColNo
    
    MsgBox "Complete", vbInformation
End Function

Edit the arrSrcCol and arrDstCol as appropiate to define the Source and Destination Columns