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
        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"
            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
End Function
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

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


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.