How can a macro overwrite export file? 1

[mikeraut]

I have added a macro to export my query to MS Excel. The resulting file needs to be replaced each time the macro runs.
The oprions within the macro editor for Autostrat are Yes and No.
Is there a solution to allow the macro to replace the file automatically?
The alternate solution would to prepend the export file with a time/date stamp (but it is beyond what I am capable of)

 

[kjv1611]

What code are you currently using to carry this out? DoCmd.TransferSpreadsheet? Or some other?

And I doubt that (but it is beyond what I am capable of) is true. You just don't know how yet. I promise it isn't difficult.. not ability, just what you do or don't know.

A way to do what you want via TransferSpreadsheet:

Private Sub cmdExport_Click()
  Dim strFilePath As String [green]'For the file path of your exported file - where you want to save it.[/green]
  Dim strFileName As String [green]'For the actual file name of your exported workbook.[/green]
  Dim strQuery As String [green]'Your Query Name[/green]
  
  strFilePath = "Z:\MyFolder\MySubFolder\"
  strFileName = "qryMyQueryName" & ".xls"
  strQuery = "QueryNameToExport"

  DoCmd.TransferSpreadsheet acExport,,strQuery,strFilePath & strFileName

End Sub

Now that's a little more than you need. All you really need is the DoCmd.TransferSpreadsheet statement, but I inserted the string variables to make the statement shorter and easier to read.

As for appending today's date, that also is not a problem, if you want to do it - you just pick the date format. So you could do that along with the above code:

Private Sub cmdExport_Click()
  Dim strFilePath As String [green]'For the file path of your exported file - where you want to save it.[/green]
  Dim strFileName As String [green]'For the actual file name of your exported workbook.[/green]
  Dim strQuery As String [green]'Your Query Name[/green]
  
  strFilePath = "Z:\MyFolder\MySubFolder\"
  strFileName = "qryMyQueryName" & Format$(Date, "yyyymmdd") & ".xls"
  strQuery = "QueryNameToExport"

  DoCmd.TransferSpreadsheet acExport,,strQuery,strFilePath & strFileName

End Sub

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

BTW, using the DoCmd.TransferSpreadsheet Method, you automatically overwrite any previous files with that file name. That can be both a blessing and a curse. Make sure when you use that, that you WANT to overwrite the file, b/c there will be no warning by default. If you want to make sure the file does not already exist, you'd need to use another object or two for looking at your file system.

--

"If to err is human, then I must be some kind of human!" -Me

 

[thefourthwall]

kjv1611,

Found your replies searching for the same solution as mikeraut.

What I do not know how to do is "attach" the code to an event, or have it run as part of another macro, say autoexec.
Would it be either the openFunction of openModule actions in the macro design window?

Got questions on the username of yours sometime too ...

TIA,
thefourthwall

 

[JerryKlmns]

Macros can execute Functions but not Subs.

So, replace "Function" with "Sub" for the above code and for the Macro, the action should be RunCode and choose the function bellow.