Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-03-25
2
Medium Priority
?
487 Views
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.
Database14.accdb
tblSales.xlsx
0
Comment
Question by:Fritz Paul
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 23

Accepted Solution

by:
Michael Fowler earned 1500 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
excel.Quit
Set excel = Nothing
Set wb = Nothing

Open in new window


For some more info have a look at http://support.microsoft.com/kb/198571

Michael
0
 

Author Closing Comment

by:Fritz Paul
ID: 39955201
Thanks,

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
excel.Quit
Set excel = Nothing
Set wb = Nothing
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

618 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