MS Access 2003 - built-in Excel spreadsheet in an access form

Question

Say I have an embedded Excel spreadsheet in the form of Microsoft Access. I call the frame of the object

ExcelFrame

and I add a text box in the form

txtA1

and I add a button in the form called

cmdInsert

I want to type “Hello World” in the text box, click the button and display it in cell A1 in this spreadsheet. What VBA am I using for this?

thank

Answer 1

You can automate Excel, write your value to a worksheet, and then update the object frame.

Private Sub cmdInsert_Click()
    Dim strPath As String
    Dim oExcel As Object
    Dim oSheet As Object

    Set oExcel = CreateObject("Excel.Application")
    oExcel.Visible = True

    strPath = Me.ExcelFrame.SourceDoc
    'Debug.Print strPath '
    oExcel.Workbooks.Open strPath
    Set oSheet = oExcel.ActiveSheet
    'Debug.Print oSheet.Name '

    oSheet.Range("A1").Value = Me.txtA1
    oExcel.ActiveWorkbook.Save
    oExcel.Quit
    Set oSheet = Nothing
    Set oExcel = Nothing
    'acOLEUpdate action requires Enabled = True '
    'and Locked = False '
    Me.ExcelFrame.Enabled = True
    Me.ExcelFrame.Locked = False
    Me.ExcelFrame.Action = acOLEUpdate
    Me.txtA1.SetFocus
    Me.ExcelFrame.Enabled = False
    Me.ExcelFrame.Locked = True
End Sub

. , .

, " ", "" . , Access 2007. , Access 2003.

Answer 2

, ?

, Excel ( OP):

Dim wb As Excel.Workbook, ws As Excel.Worksheet
Set wb = Me.ExcelFrame.Object
Set ws = wb.Worksheets(1)
ws.range("a1")= "Hello world"

, MS Excel VBA.