Making Folders and Copying Files 2

[pdldavis]

Hi, I can create folders using a recordset and capture files I need using a recordset (I think) but I do not know how to copy files to those folders. I am hoping someone can help me with this. The area I know I am having problems with is in green.

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

'Create File Names

Dim fso, fs As Scripting.FileSystemObject
Set fso = New FileSystemObject
Set fs = New FileSystemObject

Dim rst As DAO.Recordset 'Recordset for File Folders

Dim fRst As DAO.Recordset 'Recordset for Files

Dim fld As DAO.Field 'File Folder Field

Dim fFld As DAO.Field 'File Field

'Create File Folders

Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT Main.[File No] from Main order by main.[File No]", dbOpenDynaset)

Set fld = rst("[File No]")

Set fRst = CurrentDb.OpenRecordset("SELECT Main.[File No], Main.FileLink FROM Main", dbOpenDynaset)

Set fFld = rst("[FileLink]")

Set fFile = rst("[File No]")

rst.MoveFirst


Do Until rst.EOF

MkDir ("C:\Temp\" & fld) 'Create the Direcotry

'Here I want to copy files where ffld = fFile to the current folder in the recordset.

fs.CopyFile ffld Where fFile = fld, "C:\Temp\" & fld


rst.MoveNext

Loop
rst.CLOSE
Set rst = Nothing

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

Recordsets are not my long suit, so this may not be the right approach. Any help would be appreciated - a back burner item brought to the forefront again.

Thanks

 

[DoubleD]

rst.MoveFirst

Do Until rst.EOF
   MkDir ("C:\Temp\" & fld) 'Create the Directory
   Set fFld = rst("[FileLink]")

  Set fRst = CurrentDb.OpenRecordset("SELECT Main.[File No], Main.FileLink FROM Main WHERE [File No] = " & rst("[File No]"), dbOpenDynaset)
  Do While Not fRst.EOF
    fs.CopyFile  fFld, "C:\Temp\" & fld
    fRst.MoveNext
  Loop
  fRst.Close
            
  rst.MoveNext

Loop

This code grabs only the files that match your criteria, then copies those files into your folder. Your logic is a little hard to follow, and I think your referencing the wrong recordset in your Set fFile line, but I could be wrong.

I am what I am based on the decisions I have made.

DoubleD

 

[pdldavis]

Hi, thanks for the help. I am getting a 'Data type mismatch' in:

Set fRst = CurrentDb.OpenRecordset("SELECT Main.[File No], Main.FileLink FROM Main WHERE [File No] = " & rst("[File No]"), dbOpenDynaset)

[File No] is text. and when I mouse over rst("[File No]") it shows the correct folder, in this case "096". I copied the Select statement to a query and used "096" as the critera and it worked fine.

Any thoughts as to where the mismatch would be?

Thanks, Dan

 

[PHV]

Set fRst = CurrentDb.OpenRecordset("SELECT Main.[File No], Main.FileLink FROM Main WHERE [File No]='" & rst("[File No]") & "'", dbOpenDynaset)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DoubleD]

Yep, PHV has your answer for a text field.

I am what I am based on the decisions I have made.

DoubleD

 

[pdldavis]

Hi, well I seem to have made it past the set fRst and now I am running into Set fFld = rst("[FileLink]") - item not found in this collection. I moved the statement below the Set fRst statement thinking that's where it should be and still the same error. FileLink is text too, the full path to the file. Do I need extra quotes around this too?

Set fRst = CurrentDb.OpenRecordset("SELECT Main.[File No], Main.FileLink FROM Main WHERE [File No]='" & rst("[File No]") & "'", dbOpenDynaset)


Set fFld = rst("[FileLink]")
Do While Not fRst.EOF

fs.CopyFile fFld, "C:\Temp\" & fld

Thanks, Dan

 

[DoubleD]

FileLink is coming from fRst, not rst. This is the problem with using object and variable names that aren't meaningful.
I would rewrite your code as follows:

Private Sub cmdCreateFiles_Click()
On Error GoTo Err_cmdCreateFiles_Click

'Create File Names

Dim fs As New Scripting.FileSystemObject

Dim rsFolders As DAO.Recordset 'Recordset for File Folders
Dim rsFiles As DAO.Recordset 'Recordset for Files

Dim fldFolderNum As DAO.Field 'File Folder Field
Dim fldFileNum As DAO.Field 'File Field
Dim fldFileLink As DAO.Field 'File Field

'Create File Folders

Set rsFolders = CurrentDb.OpenRecordset("SELECT DISTINCT Main.[File No] from Main order by main.[File No]", dbOpenDynaset)

Set fldFolderNum = rsFolders("[File No]")

Set rsFiles = CurrentDb.OpenRecordset("SELECT Main.[File No], Main.FileLink FROM Main", dbOpenDynaset)

Do Until rsFolders.EOF
  MkDir ("C:\Temp\" & strFolderNum) 'Create the Direcotry
  Set rsFiles = CurrentDb.OpenRecordset("SELECT Main.[File No], Main.FileLink FROM Main WHERE [File No] = '" & fldFolderNum & "'", dbOpenDynaset)
  Set fldFileNum = rsFiles("[File No]")
  Set fldFileLink = rsFiles("[FileLink]")

  Do While Not rsFiles.EOF
    fs.CopyFile  fldFileLink, "C:\Temp\" & fldFileNum 
    rsFiles.MoveNext
  Loop
  rsFiles.Close
  rsFolders.MoveNext

Loop
rsFolders.Close

Exit_cmdCreateFiles_Click:
  Set rsFolders = Nothing
  Set rsFiles = Nothing
  Exit Sub

Err_cmdCreateFiles_Click:
  MsgBox Err.Description
  Resume Exit_cmdCreateFiles_Click
    
End Sub

I am what I am based on the decisions I have made.

DoubleD

 

[PHV]

Replace this:
Set fFld = rst("[FileLink]")
By this:
fFld = rst("FileLink")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pdldavis]

Thanks guys for all the help and thanks for a good example of how to structure and name these things. I do appreciate it.