CODEWITHSUNDEEP
excelvbams-access
apor
apor

Reputation: 31

How to open an Excel workbook from Access

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"

Upvotes: 2

Views: 5493

Answers (1)

VBasic2008
VBasic2008

Reputation: 54807

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

Upvotes: 3

Related Questions