[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 496
  • Last Modified:

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

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
Fritz Paul
Asked:
Fritz Paul
1 Solution
 
Michael FowlerSolutions ConsultantCommented:
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
 
Fritz PaulAuthor Commented:
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now