Vba file transfer help (1 Viewer)

Hi everyone. I'm a bit of a newbie to vba and access in general but it is making sense and I've made good progress, I am however stuck on this point.

I'm trying to put a filecopy after something similar to the code I have used below and I was hoping to use the mydrive or mydrivedir to specify the from location being from a USB stick to he computer.

Any help would
Be appreciated and I will keep working on it in the meantime.

Sub Test1()
Dim MyDrive$, MyDriveDir$
MyDrive = "D"
On Error Resume Next
MyDriveDir = Dir(MyDrive & ":", 5)
If MyDriveDir = "" Or IsError(MyDriveDir) Then
MsgBox "No disk is in Drive " & MyDrive
Else
MsgBox "Drive " & MyDrive & " has a disk and is ready!"
End If
Err.Clear
End Sub

And when you try to execute this code, what happens?
Error message? Number?

More info on Dir(), if needed

Perhaps you could tell readers more about

I've made good progress

Click to expand...

Sorry, just read that back and I've been too brief, this is the full code I am running, probably an organisational nightmare to a pro.

I can successfully get it to work if I have a fixed file from location but as we use 2 machines with drives mapped differently here I added to code to look for a: if d: wasn't present and I'm trying to use that "drive" decision to do the "filecopy" from location

all I get is my error handler telling me there was an error


Private Sub data_backup_Click()

MsgBox "Please ensure USB stick is inserted"

Dim MyDrive$, MyDriveDir$
On Error Resume Next
MyDrive = "D"
MyDriveDir = Dir(MyDrive & "$")
TryAgain:
If MyDriveDir = "" Or IsError(MyDriveDir) Then GoTo NextDriveLetter




MsgBox "Drive " & MyDrive & " has a disk and is ready!"
Err.Clear


On Error GoTo ErrHandler1:
FileCopy " & MyDriveDir & DATA.xls", "F:\DATA.xls"
On Error GoTo ErrHandler2:
FileCopy " & MyDriveDir & DATA_QR.xls", "F:\DATA_QR.xls"


MsgBox "Backup Completed"
Exit Sub
ErrHandler1:
MsgBox "Error Copying DATA.xls"

Resume Next

ErrHandler2:
MsgBox "Error Copying DATA_QR.xls"


Exit Sub


NextDriveLetter:
If MsgBox("Drive " & MyDrive & " was not present, try another location?", vbOKCancel) = vbCancel Then
Exit Sub

Else

MyDrive = "A"
MyDriveDir = Dir(MyDrive & "$\GeneralUSB_sda1")

End If

GoTo TryAgain:

End Sub

As your code shows, the actual copying of a file to another location can be done using

Sub Copy_One_File()
FileCopy "C:\Users\Bob\SourceFolder\Test.xls", "C:\Users\Bob\DestFolder\Test.xls"
End Sub

It seems your issue is dealing with different output locations???
Can you tell us more about the Access database, the users, the files etc.
Do you understand ms Access and multi-users?

Have you tried to diagram the logic you are trying to implement?

our database isn't a multi user, it will only have 1 person at a time, but depending on where we are, the usb stick is mapped to either a:\ or d:\. I thought I would be able to use the top part of the code with the error handler to swap between them, and it works to a point, it wont let me use

&mydrive& instead of a:\ or d:\ at the beginning of the file copy

as the earlier code identifies the location of the usb drive we are getting our data from.

here is a simple function that will return (in string) usb drive and capacity of the media.
it will return blank string if there is no media inserted on usb or other device is inserted (ie phone).

the return string is in format <driveLetter:>;<capacity>
so you have to use split to get the letter of drive available: