How to get an Excel spreadsheet to display on an Access form

Question

I have been using Access 2003 on a laptop but decided to move the application to another machine that has Microsoft 365.

I have an Access form that displays an Excel spreadsheet - below is the image of it in Access 2003.

In Access 2016 I can still import the spreadsheet but it opens separately - not within the Access form. This is what I get:

The following image is the form in design view with the relevant properties showing for the control that the spreadsheet is displayed in:

The following is the code that opens the spreadsheet supposedly into the access form:

The key line is:

Me!BrExcel.Navigate URL:=TempFilePath   '.Navigate

Any ideas why this works in Access 2003 but not in Access 2016. Both computers are running windows 10 and are 32 bit machines.

    ' Get path of Temp Excel file
    TempFilePath = CurrentProject.path & "\" & TempWbName
    Kill TempFilePath
    
    ExcelFileFound = "N"
    Set fd = fso.GetFolder(strFolder)
    For Each fe In fd.Files
        'Debug.Print fe.Name
        If fe.Name = strFileName Then
            ExcelFileFound = "Y"
            fso.CopyFile fe.path, TempFilePath, True
            SourceWbName = fe.Name
            Exit For
        End If
    Next
    If ExcelFileFound = "Y" Then
        Me!BrExcel.Navigate URL:=TempFilePath   '.Navigate
    Else
        MsgBox "Excel File Not Available In Home Folder", _
                        vbOKOnly, "Import Aborted"
    End If

Answer 1

Hi,

In Access 2016 and later versions, the Access form does not support displaying Excel spreadsheets directly within the form.

To display an Excel spreadsheet within an Access form in Access 2016 or later versions, you would need to explore alternative options, like,

One option is to use an ActiveX control such as the "Microsoft Office Spreadsheet" control (but that also depends on the version of your MS Access and the installed office components, which allows you to embed an Excel spreadsheet directly within the form.

And the other option is what you're already seeing i.e. Excel spreadsheet automatically opening in a separate window.

So, here's how you can use the option 1 that I mentioned-

1. Open the Access form in Design View.
2. If the "Developer" tab is not visible in the ribbon, enable it by going to the File tab, selecting Options, choosing "Customize Ribbon," and checking the box for "Developer."
3. Go to the "Developer" tab in the ribbon and click on "Design Mode" to enable design mode for the form.
4. Click on the "More Controls" button in the "Controls" group on the "Developer" tab. It looks like a hammer and wrench icon.
5. In the "More Controls" dialog box, scroll down and select "Microsoft Office Spreadsheet." Click on "OK."
6. You will now see a crosshair cursor. Click and drag on the form to draw the control's size.
7. Right-click on the control and select "Properties" to open the property sheet.
8. In the property sheet, you can set the properties of the "Microsoft Office Spreadsheet" control, such as the source file, the range to display, and other options.
9. Save and close the form.

This may not work 100% but it's worth a try.

Let me know if it worked for you.

Best Regards.

Answer 2

one year later, but who knows... :)))

This requires reference to the excel object for withevents. maybe it could be done without, using a hook on the window and listening to the messages, but it's overcomplicating things.

Also, the code is mostly on-the-fly, with no real error trapping

Option Compare Database
Option Explicit

Private Declare PtrSafe Function SetParent Lib "user32" (ByVal hWndChild As LongPtr, ByVal hWndNewParent As LongPtr) As LongPtr
Private Declare PtrSafe Function GetWindowRect Lib "user32" (ByVal Hwnd As LongPtr, lpRect As RECT) As Long
Private Declare PtrSafe Function GetAsyncKeyState Lib "User32.dll" (ByVal vKey As Long) As Integer
Public WithEvents xlApp As Excel.Application
Public WithEvents xlWb As Excel.Workbook
Public WithEvents xlWs As Excel.Worksheet
Public wdHwnd As LongPtr
Private Sub Form_Close()
xlApp.Visible = False
xlWb.Close False
xlApp.Quit
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing
End Sub
Private Sub Form_Load()
Init_Display 'gets twipsperpixel value
OpenExcelFile
End Sub
Public Sub OpenExcelFile()
On Error GoTo Err1
Set xlApp = New Excel.Application
    xlApp.Visible = False
xlApp.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
xlApp.Workbooks.Open FileName:="C:\avacont\conturi burse 2023-2024_IAN - macheta.xlsx", editable:=False, Notify:=False
Exit Sub
Err1:
MsgBox "Error", , NPMsg
End Sub
Private Sub Form_Resize()
Dim RC As RECT
Me.TimerInterval = 0
If (GetAsyncKeyState(1) And &H8000) = 0 Then
    Me.TimerInterval = 0
    SetWindowPos wdHwnd, 0, 0, 0, Me.insideWidth / TwipsPerPixelX, Me.InsideHeight / TwipsPerPixely, SWP_NOZORDER
Else
    Me.TimerInterval = 100
End If
End Sub
Private Sub Form_Timer()
Dim RC As RECT
GetWindowRect wdHwnd, RC
If (GetAsyncKeyState(1) And &H8000) = 0 Then
    If RC.Bottom <> Me.InsideHeight / TwipsPerPixely Or RC.Right <> Me.insideWidth / TwipsPerPixelX Then
        Me.TimerInterval = 0
         SetWindowPos wdHwnd, 0, 0, 0, Me.insideWidth / TwipsPerPixelX, Me.InsideHeight / TwipsPerPixely, SWP_SHOWWINDOW
    End If
End If
End Sub
Private Sub xlApp_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Excel.Range, Cancel As Boolean)
Cancel = True
End Sub
Private Sub xlApp_WorkbookOpen(ByVal Wb As Excel.Workbook)
wdHwnd = Wb.Windows(1).Hwnd
ShowWindow wdHwnd, 0
SetWindowText wdHwnd, wdHwnd
SetWindowLong wdHwnd, GWL_STYLE, GetWindowLong(xlApp.Hwnd, GWL_STYLE) And Not (WS_CAPTION Or WS_BORDER Or WS_SIZEBOX)
SetParent wdHwnd, Me.Hwnd
ShowWindow wdHwnd, SW_SHOWMAXIMIZED
Set xlWb = Wb
Set xlWs = xlWb.Worksheets(1)
End Sub
Private Sub xlWb_Open()
End Sub
Private Sub xlWb_SheetActivate(ByVal Sh As Object)
Set xlWs = Sh
End Sub
Private Sub xlWb_WindowActivate(ByVal Wn As Excel.Window)
SetParent Wn.Hwnd, Me.Hwnd
End Sub
Private Sub xlWs_BeforeRightClick(ByVal Target As Excel.Range, Cancel As Boolean)
Cancel = True 'disable right click. maybe add custom menu
End Sub
Private Sub xlWs_SelectionChange(ByVal Target As Excel.Range)
Debug.Print Target.Address
End Sub