Open specific Excel workbook and write to it from Access 2010 database

Posted on 2014-03-25
Last Modified: 2014-03-25
I want to open a specific Excel workbook from an Access 2010 database and write data to it. I have tried, but do not succeed.
The Access 2010 database has a form named "Form1" with a textbox  "txtWorkbookToOpen".
I enter a path to an Excel workbook in the textbox  "C:\AccessExcel\tblSales.xlsx" .
Then I want to click on a button "cmdOpenExcelWorkbook" to open that workbook and write some data to it. Say I want to write 100 in cell A1 of the active sheet.
Please help with the VBA.
I attach the database and the Excel workbook in case that will help.
Question by:Fritz Paul
LVL 23

Accepted Solution

Michael74 earned 500 total points
ID: 39954923
Just make a reference to the Microsoft Excel Object Library in your project and then use something like

Dim excel As Excel.Application
Dim wb As Excel.Workbook

Set excel = CreateObject("Excel.Application")
Set wb = excel.Workbooks.Open(txtWorkbookToOpen.Text)
excel.Visible = True
wb.ActiveSheet.Range("A1").Value = 100
Set excel = Nothing
Set wb = Nothing

Open in new window

For some more info have a look at


Author Closing Comment

by:Fritz Paul
ID: 39955201

I got a error message:

Run-time error '2185':
You can't reference a property or method for a control unless the control has the focus.

I then changed the code as below and it works. (However Excel still shows in my Windows Task Manager.)

Dim excel As excel.Application
Dim wb As excel.Workbook
Dim WorkbookToOpen As String

WorkbookToOpen = Me.txtWorkbookToOpen
Debug.Print WorkbookToOpen
Set excel = CreateObject("Excel.Application")
Set wb = excel.Workbooks.Open(WorkbookToOpen)
'Set wb = excel.Workbooks.Open("C:\AccessExcel\tblSales.xlsx")
excel.Visible = True
wb.ActiveSheet.Range("A1").Value = 100
Set excel = Nothing
Set wb = Nothing

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now