Copy a file using VB in an Access 2000 DB

[jvanderw]

Looking for a little help to do the following:
I have a file called template.xls that I need to copy to a location on a server. The template file is at //mars/shared/iedept/webportal/template.xls and I will need to copy this file to //mars/shared/clientreviewdocs/##/$$$-template.xls.
## = a publisher code that is in the Access DB field [criteria]![pubcode] and $$$ = a magazine code that is in the Access DB field [criteria]![magcode]. The tricky part (for me anyway) is that some times the ## folder doesn't exists, so it may have to be created. I will also want to delete the //mars/shared/clientreviewdocs/##/$$$-template.xls if it exists before I copy the new file to that location.
Thanks for the help.
Josh

 

[JeffTullin]

I will assume you can get the values of ## into a variable.

You can use DIR to quickly tell if a file or folder exists.

Folder:
MsgBox (Dir("C:\testfolder", vbDirectory)) >""

(shows TRUE if folder exists)

File:
MsgBox (Dir("C:\testfolder\canary.txt&quot) >""

(shows TRUE if file exists)


To delete a file, use KILL <filename>
(use some error checking - it may fail if you don't have permissions to do this on the network..)

To create a folder, use MKDIR <pathname>

 

[TomGuy]

Check if the file exists:

If Dir$(&quot;C:\My Folder\My File.xls&quot <> &quot;&quot; Then
msgbox &quot;It exists.&quot;
End If

Delete files with:

If Dir$(&quot;C:\My Folder\My File.xls&quot <> &quot;&quot; Then
Kill (&quot;C:\My Folder\My File.xls&quot ' Deletes the file
End If

 

[jpittawa]

Give this a shot.

Public Function CopyMonster()
Dim sPath As String
Dim sFullFileName

sPath = &quot;//Mars/Shared/ClientViewDocs/FolderX&quot;

'Make sure the folder exists:
If Dir(sPath, vbDirectory) = &quot;&quot; Then
MkDir sPath
End If

'Zap the file if its there:
sFullFileName = sPath & &quot;/template.xls&quot;
If Dir(sFullFileName) <> &quot;&quot; Then
Kill sFullFileName
End If

'Copy the File
FileCopy &quot;C:/WorkDir/template.xls&quot;, sFullFileName

End Function