'output to' question

[Steve Goodrich]

I have a query that I transfer to a text file using the 'Transfer text'
option in a macro. I have this macro assigned to the close event on my form
so when the database is closed the text file is updated and I don't get any
prompts saying that the file already exists - do you want to overwrite.,
works great,

I would like to do transfer the query into an Excel spreadsheet instead of a
text file. I am using the 'Output to' option when creating the macro -
output format Excel, and that also works great with one exception. I am
prompted to overwrite the existing file each time the macro runs, I.e. every
time I close the database.

My question:
Can I make it so the Excel file is updated automatically without me being
prompted, the same way the Transfer Text macro works when creating a text
file.

Many thanks

Steve

 

[Jeanette Cunningham]

Steve,
being prompted is generally a good idea in most situations.
To avoid the prompt, you can check if the file already exists and delete it
before you run the outputto macro.
This is vba code, there are no built in macro functions to delete a file.
However macros do have the ability to run a vba function - it is called Run
Code.
Put the following code in a new module (create a new module in the modules
section of the database window)
For the macro that runs the code, the name of the function to call is
DeleteXLFile
---------------------------
Public Function DeleteXLFile
Dim strPathAndNameOfFile as String

strPathAndNameOfFile = "C:\MyFileName"

If Len(Dir(strPathAndNameOfFile))>0 Then
Kill strPathAndNameOfFile
End If
End Function
------------------------------

strPathAndNameOfFile is the file name including extension and full path to
the file


Jeanette Cunningham -- Melbourne Victoria Australia

 

[Steve Goodrich]

Jeanette,
It worked perfectly, many thanks
Steve

Steve,
being prompted is generally a good idea in most situations.
To avoid the prompt, you can check if the file already exists and delete
it before you run the outputto macro.
This is vba code, there are no built in macro functions to delete a file.
However macros do have the ability to run a vba function - it is called
Run Code.
Put the following code in a new module (create a new module in the modules
section of the database window)
For the macro that runs the code, the name of the function to call is
DeleteXLFile
---------------------------
Public Function DeleteXLFile
Dim strPathAndNameOfFile as String

strPathAndNameOfFile = "C:\MyFileName"

If Len(Dir(strPathAndNameOfFile))>0 Then
Kill strPathAndNameOfFile
End If
End Function
------------------------------

strPathAndNameOfFile is the file name including extension and full path
to the file


Jeanette Cunningham -- Melbourne Victoria Australia