If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
'output to' question
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 |
#2
|
|||
|
|||
'output to' question
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" wrote in message . .. 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 |
#3
|
|||
|
|||
'output to' question
Jeanette,
It worked perfectly, many thanks Steve "Jeanette Cunningham" wrote in message ... 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" wrote in message . .. 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 |
Thread Tools | |
Display Modes | |
|
|