• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 554
  • Last Modified:

Library Not Found error -2147319779

This code ran great until today.  I have installed some new software over the weekend so I have no doubt something has gone awry.  I just don't know what or how to remedy it.  I get Automation Error -2147319779 'Library Not Found' when running this code.  When I click 'Debug' my Editor highlights the 5th line:

Set objExcel = CreateObject("Excel.Application").  

I appreciate any guidance.
Function ExcelFormatStats()

   Dim objExcel As Excel.Application
   Dim objExcelbook As Excel.Workbook
   Dim objExcelSheet As Excel.Worksheet
   Dim objWindow As Excel.Window
   
   
   Set objExcel = CreateObject("Excel.Application")
   Set objExcelbook = objExcel.Workbooks.Open("P:\Sales_Dept\reports\CustTypeTouches.xls")
   Set objExcelSheet = objExcel.ActiveSheet
   Set objWindow = objExcel.ActiveWindow
      
   objExcel.Visible = False
   objExcel.Selection.AutoFilter
    objExcel.Columns("C:C").Select
    objExcel.Selection.delete Shift:=xlToLeft
    objExcel.Cells.EntireColumn.AutoFit
    objExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    objExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    objExcel.Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    objExcel.Selection.Borders(xlEdgeTop).LineStyle = xlNone
    objExcel.Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    objExcel.Selection.Borders(xlEdgeRight).LineStyle = xlNone
    objExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone
    objExcel.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
   objExcelSheet.Rows(1).Select
   objWindow.FreezePanes = False
   objExcelSheet.Rows(2).Select
   objExcel.ActiveWindow.FreezePanes = True
   objExcel.ActiveWorkbook.Save
   objExcel.ActiveWorkbook.Close
   
   
   Set objExcelbook = objExcel.Workbooks.Open("P:\Sales_Dept\reports\GMStats.xls")
   Set objExcelSheet = objExcel.ActiveSheet
   Set objWindow = objExcel.ActiveWindow
      
   objExcel.Visible = False
   objExcel.Selection.AutoFilter
    objExcel.Cells.Select
    objExcel.Cells.EntireColumn.AutoFit
    objExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    objExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    objExcel.Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    objExcel.Selection.Borders(xlEdgeTop).LineStyle = xlNone
    objExcel.Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    objExcel.Selection.Borders(xlEdgeRight).LineStyle = xlNone
    objExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone
    objExcel.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
   objExcelSheet.Rows(1).Select
   objWindow.FreezePanes = False
   objExcelSheet.Rows(2).Select
   objExcel.ActiveWindow.FreezePanes = True
   objExcel.Range("A1").Select
   objExcel.ActiveWorkbook.Save
   objExcel.ActiveWorkbook.Close
   
   Set objExcelbook = objExcel.Workbooks.Open("P:\Sales_Dept\reports\GMStats_MTD.xls")
   Set objExcelSheet = objExcel.ActiveSheet
   Set objWindow = objExcel.ActiveWindow
      
    objExcel.Visible = False
    objExcel.Cells.Select
    objExcel.Selection.Columns.AutoFit
    objExcelSheet.Rows(1).Select
    objWindow.FreezePanes = False
    

    objExcel.Cells.Select
    objExcel.Selection.AutoFilter
    objExcel.Range("A1").Select
    objExcel.ActiveWorkbook.Save
    objExcel.ActiveWorkbook.Close
    
   
   
   Set objExcelSheet = Nothing
   Set objExcelbook = Nothing
   Set objExcel = Nothing
   
   
End Function

Open in new window

0
bsncp
Asked:
bsncp
  • 5
  • 5
1 Solution
 
hello_everybodyCommented:

 Excel is referenced to the ms Excel dll library. When you use new version of ms office, did you change the reference to the higher version of ms Excel dll?
 If you did not (and you need to run it with old one), did you tried regsrv to register it?
0
 
bsncpAuthor Commented:
Sorry...I'm limited on my technical skills here.  I would need a little bit of instruction on how to do both of the things you suggested.  In my References, Microsoft Excel 11.0 is checked.  I don't see anything higher than 11.0.
0
 
hello_everybodyCommented:
Regarding the first point: do you work with Excel 2007? if that's the case Microsoft Excel 12.0 should be checked.
And regarding the second point see: http://www.experts-exchange.com/Programming/Q_21020348.html
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
bsncpAuthor Commented:
It's Excel 2003.
0
 
bsncpAuthor Commented:
Okay I read through the post you linked to...however, I don't quite understand what I need to do in order to regsrv it.  Is there a certain dll I'm trying to register to fix my issue?
0
 
hello_everybodyCommented:
Here is one work around : on line 5 and any similar ones change code to:

CreateObject("Excel.Application.11")

more ideas are coming.........
0
 
bsncpAuthor Commented:
Thank you...we are clearly onto something here.  I did the late binding and it ran with no issues.  However, I have another one that is similar.  I made the late binding adjustment and it runs and gets stuck on the "With Selection" line.  I get the same Automation error...library not found.

On a side note, what is the crux of my issue?  I can't quite wrap my head around what might have changed to cause the issue in the first place.  Thanks for all your help!
Function FormatNotes()

