UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Scripts    
 
   
salimali100
post May 26 2015, 09:19 AM
Post#1



Posts: 116
Joined: 4-April 09



Hello,

I am looking for a script that will help me to copy thousands of files from an USB drive to my PC based on id number.

For example, a pdf file has id 100 on an usb stick and there is folder on my computer called 100, the script should copy the file from usb drive to PC drive.

Any idea would be greatly appreciated.

Many thanks in advanced.
Salim
Go to the top of the page
 
cheekybuddha
post May 26 2015, 09:30 AM
Post#2


UtterAccess VIP
Posts: 9,282
Joined: 6-December 03
From: Telegraph Hill


Hi Salim,

Are all the files on the stick named uniformly?

eg

>> a pdf file has id 100 <<

100.pdf

?

Will there be a unique folder for each file?

d
Go to the top of the page
 
salimali100
post May 26 2015, 09:37 AM
Post#3



Posts: 116
Joined: 4-April 09



Hi,
Are all the files on the stick named uniformly? Yes
Will there be a unique folder for each file? Yes
Many thanks,
S
Go to the top of the page
 
cheekybuddha
post May 26 2015, 10:42 AM
Post#4


UtterAccess VIP
Posts: 9,282
Joined: 6-December 03
From: Telegraph Hill


The following should give you a start:
CODE
  Dim fso, stick, pc, fl
  Dim stickFldr, pcFldr
  Dim fileName

' Use FileSystemObject
  Set fso = CreateObject("Scripting.FileSystemObject")

' Folder where files are on stick
  stickFldr = "E:\"
' Destination folder for files/folders on pc
  pcFldr = "C:\some\folder\"
  
' Open the folder on the stick
  Set stick = fso.GetFolder(stickFldr)
' Loop through all the files  
  For Each fl In stick.Files
'   Extract the file name to the left of the extension dot  
    fileName = Left(fl.Name, InstrRev(fl.Name, ".") - 1)
'   Check there is a folder of that name in the destination directory - if not, create it    
    If Not fso.FolderExists(pcFldr & fileName) Then
      fso.CreateFolder (pcFldr & fileName)
    End If
'   Copy the file to the detination folder with the same name using the same file name too    
    fl.Copy (pcFldr & fileName & "\" & fl.Name)
  Next

Copy the code into an empty text file and save it as something like fileCopy.vbs on the desktop. Double click and away you go!

It has no handling if your file names / folders contain spaces, nor any handling if the files already exist. It does create a folder if it doesn't exist.

You must put the drive letter and folder to the files in the stickFldr variable, and also the base folder where you want to store all the files in the pcFldr variable. Make sure pcFldr terminates with "\".

hth,

d
Go to the top of the page
 
salimali100
post May 27 2015, 05:31 AM
Post#5



Posts: 116
Joined: 4-April 09



Thank you very much indeed Cheekybuddha.
Your code does work but it creates new folders with same name as the file name.
Assume I have the folders already and I just want the files to be transferred. Please see snif attached.
Many thanks in advanced.
Attached File(s)
Attached File  Capture1.JPG ( 44.08K )Number of downloads: 5
 
Go to the top of the page
 
cheekybuddha
post May 27 2015, 07:46 AM
Post#6


UtterAccess VIP
Posts: 9,282
Joined: 6-December 03
From: Telegraph Hill


Where is the folder which contains the folders you have already created? In your screenshot it looks like the desktop.

You should use that folder as the path in the pcFldr variable. The script should then check whether a folder the name of the file already exists within the containing folder, and create it if not.

Or am I missing the point? Do you just want all the files copied in to one single folder?

Please clarify.

d
Go to the top of the page
 
salimali100
post May 27 2015, 10:59 AM
Post#7



Posts: 116
Joined: 4-April 09



Please ignore my earlier post, your code works perfectly. Many thanks.

Would you able to help me on a separate issue please?
I have an access table contains customer_id and supplier_id, i also have some folders in the shared drive same named as customer_ids, if customer_id and supplier_id match script should transfer files from USB to customer_id folder.
I hope it makes sense.

Many many thanks,
Salim
Go to the top of the page
 
cheekybuddha
post May 27 2015, 01:24 PM
Post#8


UtterAccess VIP
Posts: 9,282
Joined: 6-December 03
From: Telegraph Hill


Hi Salim,

OK, if you're dealing with Access tables then you may as well move your code in to Access rather than use an external vbScript.

Are you saying that the file names could be either customer or supplier id's? If so, you could adapt the code in to a function like:
CODE
Function MoveFiles() As Boolean

  Dim fso As Object, stick As Object, fl As Object
  Dim stickFldr As String, pcFldr As String
  Dim fileName AS String
  Dim strSQL AS String

' Use FileSystemObject
  Set fso = CreateObject("Scripting.FileSystemObject")

' Folder where files are on stick
  stickFldr = "E:\"
' Destination folder for files/folders on pc
  pcFldr = "C:\some\folder\"
  
' Open the folder on the stick
  Set stick = fso.GetFolder(stickFldr)
' Loop through all the files  
  For Each fl In stick.Files
'   Extract the file name to the left of the extension dot  
    fileName = Left(fl.Name, InstrRev(fl.Name, ".") - 1)
'   Let's see whether the filename is the id of a customer or supplier
    If DCount("*", "YourTableName", "customer_id = '" & fileName & "' OR supplier_id = '" & fileName & "'") > 0 Then
'     Check there is a folder of that name in the destination directory - if not, create it    
      If Not fso.FolderExists(pcFldr & fileName) Then
        fso.CreateFolder (pcFldr & fileName)
      End If
'     Copy the file to the detination folder with the same name using the same file name too    
      fl.Copy (pcFldr & fileName & "\" & fl.Name)
    End If
  Next
  Set fl = Nothing
  Set stick = Nothing
  Set fso = Nothing
  MoveFiles = (err = 0)

End Function

The DCount() function checks whether your table (make sure you change it to the correct table name!) contains any records where the customer_id or supplier_id is the same as the name of the file being looked at. If so, it will do as before: check whether a folder exists, create one if necessary, and then copy the file.

Paste the code in to a standard module in Access. Name the module differently from the function (eg 'modMoveFiles').

Then you call call the function from the Immediate Window (Ctrl+G) by using:

?MoveFiles


or you could call it from a button on a form (or from any code), eg:
CODE
Private Sub cmd_MoveFiles_Click()

   Call MoveFiles

End Sub


hth,

d
Go to the top of the page
 
salimali100
post Aug 20 2015, 06:20 AM
Post#9



Posts: 116
Joined: 4-April 09



Sorry for the very late reply.
You're an absolute legend. Your code helped me so much.
Many thanks again.
Salim.
Go to the top of the page
 
cheekybuddha
post Aug 20 2015, 12:30 PM
Post#10


UtterAccess VIP
Posts: 9,282
Joined: 6-December 03
From: Telegraph Hill


No worries, Salim!

yw.gif

I'm glad it helped.

Good luck with your project! thumbup.gif

d
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th December 2017 - 05:16 PM