Copying/Renaming files based on data in a table

  • Thread starter Thread starter sbradley0
  • Start date Start date
S

sbradley0

Basically, I'm trying to do what it says in the subject. I have a
table (tblReportsMe) with three fields:

woindex (unique)
wofile (current path to a specific file, eg. c:\00916678423 ... file
has no extention)
worepname (desired name of the file)

I'm trying to copy the files and rename them from a form, using a the
following sub:

Private Sub btnRenameFiles_Click()

Dim rstAny As DAO.Recordset
Dim dbAny As DAO.Database
Dim strFrom As String
Dim strTo As String

Set dbAny = CurrentDb()
Set rstAny = dbAny.OpenRecordset("SELECT DISTINCT woindex, worepname,
wofile FROM tblReportsMe")

While Not rstAny.EOF
strFrom = wofile
strTo = "C:\Documents and Settings\All Users\Documents\CFx\Month End
Reports\" & worepname & ".txt"
FileCopy strFrom, strTo
 
Sorry, tabbed accidentally, full code:

Private Sub btnRenameFiles_Click()

Dim rstAny As DAO.Recordset
Dim dbAny As DAO.Database
Dim strFrom As String
Dim strTo As String

 Set dbAny = CurrentDb()
 Set rstAny = dbAny.OpenRecordset("SELECT DISTINCT woindex, worepname,
wofile FROM tblReportsMe")

 While Not rstAny.EOF
  strFrom = wofile
  strTo = "C:\Documents and Settings\All Users\Documents\CFx\Month End
Reports\" & worepname & ".txt"
FileCopy strFrom, strTo
rstAny.MoveNext
Wend

End Sub

The problem is that it keeps saying that wofile and worepname are
"empty". Any help?

Thanks!
 
Sorry, tabbed accidentally, full code:

Private Sub btnRenameFiles_Click()

Dim rstAny As DAO.Recordset
Dim dbAny As DAO.Database
Dim strFrom As String
Dim strTo As String

Set dbAny = CurrentDb()
Set rstAny = dbAny.OpenRecordset("SELECT DISTINCT woindex, worepname,
wofile FROM tblReportsMe")

While Not rstAny.EOF
strFrom = wofile
strTo = "C:\Documents and Settings\All Users\Documents\CFx\Month End
Reports\" & worepname & ".txt"
FileCopy strFrom, strTo
rstAny.MoveNext
Wend

End Sub

The problem is that it keeps saying that wofile and worepname are
"empty". Any help?

Thanks!

You forgot to prefix them with the recordset variable. They should look
like:

strFrom = rstAny!wofile

and:

& rstAny!worepname &
 
Sorry, tabbed accidentally, full code:

Private Sub btnRenameFiles_Click()

Dim rstAny As DAO.Recordset
Dim dbAny As DAO.Database
Dim strFrom As String
Dim strTo As String

Set dbAny = CurrentDb()
Set rstAny = dbAny.OpenRecordset("SELECT DISTINCT woindex, worepname,
wofile FROM tblReportsMe")

While Not rstAny.EOF
strFrom = wofile
strTo = "C:\Documents and Settings\All Users\Documents\CFx\Month End
Reports\" & worepname & ".txt"
  FileCopy strFrom, strTo
  rstAny.MoveNext
 Wend

End Sub

The problem is that it keeps saying that wofile and worepname are
"empty".  Any help?

Thanks!

You forgot to prefix them with the recordset variable. They should look
like:

strFrom = rstAny!wofile

and:

& rstAny!worepname &
Click to expand...

Great, thanks!
 
Back
Top