'Formats Excel file, adding filter and re-sizing columns
   
   
   Dim objExcel As Object
   Dim objExcelbook As Object
   Dim objExcelSheet As Object
   Dim objWindow As Object
   
   
   Set objExcel = CreateObject("Excel.Application")
   Set objExcelbook = objExcel.Workbooks.Open("P:\Sales_Dept\reports\NotesHist.xls")
   Set objExcelSheet = objExcel.ActiveSheet
   Set objWindow = objExcel.ActiveWindow
   
   

    objExcel.Visible = False
    objExcel.Cells.Select
    objExcelSheet.Columns.AutoFit
    objExcelSheet.Columns("F:F").Select
    objExcelSheet.Columns("F:F").ColumnWidth = 40
    objExcelSheet.Columns("G:G").Select
    objExcelSheet.Columns("G:G").ColumnWidth = 50
    objWindow.ScrollColumn = 6
    objWindow.ScrollColumn = 5
    objWindow.ScrollColumn = 4
    objWindow.ScrollColumn = 3
    objWindow.ScrollColumn = 2
    objWindow.ScrollColumn = 1
    objExcel.Cells.Select
    With Selection
        objExcelSheet.Cells.HorizontalAlignment = xlLeft
        objExcelSheet.Cells.VerticalAlignment = xlTop
        objExcelSheet.Cells.WrapText = True
        objExcelSheet.Cells.Orientation = 0
        objExcelSheet.Cells.AddIndent = False
        objExcelSheet.Cells.IndentLevel = 0
        objExcelSheet.Cells.ShrinkToFit = False
        objExcelSheet.Cells.ReadingOrder = xlContext
        objExcelSheet.Cells.MergeCells = False
    End With
    
    objExcel.Cells.Select
    objExcelSheet.Rows.AutoFit
    
    objExcel.Cells.Select
    objExcel.Selection.AutoFilter
    
    objExcel.ActiveWorkbook.Save
    objExcel.ActiveWorkbook.Close
   
        
   Set objExcelSheet = Nothing
   Set objExcelbook = Nothing
   Set objExcel = Nothing
   

    
End Function

Open in new window

0
 
hello_everybodyCommented:
It seems that you have some registry keys from Excel 2007 either because you installed it at one point or because you have the office 2007 compatibility pack installed, or for some other weird reason (its happened to many other people).  
0
 
bsncpAuthor Commented:
Sorry for the delay.  I had to fight some other fires yesterday.  It looks like our IT guys did in fact  install the converter pack for Microsoft Office 2007.  Most of our machines are still running 2003 but people get 2007 attachments and files sent to them all the time.  So I guess they wanted everyone to have that tool.

So the late binding idea worked.  I had to use a variable for the 'With Selection' issue.  Working code attached...thank you!
Function FormatNotes()

'Formats Excel file, adding filter and re-sizing columns
   
   
   Dim objExcel As Object
   Dim objExcelbook As Object
   Dim objExcelSheet As Object
   Dim objWindow As Object
   Dim objSel As Object
   
   
   Set objExcel = CreateObject("Excel.Application")
   Set objExcelbook = objExcel.Workbooks.Open("P:\Sales_Dept\reports\NotesHist.xls")
   Set objExcelSheet = objExcel.ActiveSheet
   Set objWindow = objExcel.ActiveWindow
   Set objSel = objExcel.ActiveCell
   
   

    objExcel.Visible = False
    objExcel.Cells.Select
    objExcelSheet.Columns.AutoFit
    objExcelSheet.Columns("F:F").Select
    objExcelSheet.Columns("F:F").ColumnWidth = 40
    objExcelSheet.Columns("G:G").Select
    objExcelSheet.Columns("G:G").ColumnWidth = 50
    objWindow.ScrollColumn = 6
    objWindow.ScrollColumn = 5
    objWindow.ScrollColumn = 4
    objWindow.ScrollColumn = 3
    objWindow.ScrollColumn = 2
    objWindow.ScrollColumn = 1
    objExcel.Cells.Select
    With objSel
        objExcelSheet.Cells.HorizontalAlignment = xlLeft
        objExcelSheet.Cells.VerticalAlignment = xlTop
        objExcelSheet.Cells.WrapText = True
        objExcelSheet.Cells.Orientation = 0
        objExcelSheet.Cells.AddIndent = False
        objExcelSheet.Cells.IndentLevel = 0
        objExcelSheet.Cells.ShrinkToFit = False
        objExcelSheet.Cells.ReadingOrder = xlContext
        objExcelSheet.Cells.MergeCells = False
    End With
    
    objExcel.Cells.Select
    objExcelSheet.Rows.AutoFit
    
    objExcel.Cells.Select
    objExcel.Selection.AutoFilter
    
    objExcel.ActiveWorkbook.Save
    objExcel.ActiveWorkbook.Close
        
   
   Set objSel = Nothing
   Set objWindow = Nothing
   Set objExcelSheet = Nothing
   Set objExcelbook = Nothing
   Set objExcel = Nothing
   

    
End Function

Open in new window

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now