FileCopy command using FileDialog Box

[LarryE]

I want users to be able to select a file using
FileDialog(msoFileDialogFilePicker) and then have that file copied to a
specific location on the users C Drive. I am having a terrible time defining
the file selected to the FileCopy 'soucefile' parameter. It only accepts text
and the file selected needs to be defined as a Variant for the FileDialog
command to work. Below is the code I have written, but I get a Runtime 52
'Bad file name or number' error. Does anyone know how to do this? VBA is
MADDNING!

Dim CopyDialog As Office.filedialog
Dim SourceFile As Variant
ChDir Dir("*.*", vbDirectory)
Set CopyDialog = Application.filedialog(msoFileDialogFilePicker)
With CopyDialog
.AllowMultiSelect = True
.Title = "Select File"
.Filters.Add "Access Databases", "*.accdb"
If .Show = True Then
For Each SourceFile In .SelectedItems
FileCopy SourceFile, "C:\ORCAS Backup\" & SourceFile
Next
End If
End With
End Function

 

[David C. Holley]

If memory serves, the file dialog returns the full path to the file. As such
the FileCopy command is actually seeing

FileCopy "H:\data\users\holled\documents\file1.doc", "C:\orcas
backup\H:\data\users\holled\documents\file1.doc"

Since "H:\data\users\holled\documents\file1.doc" is the value in the
variable SourceFile the statement "C:\ORCAS Backup\" & SourceFile results in
the example above.

You'll need to scrub the value to eliminate the path information.

Here are some examples...
http://snippets.dzone.com/posts/show/533
http://www.vbaexpress.com/kb/getarticle.php?kb_id=757
http://www.vbaexpress.com/kb/getarticle.php?kb_id=767

Also, keep in mind that when you're working with variables adding a
Msgbox([variable name here]) or Debug.Print [variable name here] can go a
long way in sorting out the problem. You can also set a watch which allows
you to inspect the values of all of your variables or a specific one. DEBUG

ADD WATCH adds the watch. VIEW >> LOCALS WINDOW and VIEW >> WATCH WINDOW

will show the values.

 

[Tom Wickerath]

Hi Larry,

You're almost there...the problem is that your target looked something like
this:
C:\ORCAS Backup\G:\Databases\MyDB.accdb

Try this:

For Each SourceFile In .SelectedItems
FileCopy SourceFile, "C:\ORCAS Backup\" _
& Mid$(SourceFile, InStrRev(SourceFile, "\"))
Next


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Stuart McCall]

Hi Larry,

You're almost there...the problem is that your target looked something
like
this:
C:\ORCAS Backup\G:\Databases\MyDB.accdb

Try this:

For Each SourceFile In .SelectedItems
FileCopy SourceFile, "C:\ORCAS Backup\" _
& Mid$(SourceFile, InStrRev(SourceFile, "\"))
Next


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/

That concatenation would result in:

C:\ORCAS Backup\\Filename.ext

Should be:

For Each SourceFile In .SelectedItems
FileCopy SourceFile, "C:\ORCAS Backup\" _
& Mid$(SourceFile, InStrRev(SourceFile, "\") + 1)
Next

 

[LarryE]

Tom and Stuart, yup that worked. I would have never got that. Can't thank you
guys enough. Apparently, it DOES pay to hang around programming geeks. Ha Ha.
I owe you both a Sam Adams.

 

[Tom Wickerath]

Hi Stuart,

That concatenation would result in:

C:\ORCAS Backup\\Filename.ext

Now that I look at it again, I see that you are correct....but, the form I
gave still works correctly. Apparently the FileCopy method is resilient
enough to deal with this little blunder.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Stuart McCall]

Hi Stuart,


Now that I look at it again, I see that you are correct....but, the form I
gave still works correctly. Apparently the FileCopy method is resilient
enough to deal with this little blunder.

I see what you mean (just tried it). I'm not sure I like that, though. I'd
rather get an error because it does break the rules after all.

There's just no pleasing some people!

 

[David W. Fenton]

Tom and Stuart, yup that worked. I would have never got that.
Can't thank you guys enough. Apparently, it DOES pay to hang
around programming geeks. Ha Ha. I owe you both a Sam Adams.

Well, let me complicate things. I would recommend against using the
FileDialog object. It's a wrapper around a call to the Windows API
and is subject to Microsoft's whims as to whether it will continue
to exist in future versions of Access (the FileSearch object, for
instance, was removed in Office 2007). But the Windows API file open
dialog will always works so long as the Win32 API is supported in
Windows.

http://mvps.org/access/api/api0001.htm

 

[David C. Holley]

I came close to posting the mvps.org solution be deferred for the built-in.