In an Access database I have a query table that is linked to a form when the form is opened and edited.
How can I link this query table to Excel so the when I click a button on the form in Access the spreadsheet in Excel is opened showing all items in the Access query table, the user can then edit the spreadsheet if required.
All in Access/Excel 2003
(I'm using 2007, but same applies) In the button's click event (use code builder, not macro), the code
Shell "excel.exe " & Chr(34) & "C:\FullPathToYourFile\file.xls" & Chr(34), vbMaximizedFocus
will do it, although inelegantly. Missing out the chr(34) breaks with paths or filenames with spaces in them, and although wrapping the path with ' should work in it's place, it isn't on my 2007 version.
I am not familiar with how to do it in macros, or if it's possible.
FollowHyperlink can be a useful way to open a document of any type with its registered application:
FollowHyperlink "C:\SomeDir\BookX.xls" ''Excel
FollowHyperlink "C:\SomeDir\" ''Explorer
FollowHyperlink "mailto:[email protected]" ''Default email
More: http://msdn.microsoft.com/en-us/library/aa204494%28office.10%29.aspx
If you wish to manipulate the Excel file after you have opened it, it may be best to use Automation:
Dim strFileName As String
Dim objXL Object
Dim wkb As Object
''New instance, GetObject for existing instance
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
strFileName = "C:\Docs\LTD.xls"
Set wkb = objXL.Workbooks.Open(strFileName)
''Do stuff
''Give control to user
objXLS.UserControl = True