I've read loads of similar questions and answers, but keep getting error.
What I do is I export query via
DoCmd.TransferSpreadsheet acExport, 8, "blabla", outputFile, True
Then I'm attempting to add validation to a range in my new excel file.
Here I get an error on setting AlertStyle:=xlValidAlertStop, Operator:=xlBetween,
(no errors without them, but no validation is set as well, obviously)
[Application-defined or object-defined error ]
Running same code from Excel - cause no issues!
Sub ApplyExcelFormating(ByVal myFile As String, ByVal mySpreadsheet As String)
Dim myValidationList(1) As String
myValidationList(0) = "YES"
myValidationList(1) = "OK"
Dim XL As Object, WB As Object, WKS As Object
Set XL = CreateObject("Excel.Application")
XL.Visible = True
XL.DisplayAlerts = True
Set WB = XL.Workbooks.Open(myFile)
WB.Activate
Set WKS = WB.worksheets(mySpreadsheet)
Dim lastrow As Integer
lastrow = WKS.UsedRange.Rows.Count
WKS.Range("K2:K" & lastrow).Activate
WKS.Range("K2:K" & lastrow).Select
Only these two cause error:
'AlertStyle:=xlValidAlertStop, Operator:=xlBetween,
With WKS.Range("K2:K" & lastrow).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(myValidationList, ",")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
WB.Save
XL.Workbooks.Close
XL.Visible = True
End Sub