Copy files into specific folder

[Jan22]

I have created a database that saves a link of technical data.
Now i have forms where you can add the hyperlink of the data in the database.
You can also type information about the data into the form (like location, what kind of part etcc.)

But I was asked to save the data in a specific folder that you can select.

So what I have to do is:

Browse for a file in a form


Save the hyperlink of the file in the database
Don't save the file in the database, but copy the file that's selected when browsing to specified folder

Could someone help me out?

[jhargram]

Hi Jann, As per above issue you want to save in a specific folder automatically or you want to save that data through select folder (manual) option.

[Jan22]

I want to save data automatically into a folder.

On my form i can browse for a file
The hyperlink is saved into the database

But before the hyperlink is saved into the database
i want to copy the file to a folder that is specified and then save the hyperlink of the copied file.
So they don't have to look up the folder and save the file by themselfs.

I want to do this because many people will add data from for example a usb stick into the database.

[jhargram]

Still I want some clarification regarding this.
you want to import the data from outside or Export the data from your database to outside folder...

[Jan22]

First I want to browse for a file.

Then i want to copy the file from an usb stick to a specified folder on the hard disk.

Then i want to import the hyperlink of the file ( for example C:\mydocuments\Technicalinfo.doc) into the database.

[jhargram]

for that you can use filedialog(msofiledialogopen) with filter add *.doc (for word file)...

[Jan22]

Thank You!
Now i can copy files from one destination to another..

Private Sub Knop11_Click()
Dim Pad As String
Dim Bestandnaam As String
Dim Regel As String
Pad = "C:\"
With Application.FileDialog(3)
.Title = "Bestand selecteren"
.Filters.Clear
.AllowMultiSelect = False
.InitialFileName = Pad
If .Show <> 0 Then
Bestandnaam = .SelectedItems.Item(1)
Open Bestandnaam For Input As #1
Line Input #1, Regel
MsgBox "Op de eerste regel van het bestand staat:" & vbLf & vbLf & Regel
Close #1
End If
End With
End Sub

But could you tell me how i could save the hyperlink automaticly with a VBA code?

[Jan22]

How can i adjust this VBA code to save the hyperlink into the database at the same time?

This is the code to add the hyperlink into a record into my database:

Me.Data.SetFocus
On Error GoTo browse_stop
RunCommand acCmdInsertHyperlink
browse_stop:


If i copy paste this under the code shown above I have to browse two times.

[jhargram]

You can use this:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"database_name", "path of that file", True

I think this code will solve your prob...

[Jan22]

The files that i copy into another folder are different files (exe, doc, xls).
The code that you gave only works for excel sheets i suppose?
And
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"database_name", "path of that file", True

I can't give a name there because i'm browsing files with the filedialog. I need the hyperlink of the selected file in the filedialog.

[jhargram]

Hi Can you upload your file with all the details for ref...

[Jan22]

Okay i've pasted a table and a form in a different database so that you can get an idea of what i'm doing:

http://www.filefactory.com/file/usooke4nsv5/n/db1_zip

This is the code for the button where you can copy files to a folder in windows.

Private Sub Knop27_Click()

Pad = "C:\"
With Application.FileDialog(3)
.Title = "Bestand selecteren"
.Filters.Clear
.AllowMultiSelect = False
.InitialFileName = Pad
If .Show <> 0 Then
Bestandnaam = .SelectedItems.Item(1)
Open Bestandnaam For Input As #1
Line Input #1, Regel
MsgBox "Op de eerste regel van het bestand staat:" & vbLf & vbLf & Regel
Close #1
End If
End With
OldName = Bestandnaam
NewName = "C:\"
retval = 0
Dim objFSO As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
retval = objFSO.CopyFile(OldName, NewName, True)
Set objFSO = Nothing

Me.Data.Value = Bestandnaam & "# " & Title
End Sub

Now everything is working properly, but if i select a different category in a collumn in my form the file that i copy (NewName = "C:\") has to go to another folder
on the harddisk. (for example NewName = "C:\differentfolder\).

Could you help me out with this?

[Jan22]

Ok, i have solved my problem.. Just had to add some if commands:

Private Sub Knop27_Click()

Pad = "C:\!jajaja"
With Application.FileDialog(3)
.Title = "Bestand selecteren"
.Filters.Clear
.AllowMultiSelect = False
.InitialFileName = Pad
If .Show <> 0 Then
Bestandnaam = .SelectedItems.Item(1)
Open Bestandnaam For Input As #1
Line Input #1, Regel
MsgBox "Op de eerste regel van het bestand staat:" & vbLf & vbLf & Regel
Close #1
End If
End With
OldName = Bestandnaam
If Me![Betonverdeler Onderdelen] = "Verdelerwals" And Me![Soort Data] = "Onderdelenlijst" Then NewName = "C:\!ja\"
If Me![Betonverdeler Onderdelen] = "Verdelerwals" And Me![Soort Data] = "Parameters" Then NewName = "C:\!jajaja\"
If Me![Betonverdeler Onderdelen] = "Verdelerwals" And Me![Soort Data] = "Overige Informatie" Then NewName = "C:\!Inprise\"
If Me![Betonverdeler Onderdelen] = "Verdelerwals" And Me![Soort Data] = "Tekeningen" Then NewName = "C:\!ja\"
If Me![Betonverdeler Onderdelen] = "Verdelerwals" And Me![Soort Data] = "Onderhoudsgegevens" Then NewName = "C:\!ja\"
If Me![Betonverdeler Onderdelen] = "Verdelerwals" And Me![Soort Data] = "Keuringsgegevens" Then NewName = "\====
If Me![Betonverdeler Onderdelen] = "Verdelerwals" And Me![Soort Data] = "Software" Then NewName = "C:\!ja\"


retval = 0
Dim objFSO As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
retval = objFSO.CopyFile(OldName, NewName, True)
Set objFSO = Nothing

Me.Data.Value = NewName & "# " & Title