How to open an Excel file from Access 2010?

[stu]

Hi,



I need to open an excel *.xlsx file from Access 2010 using a macro or VBA but can't get any of the examples on various forums to work. Please help.

Thanks in advance.

Stu

[June7]

Why can't you get code to work - error message, wrong results, nothing? What methods have you attempted? Show the code so we can analyse.

[stu]

Hi, nothing happens at all. The code is -

Private Sub Command273_Click()

Dim xl As Excel.Application
Dim wbk As Excel.Workbook
Dim wsht As Excel.Worksheet
Dim filename As String
Dim lastRow As Integer
       
Set xl = New Excel.Application
xl.DisplayAlerts = False
Set wbk = xl.Workbooks.Add("path\filename.xlsx")

End Sub

This is just a piece of code I picked up from a forum search. Can you tell me how you would go about opening an excel file by clicking a button in Access 2010?

Many thanks,

Stu

[June7]

Depends on what you want to do with the open workbook.

If you just want to open the same workbook every time, then can be done as a hyperlink. Use the HyperlinkAddress property of a command button. Put the path to the file, like: D:\LL\TournUmpires.xlsx. Put whatever you want in the Caption. If you want to dynamically open variety of workbooks, review http://allenbrowne.com/func-GoHyperlink.html

If you want to open the workbook and have Access write data to/from cells, that requires opening an Excel object and code will manipulate the Excel object. It is this situation that your code is for. Need to use the actual drive and path and filename for your workbook in place of the generic example in the code. Will also have to set a VBA reference to: Microsoft Excel 14.0 Object Library. I tested the procedure (with my drive\path\filename as shown in previous paragraph) and it does open an instance of Excel as can be seen in the Windows Task Manager. The app just isn't visible. Need line in code: xl.Visible = True
Review http://forums.aspfree.com/microsoft-...el-414974.html

[stu]

Sorted. Thank you June7!

[squatter]

Thank you sir. it really help....