Access: save Excel file to PDF... suddenly not working

Public Sub f005_CopyFromRecordSet(strSql As String, _
                                  strCPath As String, _
                                  strExcelFile As String, _
                                  strBackRevisedExcelFile As String, _
                                  strBackPDFFile As String)
 
 
Dim strExcelPath          As String
Dim strPSfile             As String
Dim strLOGfile            As String
 
 
Dim lngPos                As Long
 
Dim db                    As DAO.Database
Dim rst                   As DAO.Recordset
Dim fld                   As DAO.Field
 
 
strExcelPath = strCPath & strExcelFile
strBackRevisedExcelFile = strCPath & Mid(strExcelFile, 6)
 
Call e090_LaunchExcel
' Set objExcel = CreateObject("Excel.Application")
  
Set objExcelActiveWkbs = objExcel.Workbooks
 
Set objExcelActiveWkb = objExcelActiveWkbs.Open(Filename:=strExcelPath)
Set objExcelActiveWS = objExcel.ActiveSheet
 
objExcel.Visible = True
 
Set db = CurrentDb
Set rst = db.OpenRecordset(strSql)
 
Debug.Print strSql
 
objExcelActiveWS.Range("A2").CopyFromRecordset rst
 
 
With objExcelActiveWS
     With .Cells
          .Select
          .EntireColumn.AutoFit
          
          
          
         With Selection.Borders(xlDiagonalDown)
              .LineStyle = xlNone
         End With
         
         With Selection.Borders(xlDiagonalUp)
              .LineStyle = xlNone
         End With
          
         With Selection.Borders(xlEdgeLeft)
              .LineStyle = xlContinuous
              .Weight = xlThin
              .ColorIndex = xlAutomatic
         End With
         
         With Selection.Borders(xlEdgeTop)
             .LineStyle = xlContinuous
             .Weight = xlThin
             .ColorIndex = xlAutomatic
         End With
    
         With Selection.Borders(xlEdgeBottom)
             .LineStyle = xlContinuous
             .Weight = xlThin
             .ColorIndex = xlAutomatic
         End With
    
         With Selection.Borders(xlEdgeRight)
             .LineStyle = xlContinuous
             .Weight = xlThin
             .ColorIndex = xlAutomatic
         End With
    
         With Selection.Borders(xlInsideVertical)
             .LineStyle = xlContinuous
             .Weight = xlThin
             .ColorIndex = xlAutomatic
         End With
    
         With Selection.Borders(xlInsideHorizontal)
             .LineStyle = xlContinuous
             .Weight = xlThin
             .ColorIndex = xlAutomatic
         End With
          
     End With
     
     With .Columns("E:E")
          .Select
          Selection.NumberFormat = "mm/dd/yy;@"
     End With
 
    With .Columns("F:F")
         .Select
         Selection.NumberFormat = "mm/dd/yy;@"
    End With
    
    
    
End With
 
 
objExcel.DisplayAlerts = False
 
 
' save Excel file
objExcelActiveWkb.SaveAs Filename:= _
     strBackRevisedExcelFile, _
     FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
     ReadOnlyRecommended:=False, CreateBackup:=False
 
' Create PS, LOG, PDF file names from Excel file name
lngPos = InStr(strBackRevisedExcelFile, ".")
strPSfile = Left(strBackRevisedExcelFile, lngPos) & "ps"
strLOGfile = Left(strBackRevisedExcelFile, lngPos) & "log"
strBackPDFFile = Left(strBackRevisedExcelFile, lngPos) & "pdf"
 
Debug.Print strPSfile
Debug.Print strLOGfile
Debug.Print strBackPDFFile
 
' objExcel.ActivePrinter = "Adobe PDF"
 
' objExcel.ActivePrinter = "Adobe PDF on Ne05:"
' objExcel.ActivePrinter = "CutePDF Writer on CPW2:"
  
'save default printer
Dim strDefaultPrinter As String
strDefaultPrinter = objExcel.ActivePrinter
 
' print Excel file to Acrobat PS file
Dim objPDF_Distiller As PdfDistiller
Set objPDF_Distiller = New PdfDistiller
  
objExcel.ActiveSheet.PrintOut , Copies:=1, _
PrintToFile:=True, PrToFilename:=strPSfile
 
' Convert Acrobat PS file to PDF file
objPDF_Distiller.FileToPDF strPSfile, strBackPDFFile, ""
 
' delete work files
Kill strPSfile
Kill strLOGfile
 
Set objPDF_Distiller = Nothing
                                  
'Restore the default settings
objExcel.ActivePrinter = strDefaultPrinter
 
objExcel.DisplayAlerts = True
 
Call e110_CloseExcel(True)
 
Set objExcel = Nothing
Set objExcelActiveWkb = Nothing
Set objExcelActiveWkbs = Nothing
Set objExcelActiveWS = Nothing
 
rst.Close
db.Close
 
Set rst = Nothing
Set db = Nothing
Set fld = Nothing
 
End Sub

Select allOpen in new window