How to open an Excel workbook from Access

Question

I currently have a string in Microsoft Access that is delegating functions to an external Application.

I have gotten so far as to copying a set of data from the external application.

I want to paste this into an excel workbook that is not open but exists. C:\Users\abcdef\Desktop KDNR.xlsx

how can I integrate this function into my sub procedure?

Thank you in advance

I attempted simply writing

Dim x as Workbook 

set x = Workbooks.Open (" workbook name ")

Howoever, i got the compile error "user defined type not defined"

when i just write

Workbooks.Open (" workbook name ")

i get the compile error

"variable not defined"

Did you add an Excel reference to MS Access? It's not going to know what a workbook is if you don't. — braX, Nov 03 '22 at 01:27
`Dim wb as object` and `set wb = GetObject("c:\MyFolder\MyFile.xlsx")`. Review my answer here for your options. https://stackoverflow.com/questions/74269979/vb-net-add-workbook-to-running-excel-instance/74270544#74270544 — Lundt, Nov 03 '22 at 01:42

score 1 · Answer 1 · answered Nov 03 '22 at 03:19

Use Excel From Access

This is a basic example of how to work with Excel from Access.
It opens a new instance of Excel (whether Excel is open or not), opens the workbook, writes the string Test to cell A1 of worksheet Sheet1, saves and closes the workbook, and finally quits the Excel instance.

' If you haven't already, create a reference to
' "Tools->References->Microsoft Excel 16.0 Object Library"!

Sub Test()
    
    Dim DestinationFilePath As String
    DestinationFilePath = Environ("USERPROFILE") & "\DeskTop\KDNR.xlsx"
    
    Dim xlApp As Excel.Application: Set xlApp = New Excel.Application
    xlApp.Visible = True ' out-comment when done developing; default is 'False'
    
    Dim wb As Workbook: Set wb = xlApp.Workbooks.Open(DestinationFilePath)
    
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
    
    ws.Range("A1").Value = "Test"
    
    wb.Close SaveChanges:=True
    
    xlApp.Quit
        
End Sub

How to open an Excel workbook from Access

1 Answers1

Use Excel From Access