Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Add-in with access import (2003/2007)

    I export a query from access to excel. The min/max graph axises are dynamically adjusted with Tushar Mehta's autochart. If I attempt the export through code then the add-in does not function until it is reloaded. On the other hand if excel is open, not the workbook, it works fine. In a similar manner the transfer spreadsheet macro works fine if the particular workbook is open. but not if its closed. I tried opening opening the workbook first with the 'runapp' action I receive this error message "external table is not in the expected format." Any ideas?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add-in with access import (2003/2007)

    Please provide more detailed information - are you using Automation to control Excel from Access? If so, how do you start/activate Excel?

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add-in with access import (2003/2007)

    It was code that you were an huge help
    Option Compare Database
    Option Explicit

    Const strWbk = "C:My DocumentsTemplates & FormsExcelWorkbookBMI.xls"
    Const strWsh = "BMIWT"
    Const strCel = "A1"

    The sub is as follows;

    Private Sub cmdExport_Click()
    Dim xlApp As Object
    Dim xlWbk As Object
    Dim xlWsh As Object
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim lngRec As Long
    Dim i As Integer

    On Error GoTo ErrHandler

    If IsNull(Me.PatientID) Then
    MsgBox "No patient selected!", vbCritical
    Exit Sub
    End If



    strSQL = "SELECT * FROM qryWTBMI WHERE patientID=" & Me.PatientID

    ' Open database and recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

    On Error Resume Next

    ' Get/start Excel
    Set xlApp = GetObject(, "Excel.Application")
    If xlApp Is Nothing Then
    Set xlApp = CreateObject("Excel.Application")
    If xlApp Is Nothing Then
    MsgBox "Can't start Excel.", vbCritical
    Exit Sub
    End If
    End If

    On Error GoTo ErrHandler

    ' Open workbook
    Set xlWbk = xlApp.Workbooks.open(strWbk)

    On Error Resume Next

    ' Get worksheet
    Set xlWsh = xlWbk.Worksheets(strWsh)

    On Error GoTo ErrHandler

    If xlWsh Is Nothing Then
    ' Create it if it doesn't exist
    Set xlWsh = xlWbk.Worksheets.Add(After:=xlWbk.Worksheets(xlWbk .Worksheets.Count))
    xlWsh.Name = strWsh
    ' Set field names
    For i = 0 To rst.Fields.Count - 1
    With xlWsh.Range(strCel).Offset(0, i)
    .Value = rst.Fields(i).Name
    .Font.Bold = True
    End With
    Next i
    End If

    xlWsh.Range("A2:N50").ClearContents
    lngRec = xlWsh.Range(strCel).Offset(1, 0).CopyFromRecordset(rst)
    MsgBox lngRec & " records imported into Excel.", vbInformation
    xlWsh.Activate
    xlApp.UserControl = True
    xlApp.Visible = True

    ExitHandler:
    On Error Resume Next
    rst.close
    Set rst = Nothing
    Set dbs = Nothing
    Set xlWbk = Nothing
    Set xlWbk = Nothing
    Set xlApp = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add-in with access import (2003/2007)

    I have no experience with Tushar Mehta's add-in, but in general Excel loads add-ins normally when it is started by Automation (using CreateObject), so I don't know why this particular add-in wouldn't work. Sorry!

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add-in with access import (2003/2007)

    Curious Thanks for looking at it

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add-in with access import (2003/2007)

    Hi Hans,

    I just tried this:

    Sub Foo()
    Dim oXL as Object
    Set oXL=CreateObject("Excel.Application")
    oXL.Visible=True
    End Sub

    The Excel instance did NOT load its Excel addins (as I expected), even though they do appear checked in the addins list.
    COM addins do get loaded though.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add-in with access import (2003/2007)

    Thanks - I didn't check whether the add-ins were actually loaded, I looked at the Installed property, which was True for the ones I expected.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Add-in with access import (2003/2007)

    You basically just need to reset that property to actually load the add-ins. I usually use something like:
    <pre> Dim objXl, objAddIn
    Set objXl = CreateObject("Excel.Application")
    objXl.Visible = True
    objXl.workbooks.Add
    For Each objAddIn In objXl.AddIns
    With objAddIn
    If .Installed Then
    .Installed = False
    .Installed = True
    End If
    End With
    Next objAddIn
    </pre>


    PS You also need to specifically have the code open any startup workbooks you want available, like Personal.xls.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •