Hello,
I export the access report to excel via access VBA(outputo method) and then I am opening the file from access to add the data validation list in the excel file. Below is the code -Code runs fine and it shows no error but when I check the file, nothing happening. I want to have data validation drop down list in column P.
I export the access report to excel via access VBA(outputo method) and then I am opening the file from access to add the data validation list in the excel file. Below is the code -Code runs fine and it shows no error but when I check the file, nothing happening. I want to have data validation drop down list in column P.
Code:
Public Function FormatExportedExcelFileFormats(myFile As String, mySheet As String)
On Error GoTo Err_Code_Click
Dim xlApp As Object
Dim xlSheet As Object
Dim rList As String
Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(myFile).Sheets(1)
With xlApp
.Application.Sheets(mySheet).Select
'Add the new column name to the P column
.Application.Range("P1").Select
.Application.ActiveCell.Value = "Lienholder Comment'"
'Data Validation
.Application.Range("P2239").Select
.Application.ActiveCell.Value = "Perfect"
.Application.Range("P2240").Select
.Application.ActiveCell.Value = "Please enter Id"
.Application.Range("P2241").Select
.Application.ActiveCell.Value = "Inquiry"
.Application.Range("P2242").Select
.Application.ActiveCell.Value = "Send customer letter"
.Application.Range("P2243").Select
.Application.ActiveCell.Value = "Resolved"
.Application.Range("P2244").Select
.Application.ActiveCell.Value = "Wrong address"
.Application.Range("P2245").Select
.Application.ActiveCell.Value = "Update needed"
.Application.Range("P2246").Select
.Application.ActiveCell.Value = "Other"
rList = "$P$2239:$P$2246"
.Application.Columns("P:P").Select
.Application.Range("P2").Activate
With .Application.Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & rList
.IgnoreBlank = True
.InCellDropdown = True
End With
.Application.Range("A1").Select
.Application.ActiveWorkbook.Save
.Application.ActiveWorkbook.Close
.Quit
End With
Exit_Code_Click:
Exit Function
Set xlApp = Nothing
Set xlSheet = Nothing
Err_Code_Click:
MsgBox Err.Description
Resume Exit_Code_Click
End Function