what's wrong with this code? (1 Viewer)

Jovial

Registered User.
Local time
Today, 03:49
Joined
Jan 21, 2015
Messages
29
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.
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
 

JHB

Have been here a while
Local time
Today, 11:49
Joined
Jun 17, 2012
Messages
7,732
Have you tried to step through code line by line, to see what happen?
And also making Excel visible could help you to find out what is going on!
 

Jovial

Registered User.
Local time
Today, 03:49
Joined
Jan 21, 2015
Messages
29
Thank you very much for your reply. Yes, i debug each line one by one and there is no issue but i don't know how to make excel visible. This function is called by the access after dumping the report to the excel.
I spent quite a time on this but no luck.
 

JHB

Have been here a while
Local time
Today, 11:49
Joined
Jun 17, 2012
Messages
7,732
Add the line below.
Code:
xlSheet.Application.Visible = True
 

Jovial

Registered User.
Local time
Today, 03:49
Joined
Jan 21, 2015
Messages
29
ok I make excel file visible and the code creates the validation list but as the code save the file and quit the file, there is no validation at all.
I am not sure what's going on. whats wrong am I doing in saving it?
Code:
.Application.ActiveWorkbook.Save
      .Application.ActiveWorkbook.Close
      .Quit
   End With

Exit_Code_Click:
    Exit Function
 

Solo712

Registered User.
Local time
Today, 06:49
Joined
Oct 19, 2012
Messages
828
ok I make excel file visible and the code creates the validation list but as the code save the file and quit the file, there is no validation at all.
I am not sure what's going on. whats wrong am I doing in saving it?
Code:
.Application.ActiveWorkbook.Save
      .Application.ActiveWorkbook.Close
      .Quit
   End With
 
Exit_Code_Click:
    Exit Function

Try
Code:
.Application.ActiveWorkbook.SaveAs Myfile

Best,
Jiri
 

JHB

Have been here a while
Local time
Today, 11:49
Joined
Jun 17, 2012
Messages
7,732
Else post a stripped down version of your database with some sample data, (zip it). And a description how to run/trigger your code.
 

Jovial

Registered User.
Local time
Today, 03:49
Joined
Jan 21, 2015
Messages
29
I save the file as
Code:
 .Application.ActiveWorkbook.SaveAs "C:\" & Format(Now(), "mm-dd-yyyy") & "RptTest6.xlsx"
      .Application.ActiveWorkbook.Close
      .Quit

Now I am getting below error while opening the file:
"Excel cannot open the file "RptTest6.xlsx" because the file format or file extension is not valid. Verify that the file has been corrupted and that the file extension matches the format of the file"
 

Jovial

Registered User.
Local time
Today, 03:49
Joined
Jan 21, 2015
Messages
29
Solved it!!!
Code:
  .Application.ActiveWorkbook.SaveAs "C:\" & Format(Now(), "mm-dd-yyyy") & "RptTest6.xlsx", FileFormat:=51
      .Application.ActiveWorkbook.Close
      .Quit

thank you all for your suggestions
 

Users who are viewing this thread

Top Bottom