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.
@271828
if you mean filter, based on my test, it can be preserved.
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.
Are there any updates on this? I'm running into the same issue with the data validation being removed. Thanks!
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
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
Do you know if this was ever fixed?
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!