CODEWITHSUNDEEP
excelms-access
Shaun
Shaun

Reputation: 99

Access query to Excel spreadsheet

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

Upvotes: 1

Views: 999

Answers (2)

Fionnuala
Fionnuala

Reputation: 91306

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

Upvotes: 1

Doogie
Doogie

Reputation: 815

(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.

Upvotes: 0

Related Questions