Moving columns from one sheet to another containing formulas, without affecting formulas.

[vj28]

I have two workbooks(say wb1, wb2). Sheet1 in wb1 has n columns and Sheet2 in wb2 has n+2 columns (these 2 extra columns are in the end and have formulas.).

I want to replace the first n columns in sheet2 with the ones in sheet1, but I can't do this without messing up the formulas. 

What I've tried so far 

1) join tool: used two separate input tools to get all columns in sheet1 and the last 2 columns in sheet2, use Record ID and join tool in conjunction to add results, but the formulas were gone.

2) vbs script to directly create those two columns with the formulas, but I'm very new to scripting so my script didn't work. But if no other solution is gonna work, I'll keep trying this. 

Is there any way to do this? (excluding python related solutions please)

Any solutions are welcome and appreciated. This seems like a fairly common use case so I'm surprised alteryx isn't very convenient when dealing with excel sheets that have formulas. 

[OTrieger · Accepted solution]

@vj28 
There are several ways how to do it.
The main thing is to set the output range where the data will be written to, and on the Data Output tool to select Preserve Original Formatting.
You can use Blob tool to call for a a ready template and then write the data in.

[vj28]

@OTrieger Thanks for the response

Could you please elaborate when you said set output range where data will be written to, do you mean in the output tool where there's an "overwrite sheet or range" option? I'm familiar with overwriting entire sheets but the range part I'm not sure how to use.

Could you also elaborate on the blob tool part? I'm entirely unfamiliar with it, especially considering the documentation for the blob tool doesn't have any examples for excel and excel files containing formulas. Thanks.

[ntakeda]

Hi @vj28 

The most common approach is to define the output range of cells.
In the example, it is set to A1:C1000. Please refer to below image and attached workflow.

If this doesn't resolve the issue, using an Excel macro would be a good option.

Code is below

Sub ReplaceColumnsWithSelection()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim n As Long
    Dim wb1Path As String, wb2Path As String
    
    ' Open file dialog to select wb1
    wb1Path = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx", , "Select wb1")
    If wb1Path = "False" Then Exit Sub
    Set wb1 = Workbooks.Open(wb1Path)
    
    ' Open file dialog to select wb2
    wb2Path = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx", , "Select wb2")
    If wb2Path = "False" Then Exit Sub
    Set wb2 = Workbooks.Open(wb2Path)
    
    ' Set the first sheet in each workbook
    Set ws1 = wb1.Sheets(1)
    Set ws2 = wb2.Sheets(1)
    
    ' Get the last column in wb1 (n columns to replace)
    lastCol = ws1.Cells(1, ws1.Columns.Count).End(xlToLeft).Column 'Column count is checked in 1st row
    n = lastCol ' Set n as the last column count in Sheet1
    
    ' Get the last row in wb2 (assuming row count should match wb1)
    lastRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row 'Row count is checked in Last row of A column
    
    ' Copy the first n columns from wb1.Sheet1 to wb2.Sheet2 (values only)
    ws1.Range(ws1.Cells(1, 1), ws1.Cells(lastRow, n)).Copy
    ws2.Range(ws2.Cells(1, 1), ws2.Cells(lastRow, n)).PasteSpecial Paste:=xlPasteValues
    
    Debug.Print lastRow, n, lastCol
    

    ' Clear clipboard
    Application.CutCopyMode = False

    ' Activate wb2 and bring it to the front
    wb2.Activate
    ws2.Activate

    ' Display completion message
    MsgBox "XXXXXXX", vbInformation
End Sub

You can make any necessary adjustments using ChatGPT or other tools.
Run the macro in this file and select the files in the order of wb1 → wb2.

Finally, I also send an example workflow for the case of Blob.
Blobs are often used to prevent template corruption.

The workflow saves the Blob input file to a specific path.
Next, output the results of the workflow to that file.

Let me know if you have any questions.

Specifying Range (in the first case) is the simplest and most appropriate, in my opinion.

[OTrieger]

@vj28 
When writing out data we can define the sheet name, it will be the text after the 3 pipes (|||). Now we can write data into specific cells or ranges, that can be added just after the sheet name. What you will need to do is to add $ sign and then a normal range as  you will define in excel A2:C4
i.e. \file.xlsx|||Sheet1$A2:C4
In this case Alteryx will save the data in Sheet1 in the range A2:C4

[OTrieger]

Normally we do not know what will be the exact range as the amount of data might change from period to period, so what you can do, set the end range as a variable and by counting the number of rows will define the end of your range.
So you can set it dynamically, do not forget to add the value of the starting row. So if your range starts at row 2 you will need to add +1 to the range as if you will have 100 rows and your range start from D2 the last row will be D101.

[vj28]

@OTrieger  @ntakeda thanks for the responses.

I tried the specifying range in output configs approach like you both suggested, but I get the following error :

"We found a problem with some content when opening excel file, would you like to recover as much as possible?"

And when I clicked on recover, the issue still persisted. 

@ntakeda the overwrite_sample.yxmd you posted doesn't work for me because I have an older version of alteryx. 

Error: GenericTool (5): The entry point "AlteryxCtrlContainer" could not be found in the plugin "AlteryxBasePluginsEngine.dll".

[ntakeda]

@vj28 

>"We found a problem with some content when opening excel file, would you like to recover as much as possible?"

Are you overwriting a formula?
For example, if cell C5 in the output template (wb2) contains a formula and you specify a range like Sheet1$A2:C1000 in Alteryx, C5 will be overwritten, causing the above error.

If that's not the case, I'm not sure.
I think this is a difficult error, so using VBA might be a better option.

>Error: GenericTool (5): The entry point "AlteryxCtrlContainer" could not be found in the plugin "AlteryxBasePluginsEngine.dll".

If the Control Container cannot be used, you can use the "Block Until Done" tool.
This tool ensures that processing of output 1 is completed before proceeding with output 2.

[vj28]

@ntakeda @OTrieger  The solutions you initially provided, i.e simply setting the range in the output tool somehow worked. I'm not sure what I did wrong previously that got me the errors. 

I still need to look into blob tools and excel templates as a means to preserve formulas. The workflows you provided were super helpful, thanks!