FileCopy command using FileDialog Box

  • Thread starter Thread starter LarryE
  • Start date Start date
L

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
 
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
Click to expand...
will show the values.
 
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/
__________________________________________
 
Tom Wickerath said:
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/
Click to expand...

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
 
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.
 
Hi Stuart,
That concatenation would result in:

C:\ORCAS Backup\\Filename.ext
Click to expand...

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/
__________________________________________
 
Tom Wickerath said:
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.
Click to expand...

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!
 
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.
Click to expand...

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
 
Back
Top