Using DB fields to copy files

[exo]

Greetings sages of Access



The bulk of my knowledge of access functions and vbscripting lies within internal procedures. My ability in regards to functions that perform external duties is quite limited.

Today my challenge is daunting for me, yet I am positive it is child's play for you guys.

I have approx 5,000 files that are in folder A. I want them copied to folder B. Folder B is different for each file.

I have a table in access that lists each file name as well as the associated folder it belongs to (it's own personal folder B).

So, for example, say I have a file named example.zip. example.zip is currently in x:\dontknow\muchabout\.

I want it to go live in x:\dontknow\muchabout\access\exmpl\

My access table has an entry for example.zip, and among the many things it tracks about this file, one of them is the name of that new destination folder (exmpl).

So, I figure I need to pass both of these fields to a function, and once I get to that point, I begin to get quite a bit more confident about the copying and such. Problem is, I have never written a function before that used a field as an argument.

Once I have those two arguments, I believe I can assign each to a string variable that contains the full path, and then use fs.filecopy. Does this sound about right?

Any assistance would be greatly appreciated! Thank you.

[Dal Jeanis]

The basic idea is to build a DOS .BAT file to do the copying for you, then execute that file. Here's the code to execute the .BAT file. You should only use this AFTER you have verified that the program is building the file correctly.

'return code from shell commands below
Dim ShellRC As Integer 

' strBatFile is a string containing the full path and file name of the bat file you created above 
'execute the DelCopy.bat file to delete old spreadsheets
ShellRC = Shell(strBatFile)
 
'Delay to allow shell to finish
Sleep 20000   'Implements a 20 second delay - vary this as needed

Here's sample code to build the BAT file. This code hasn't been tested, since I had to strip out a lot of proprietary stuff and unnecessary complications that wouldn't have been useful for showing the technique.

‘ Here’s a simple example.  I’ve hard-coded everything 
‘ Except for the loop
 
‘batch file name
Dim strBatFile As String
strBatFile = "C:\fullpath\batfolder\namehere\DelCopy.bat"
 
' Open bat file for output
Open strBatFile For Output As #1    
 
‘ tell DOS what Drive should be current
Print #1, Tab(1); "C: "
 
‘ set the current folder on the drive
Print #1, Tab(1); "CD C:\fullpath\copyfolder\namehere\"
 
 
‘*****************************************
‘ Here is where you do the actual copy work
‘ you can adapt this to build your correct
‘ source and destination files
‘*****************************************
‘ In this example, aFileName is an array of 
‘ strings loaded somewhere above, and
‘ each string in that array is one file to be 
‘ newly created from a fresh copy of Template.XLS 
‘
‘ limFN is the number of file names in aFileName
‘
‘       Dim aFileName(100) As String
‘       Dim limFN As Integer
‘  
‘*****************************************
 
 
‘ delete the old backups from the folder
Print #1, Tab(1); "del *.prev"
 
 
‘ for each file in the array, 
‘ rename the old .XLS to .PREV
‘ copy the template to .XLS
 
Dim xFN As Integer
Dim strOutline As String
Dim DQ As String
DQ = Chr(34) 'string variable representing a double quote
 
For xFN = 1 To limFN Step 1
 
‘copy .XLS to .PREV
   strOutline = "ren " & DQ & aFileName(xFN) & ".xls" & DQ & " " & DQ & aFileName(xFN) & ".prev" & DQ
   Print #1, Tab(1); strOutline
 
‘copy template.XLS to .XLS
   strOutline = "copy Template.xls " & DQ & aFileName(xFN) & ".xls" & DQ
   Print #1, Tab(1); strOutline
Next
   
Print #1, Tab(1); "exit"    
Close #1    ' Close file.

[Dal Jeanis]

You can adapt the loop in the last post to have two arrays, full source filename and full destination filename, loaded from your table. Here's a really basic sample code for loading the two arrays, once again this is aircode - fix the names to your own conventions and be skeptical .

'define and open local unique Filename recordset and load to aFileName array
Dim strSqlUnqFN As String
strSqlUnqFN = "SELECT tblYourTable.SourceName, tblYourTable.DestName " & _
     "FROM tblYourTable; " 

Dim rstUnqFN As Recordset
Set rstUnqFN = dbs.OpenRecordset(strSqlUnqFN, dbOpenDynaset)

'define aFileName arrays and variables used to load the arrays
Dim aSourceName(100) As String
Dim aDestName(100) As String
Dim limFN As Integer
Dim xFN As Integer

'get list of file names and load to aFileName arrays
limFN = 0
rstUnqFN.MoveFirst
Do Until rstUnqFN.EOF
   limFN = limFN + 1
   aSourceName(limFN) = rstUnqFN!SourceName
   aDestName(limFN) = rstUnqFN!DestName
   rstUnqFN.MoveNext
Loop
rstUnqFN.Close

[exo]

It appears your original intention with the copy area was to copy excel files to a backup, and them replace the old version with a template.

I'm not seeing where it pulls in the variable from the array however. I see no crossover between variables in the arry sub function and the copying subfunction. A bit more clarification there would be appreciated.

edit: Or, re-reading this, you are not suggesting I use both separate. You are suggesting I adapt the array into the original copy function, correct?

I think I can figure that out .

edit2:
I am getting a debug error here:
Set rstUnqFN = dbs.OpenRecordset(strSqlUnqFN, dbOpenDynaset)

The header info above it is here:

Public Function CopyArray()
Dim strSqlUnqFN As String
strSqlUnqFN = "SELECT RPGMast.File, RPGMast.Dest " & _
"FROM RPGMast; "


Dim rstUnqFN As Recordset
Set rstUnqFN = dbs.OpenRecordset(strSqlUnqFN, dbOpenDynaset)

I'm not familiar with the underscore in the select line. Is that right? And the line under it seems sort of lost too?

[Dal Jeanis]

1) You're right, my original code would have loaded the aFileName array rather than the other two arrays.

2) You can put a statement like

MsgBox strSqlUnqFN

to see how the SQL looks, but it looks okay to me.

3) The underscore is a continuation character. Make sure there is a space between the & and the _

4) Also, since your whole SQL statement is short, you could just code it all on one line.

5) Another option - make a query to make the same select, verify it works, then switch to SQL view, copy the query and paste it into your code, then add the quotes.

6) The only other possible source of problems I see is if the reference to the dbOpenDynaset constant is not set. You can set a break for that line in your code and do a mouse hover to see if Access has a value for it.

7) By the way, I forgot to give you this for the Sleep function. Put it at the top of the module.

Declare Sub Sleep Lib "Kernel32" _
   (ByVal dwMilliseconds As Long)

[exo]

Unfortunately this did not work for me, or I just couldnt figure it out. I did however find a way to do it 100% via batch files. Thanks though.

[Dal Jeanis]

Good enough. Don't fight the tech when you don't have to.