Copy files using access 1

[ianbar]

I would like a button that a user can click on my form, that when clicked copies files from one directory on the network to another. Is this possible and if so how?

 

[GHolden]

Check out FileCopy in the help...



There are two ways to write error-free programs; only the third one works.

 

[ianbar]

I have Access 2K and have looked it doesn't appear to be in their. I waws hoping to do this using Access VB.

 

[GHolden]

If you include a reference to Microsoft Scripting Runtime you can use this...

Private Sub mCopyFile(ByVal vstrFrom As String, ByVal vstrTo As String)
Dim objFSO As Scripting.FileSystemObject

Set objFSO = CreateObject("Scripting.FileSystemObject&quot

objFSO.CopyFile vstrFrom, vstrTo

Set objFSO = Nothing

End Sub

You can call using ...

mCopyFile "C:\test\Test.xls", "c:\temp\"

This will copy C:\test\Test.xls to c:\temp


There are two ways to write error-free programs; only the third one works.

 

[GHolden]

BTW you can also use UNC

eg.

mCopyFile "\\worthing-s1\users\grahamh\misc\temp.xls", "c:\temp\"

There are two ways to write error-free programs; only the third one works.

 

[ianbar]

Thanks very much.

I need to copy the contents of a folder (the contents could be anything from 0 to 10 files) and they need to be another folder, overwriting any previous copies. Would your code be capable of this?

 

[GHolden]

Yep, no problem...

mCopyFile "\\worthing-s1\users\grahamh\misc\*.*", "c:\temp\"

would copy all files..

mCopyFile "\\worthing-s1\users\grahamh\misc\*.xls", "c:\temp\"

would copy all xls files...

etc.



There are two ways to write error-free programs; only the third one works.

 

[ianbar]

Thanks very much. Have a star!

 

[GHolden]

Cheers, no problem..



There are two ways to write error-free programs; only the third one works.

 

[ianbar]

When I try to run the code I get:

Invalid Proceedure Call or Argument.

I'm using Access 2k, will this make a difference. I have also referenced the Ms Scripting Runtime.

The line that is highlighted by the debugger is 'objFSO.CopyFile vstrFrom, vstrTo'. Any ideas?

 

[GHolden]

Although I've never used this in Access 2K I've used it in various incarnations of VBA and VB6. So I don't see why it shouldn't work with 2K.

Try doing a simple hardcoded example...

eg.

Private Sub mTest()
Dim objFSO As FileSystemObject

Set objFSO = CreateObject("Scripting.FileSystemObject&quot
objFSO.CopyFile "C:\Test\test.xls", "C:\temp\"

Set objFSO = Nothing
End Sub

Also when you type objFSO. in the editor does the the list of options appear with CopyFile listed?

Let me know what happens...




There are two ways to write error-free programs; only the third one works.

 

[ianbar]

This works, i was trying to use a user definable path (held in a table). But it doesn't like it when I add anything else but coded paths.

 

[GHolden]

Can you post your code?

With the calling statement


There are two ways to write error-free programs; only the third one works.

 

[ianbar]

I'll post it monday. I'll have to recode it!

 

[GHolden]

Okay no probs

There are two ways to write error-free programs; only the third one works.

 

[ianbar]

Private Sub cmdtransfer_Click()
Dim objFSO As FileSystemObject
Dim Msg, title, Response, Style As String

Msg = "This option will overwrite older files, do you wish to proceed?"
title = "Warning!"
Style = vbYesNo + vbCritcal + vbDefaultButton2
Response = MsgBox(Msg, Style, title)

If Response = vbYes Then
Set objFSO = CreateObject("Scripting.FileSystemObject&quot
objFSO.CopyFile Me!OFT_Temp_Folder & "*.*", Me!OFT_Dest_Folder
Set objFSO = Nothing
MsgBox "Files succesfully transfered.", vbOKOnly, "Success"
Else:
MsgBox "Operation cancelled.", vbOKOnly, "Cancelled"
End If

End Sub


Here's my code. Which references two txtboxes on a form. Also I would like the files that have been copied to be deleted from the Temp folder.

Thaks again for your help.

 

[GHolden]

Seems to work okay. Are you typing the full path name in the text boxes, and including the \ character at the end of each one?



There are two ways to write error-free programs; only the third one works.

 

[ianbar]

Your right, how dumb am I? It didn't work with the old code!

Could you help me with the removal of the original file in the temp folder? Here's the code I tried:
-------------------------------------------
Private Sub cmdtransfer_Click()
Dim objFSO As FileSystemObject
Dim Msg, title, Response, Style As String

Msg = "This option will overwrite older files, do you wish to proceed?"
title = "Warning!"
Style = vbYesNo + vbCritcal + vbDefaultButton2
Response = MsgBox(Msg, Style, title)

If Response = vbYes Then
Set objFSO = CreateObject("Scripting.FileSystemObject&quot
objFSO.CopyFile Me!OFT_Temp_Folder & "*.*", Me!OFT_Dest_Folder
Set objFSO = Nothing
objFSO.DeleteFile Me!OFT_Temp_Folder & "*.*"
Set objFSO = Nothing
MsgBox "Files succesfully transfered.", vbOKOnly, "Success"
Else:
MsgBox "Operation cancelled.", vbOKOnly, "Cancelled"
End If

End Sub
-------------------------------------------

I get the error 'object variable or with block variable not set'.

 

[GHolden]

Take out the line in red, at the moment you're setting the objFSO to nothing and then trying to use it on the next line hense 'object variable or with block variable not set', because you've unset it.

Dim objFSO As FileSystemObject
Dim Msg, title, Response, Style As String

Msg = "This option will overwrite older files, do you wish to proceed?"
title = "Warning!"
Style = vbYesNo + vbCritcal + vbDefaultButton2
Response = MsgBox(Msg, Style, title)

If Response = vbYes Then
Set objFSO = CreateObject("Scripting.FileSystemObject&quot
objFSO.CopyFile Me!OFT_Temp_Folder & "*.*", Me!OFT_Dest_Folder
Set objFSO = Nothing
objFSO.DeleteFile Me!OFT_Temp_Folder & "*.*"
Set objFSO = Nothing
MsgBox "Files succesfully transfered.", vbOKOnly, "Success"
Else:
MsgBox "Operation cancelled.", vbOKOnly, "Cancelled"
End If


There are two ways to write error-free programs; only the third one works.

 

[ianbar]

I see! I thought that unloaded the current function, in this case CopyFile and then I was loading in the next function. Oh well, it's sorted now. Thanks very much for all your help.