Copy function on Output to file

[benchpolo]

Function outfile()
    Dim stbl As String
    stbl = "Contact"
    DoCmd.OutputTo acOutputTable, stbl, acFormatXLS, "c:\temp\contact_" & Format(Now(), "yyyymmdd") & "_" & Format(Time(), "hhmmss") & ".xls"
    MsgBox "File Exported"
End Function
 

Exported Filename: contact_20071029_104206.xls

Question? How do I copy (VBA code) the Exported filename to a different location?

Thanks.

[missinglinq]

How do I copy (VBA code) the Exported filename to a different location?

You can change your code slightly, like this:

Function outfile()
Dim stbl As String
Dim FileName as String
 
stbl = "Contact"
 
FileName = "contact_" & Format(Now(), "yyyymmdd") & "_" & Format(Time(), "hhmmss") & ".xls"
 
DoCmd.OutputTo acOutputTable, stbl, acFormatXLS, "c:\temp\" & FileName
 
'Copy to a different location
 DoCmd.OutputTo acOutputTable, stbl, acFormatXLS, "d:\temp\" & FileName
 
 
MsgBox "File Exported"
End Function
 

and you'd have your filename as it was for the output file, right down to the second. But where do want to "copy" it to?

Linq ;0)>

[benchpolo]

To another location like a backup external hardrive.

[benchpolo]

What about file scripting? (see code below). With this code it is basically copying file from one source (c:\temp) to a new destination (c:\temp\txt), but i am getting error "Path not found". Thanks.

Function copyfile()
    Dim fs As Object
    Dim oldPath As String, newPath As String
 
    oldPath = "C:\Temp\contact_" & Format(Now(), "yyyymmdd") & "_" & Format(Time(), "hhmmss") & ".xls"
    newPath = "C:\Temp\txt\contact_" & Format(Now(), "yyyymmdd") & "_" & Format(Time(), "hhmmss") & ".xls"
 
    Set fs = CreateObject("Scripting.FileSystemObject")
 
    fs.copyfile oldPath & "\" & "contact_" & Format(Now(), "yyyymmdd") & "_" & Format(Time(), "hhmmss") & ".xls", newPath & "\" & "contact_" & Format(Now(), "yyyymmdd") & "_" & Format(Time(), "hhmmss") & ".xls"
 
    Set fs = Nothing
End Function
 

[missinglinq]

So you don't want to copy the filename to another location, as you said, but rather copy the file to another location. Go back to my previous post, where I've edited my code. Line # 12 repeats your operation, sending the file to the D drive and same subfolder as the original. You can modify this path to suit your needs. That's all you need to do.

Linq ;0)>

[Rabbit]

This is more efficient for copying a file:

FileCopy "Source Path", "Destination Path"
 

This is slightly more efficient as far as formatting the date goes:

Format(Now(), "yyyymmdd_hhmmss")
 

[benchpolo]

I think I will stick to your suggestion.. I don't to do tidious coding.. like the old saying "Work Smart Not Hard".. Thanks for all the inputs! =)

[Rabbit]

Good luck.