Copying files from user HD to network drive

[GT_engineer]

I have a database where users can attach documents or pictures to a form. I want them to be able to select the attachment they want to attach to the form from their local HD or wherever and I want it to copy/paste it automatically to a network drive so the database can link to it at all time and I don't have to worry about broken links or deleted files.

The database will contain a table with each file's name and the location address of the file.

So how can I copy and paste a file using VBA??

 

[boblarson]

FileCopy "Source As String Including file extension", "Destination path and file name including extension"

 

[GT_engineer]

wow, thanks for the quick response, I'll give it a try and let you know if I come accross any other roadblocks

 

[nanscombe]

... which is all right as long as the destination already exists.

However, what I tend to do is something along these lines.

Public Sub CopyFile(byval theSource as string, byval theTarget as string)
dim strPath as String, strTarget as String, lngIndex as Long
on error resume next ' Mainly suppresses errors where the directories already exist

  strPath =""
  strTarget = theTarget

  Do
   lngIndex = Instr(strTarget, "\")
   if lngIndex = 0 Then Exit Do
   strPath = strPath & Mid(strTarget, 1, lngIndex)
   mkDir strPath
   strTarget = Mid(strTarget, lngIndex + 1)
  Loop

  FileCopy theSource, theTarget
End Sub

This will treewalk the path of the Target and recreate any directory structure as necessary.

 

[boblarson]

You can also just find out if the path exists by

If Dir("PathAndFileNameHere", vbDirectory) = vbNullString Then
  ' folder does not exist
   MkDir("PathNameHere")
End If

Then you don't need to use On Error Resume Next (which I hate to use because it can obscure things).

 

[nanscombe]

Good idea....

Version 2:

Public Sub CopyFile(byval theSource as string, byval theTarget as string)
dim strPath as String, strTarget as String, lngIndex as Long

  strPath =""
  strTarget = theTarget

  Do
   lngIndex = Instr(strTarget, "\")
   if lngIndex = 0 Then Exit Do
   strPath = strPath & Mid(strTarget, 1, lngIndex)
   If Dir(strPath, vbDirectory) = vbNullString Then mkDir strPath
   strTarget = Mid(strTarget, lngIndex + 1)
  Loop
  
  If Dir(theTarget, vbNormal) <> vbNullString Then Kill theTarget
  FileCopy theSource, theTarget
End Sub

Adding "Kill theTarget" makes sure it effectively overwrites any existing file

 

[GT_engineer]

Is there a way to create a browser window so the user can search and find his/her's file???

Right now I'm hard coding the source path. I need the user to be able to search for the file. thanks

 

[boblarson]

Is there a way to create a browser window so the user can search and find his/her's file???

Right now I'm hard coding the source path. I need the user to be able to search for the file. thanks

There's a lot more you can do with this but here's a function you can put into a standard module to immediately use, but if you set a reference to your Microsoft Office x Object Library (where x is 11, 12, 14, etc.) then you can get intellisense to work and find out about all of the things you can do with the Application.FileDialog object.

Function BrowseForFile(Optional ButtonCaption As String, Optional DialogTitle As String) As String
    Dim fd As Object
    Const msoFileDialogFilePicker As Integer = 3
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .AllowMultiSelect = False
        If ButtonCaption <> vbNullString Then
            .ButtonName = ButtonCaption
        End If
        If DialogTitle <> vbNullString Then
            .Title = DialogTitle
        End If
        .Show
        If fd.SelectedItems.Count > 0 Then
            BrowseForFile = fd.SelectedItems(1)
        Else
            BrowseForFile = vbNullString
        End If
    End With
End Function

 

[GT_engineer]

Guys thanks alot!
Works real well. Below is the code I settled on. I don't know if there is an easier way to find the actual name of the file, I used a Do Loop and multipe Right() fucntions.

'declare variables
Dim Source As String
Dim Path As String
Dim fd As Object

Const msoFileDialogFilePicker As Integer = 3
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.AllowMultiSelect = False
.Show
If fd.SelectedItems.Count > 0 Then
Source = fd.SelectedItems(1)
Else
End
End If
End With

i = 1
Do
test = Right(Source, i)
check = InStr(test, "\")
If check <> 0 Then Exit Do
i = i + 1
Loop

Path = Right(Source, i)

Path = "C:\Pics" & Path

FileCopy Source, Path

 

[nanscombe]

This might work better ...

'declare variables
Dim Source As String
Dim Path As String
Dim fd As Object
Dim test as Long

Const msoFileDialogFilePicker As Integer = 3
  Set fd = Application.FileDialog(msoFileDialogFilePicker)
  With fd
    .AllowMultiSelect = False
    .Show
    If fd.SelectedItems.Count > 0 Then
      Source = fd.SelectedItems(1)
    Else
      End
    End If
  End With

[B]  Path = StrReverse(Source) ' [URL="http://msdn.microsoft.com/en-us/library/8ycf85wx(v=vs.84).aspx"]StrReverse[/URL]() reverses the string
  test= Instr(Path, "\")
  If test > 0 then  Path = Mid(Path, 1, test - 1)
  Path = StrReverse (Path)
[/B]
Path = "C:\Pics\" & Path

FileCopy Source, Path

By reversing the string Source "c:\test\File1.jpg" becomes "gpj.1eliF\tset\:c" so you only have to find the first (last) '\'. Then reverse it again to get "File1.jpg".

 

[boblarson]

InstrRev also works fine.

Path = Left(Source, InstrRev(Source, "\") )

 

[nanscombe]

Even easier...

 

[GT_engineer]

Path = Right(Source, Len(Source) - InStrRev(Source, "\") + 1)

 

[fra]

Hi,
This thread has made a wish come true: For the first time I have managed to successfully add a file path selected from a browser window to a table's field!
One slight inconvenience remains - the code presented opens the file browser at the location of the database:
"Set fd = Application.FileDialog(msoFileDialogFilePicker)"
Would it be possible to open the browser directly at the desired drive/subfolder, including a variable (MovieCat) path element:
"M:\media\movies\" & MovieCat & "\"
which the selection of a particular file would then complete to:
"M:\movies\movies\Fiction\myfanart.jpg"
I should be very grateful for any suggestions.
Fritz

 

[nanscombe]

Courtesy of InitialFileName Property, It looks like you may just need to add an extra line ...

Set fd = Application.FileDialog(msoFileDialogFilePicker)
  With fd
    [B].InitialFileName = "<yourPathGoesHere>"[/B]
    .AllowMultiSelect = False
    .Show
    If fd.SelectedItems.Count > 0 Then
      Source = fd.SelectedItems(1)
    Else
      End
    End If
  End With

 

[fra]

Nanscombe, many thanks - it works perfectly!

Fritz

 

[fra]

Nanscombe, thank you very much - it works perfectly!
Fritz