Access/Excel

  • Thread starter Thread starter Pat
  • Start date Start date
P

Pat

I am basically an access person who now needs to use
Excel also. Here is what I am trying to do and possibly
someone can give me a push in the right direction.
I have all our data an an access database. What I wish
to do is set up and excel spreadsheet and export the
result of a query from access into this spreadsheet(this
I can do). Then I wish to do some type of a link or
something that when I update the database and rerun the
query, the new results will flow to the spreadsheet so I
don't have to update it all the time. Have all the
technical books, just not sure what it is I am trying to
do. Any help would be appreciated
 
Hi Pat,

To automate the process of re-exporting a query
after you've updated the database, you could write
some vba code and run the code from the click
event of a command button on a form or from a
toolbar button. I give some sample code below.

If you want to reformat the Excel spreadsheet from
within Access, you need to write vba code and the
topic you need to look up is "Automation". If you
need some sample Automation code, post again to
this thread. This can get complex.

Good luck.
Geoff

In the following code, if the query or path might
not exist, then an error will be generated. The
ErrorHandler code will display an Access error
message. If you need a friendlier message for
users, you could write code to test if the query
and path exist - and if they don't, display a
friendly message. (If you want sample code, post
to this thread again.) If you're the only user,
then you can simply make sure the query and path
do exist and an error won't happen.

*** CODE START ***

Sub MyButton_Click()

' Example of how to call the "ExportQuery"
SubProcedure (below).

ExportQuery "Query1", "C:\My
Documents\MyFile.xls"

End Sub

Sub ExportQuery(strQueryName As String,
strFilePathName As String)

' Export a query to an Excel spreadsheet.

' In:
' strQueryName = The name of an existing
query.
' strFilePathName = The full path and
name of the Excel spreadsheet
' file to which the
query is to be exported.

On Error GoTo ErrorHandler

' You may need to change the constant
"acSpreadsheetTypeExcel9"
' to reflect your version of Excel:
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, strQueryName,
strFilePathName

Bye:

Exit Sub

ErrorHandler:

MsgBox Err.Description, vbOKOnly, "Error
Number: " & Err.Number
Resume Bye

End Sub

*** CODE END ***
 
Back
Top