ACCESS FEED: [MS_AccessPros] Re: Export Data to Excel

Jumat, 01 Mei 2015

[MS_AccessPros] Re: Export Data to Excel

Phucon

Take a look at this thread:

https://social.msdn.microsoft.com/Forums/en-US/d35f6852-8770-4503-8428-4a7040b654cd/suddenly-automation-error-library-not-registered?forum=isvvba

Regards,

Bill Mosca, Founder - MS_Access_Professionals

http://www.thatlldoit.com

Microsoft Office Access MVP

http://mvp.microsoft.com/en-us/mvp/Bill%20Mosca-35852

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

---In MS_Access_Professionals@yahoogroups.com, <saigonf7q5@yahoo.com> wrote :

I have a function (I copied it from a book) which I have been using for exporting data to Excel. It's been working perfectly fine until recently the error "-2147319779 Automation error Library not registered." popped up each time when the Export Button was clicked. It happens on every Access application that uses this function.

The IT people had done something to the Microsoft Office Suite, I was wondering if that's the cause? Is this an easy fix and How?

Phucon

Function SendRecordset()
On Error GoTo ErrorHandler

Set xl = New Excel.Application
Set xlwkbk = xl.Workbooks.Add
Set db = CurrentDb()
Set f = Forms!frmRunQuery

xl.Visible = True

xl.Application.DisplayAlerts = False 'suppress Excel's alerts

Select Case f!lstAsset
    Case "E or G than 1B"
        Call EorG1B
        xl.ActiveWorkbook.SaveAs FileName:= _
        CstrFilePathEorG1B, FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    Case "Less than 1B"
        Call Less1B
         xl.ActiveWorkbook.SaveAs FileName:= _
         CstrFilePathLess1B, FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", _
         ReadOnlyRecommended:=False, CreateBackup:=False
    Case ">=1Bil and <1Bil"
        Call Both
         xl.ActiveWorkbook.SaveAs FileName:= _
         CstrFilePathBoth, FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", _
         ReadOnlyRecommended:=False, CreateBackup:=False
    Case Else
        MsgBox Err.Number & vbCrLf & Err.Description
End Select

xl.Application.DisplayAlerts = True 'turn the warning back on

With xl
    .Sheets("Sheet1").Delete
    .Sheets("Sheet2").Delete
    .Sheets("Sheet3").Delete
End With

ExitFunction:
'clean up
    Set rs = Nothing
    Set xl = Nothing
    Set xlwkbk = Nothing
    Set xlsheet = Nothing

Exit Function

ErrorHandler:
    MsgBox Err.Number & vbCrLf & Err.Description
    Resume ExitFunction
    Resume
End Function

__._,_.___

Posted by: wrmosca@comcast.net

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•