开发者

Access query to Excel spreadsheet

In an Access开发者_StackOverflow 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


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:example@example.com" ''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 


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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