Link to home
Create AccountLog in
Avatar of Mik Mak
Mik Mak

asked on

Error writing to Excel from Access 2010

I'm trying to write a function in an Access 2010 database that exports a value to a named range in an Excel file. When using the code shown below I get an error "Unable to get the Open property of the Workbooks class" - the code has worked in a previous Office version, but I simply can't figure out whats going wrong :)

Please advice

Function UpdateExcelRpt(strRptFile As String, strRange As String, strVal As String)
    Dim xlApp As Object
    Dim xlWB As Object
    Dim xlBook As Variant
    Dim oSheet As Object
   
    Set xlApp = GetObject("", "Excel.Application")
    If Err.Number <> 0 Then
        Err.Clear
        Set xlApp = CreateObject("Excel.Application")
        If Err.Number <> 0 Then
            MsgBox "There was an unknown error while attempting to open Excel.  The export did not complete"
            xlApp.Quit
            Set xlApp = Nothing
        End If
    End If
   
    Set xlBook = xlApp.Workbooks.Open(strRptFile)
    xlApp.Visible = False
    Set oSheet = xlBook.Worksheets("TEST")
    oSheet.Range(strRange).Value = strVal
   
    xlBook.Save
    xlApp.Quit
End Function
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Mik Mak
Mik Mak

ASKER

It does point to a valid filename and path  - but you pointed my in the right direction - the excel fil contained a damaged pivot tabel, that crashed the file. So the code was alright afterall.