Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Outputting to a specific Excel range removes data validation

271828
8 - Asteroid

I have a workflow that outputs data to a specific range in a certain sheet in ~40 workbooks. That works great, however it removes the data validation in that sheet for some reason. Before outputting, there was a dropdown in the "Service Exists" column but that is now gone.

 

Is this a known issue and if so, is there a workaround?  I'll be correcting this particular instance with VBA but it'd be nice to know for the future.

 

colorado970_1-1603758283746.png

 

colorado970_2-1603758304244.png

 

 

9 REPLIES 9
Qiu
20 - Arcturus
20 - Arcturus

@271828 
if you mean filter, based on my test, it can be preserved.1027-colorado970.png

echuong1
Alteryx Alumni (Retired)

The filters should be preserved with output. Can you please upload a copy of your workflow and dataset? Or a sample where you're able to replicate?

 

I can do some testing on my end to see why it's not saving.

271828
8 - Asteroid

I mean data validation which restricts the values that can be entered, not header filters.

 

Before:

colorado970_0-1603808601876.png

 

After:

colorado970_1-1603808881097.png

 

Just like I posted last night, it removes the validation. I attached an excel sheet and a workflow that should demonstrate it.

BT10209
5 - Atom

Are there any updates on this? I'm running into the same issue with the data validation being removed. Thanks!

271828
8 - Asteroid

@echuong1 

 

Did you get a chance to look into this?

Christoph_Ebersbach
8 - Asteroid

Same issues here!

 

Would be great if the features around excel range output could be improved.

It is sad that the data validation is lost.

 

Also sad that you cannot overwrite a formula/link with a value from the excel range output without destroying the file!

I put a lot of hope into this topic after some first testing but this does not seem to be as strong as I have thought in the actual version...

 

Best regards Christoph

271828
8 - Asteroid

This is still causing issues for me, so here's the VBA I'm using to remedy it. I only have 32 workbooks so I'm using the rather simple method of just listing the workbook names. If you have more, it'd be better to go with a For Each loop or a directory method. The bolded section is how you apply the data validation.

 

Option Explicit
Sub data_validation()

 

'create loop counting variables
Dim x As Integer
Dim y As Integer
Dim n As Integer

 

'create worksheet variables
Dim wb As Workbook
Dim wb1 As Workbook
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim ref_ws As Worksheet

 

'create save path string variables
Dim opath As String
Dim opath2 As String

 

Set ref_ws = Workbooks("Refbook.xlsm").Worksheets("Sheet1")

opath = "\open\file\path\here\test.xlsx"

 

For n = 2 To 33

opath2 = opath & ref_ws.Cells(n, 2).Value

Set wb = Workbooks.Open(opath2)

Set ws = wb.Worksheets("Sheet1")


Debug.Print spath2


With wb
.Unprotect Password:=""
End With

With ws
.Range("A4:A2500").Validation.Add Type:=xlValidateList, Formula1:="<Select>, Y, N"
.EnableSelection = xlNoRestriction
.Protect Contents:=True, UserInterfaceOnly:=True
End With

 

With wb
.Protect Password:=""
.Save
.Clos
End With

Next n

End Sub

carlson1858
5 - Atom

Do you know if this was ever fixed?

dasbintee
5 - Atom

 

I was facing the same issue. I played around to realise  that creating data validation with lists using a named range in Excel instead of cell references helps in retaining the drop downs - hope this helps!

Labels