Adding Field from Table in OS File Name

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I am trying to copy an OS File name using a combination of a Record in a
Table in my database plus the original file name to create a backup file. For
example the Record in the table is the Store Number which is string "049".
The original file name is PL110404.PRN. I want the new file name to be
049PL110404.PRN.

The table which stores my information will always only have one value in it.
Here is my code that I have now. All help will be appreciated!

Public Function ImportFileHandling()
On Error GoTo Err_ImportFileHandling

Dim rs As New ADODB.Recordset
Dim Store As String
Dim bFName As String
Dim sFName As String
Dim pFName As String

rs.CursorLocation = adUseClient
rs.Open "Select Store from tStoreNumber", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic
rs.MoveFirst

Store = rs.Fields("Store")

Const cFName = "PL*.PRN"

sFName = Dir(gsPath & cFName)
bFName = Dir(gsPath & "Store" & cFName)
pFName = Dir(gsPath & bFName)


FileCopy gsPath & sFName, gsPath & "PLTW.csv"
FileCopy gsPath & sFName, gsPath & "Bak\" & bFName
Kill gsPath & sFName

Set rs = Nothing

Exit_ImportFileHandling:
Exit Function

Err_ImportFileHandling:
MsgBox Err.Description
Resume Exit_ImportFileHandling

End Function
 
It would help if you said what the problem was. Does any of the code work as
you expect?

Suggestions embedded



DavidL said:
Hello,

I am trying to copy an OS File name using a combination of a Record in a
Table in my database plus the original file name to create a backup file.
For
example the Record in the table is the Store Number which is string "049".
The original file name is PL110404.PRN. I want the new file name to be
049PL110404.PRN.

The table which stores my information will always only have one value in
it.
Here is my code that I have now. All help will be appreciated!

Public Function ImportFileHandling()
On Error GoTo Err_ImportFileHandling

Dim rs As New ADODB.Recordset
Dim Store As String
Dim bFName As String
Dim sFName As String
Dim pFName As String
Click to expand...

It would help if you had some comments for what the three strings represent
rs.CursorLocation = adUseClient
rs.Open "Select Store from tStoreNumber", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic
rs.MoveFirst

Store = rs.Fields("Store")
Click to expand...

Okay, you've set the string variable Store to equal the field Store
Const cFName = "PL*.PRN"

sFName = Dir(gsPath & cFName)
Click to expand...

This returns the first file with the name "PL*.PRN"
bFName = Dir(gsPath & "Store" & cFName)
Click to expand...

This returns the first file with the name "StorePL*.PRN"
Is this correct? It seems to me that you maybe looking for the first file
with the name commencing with the value extracted from the table.
If so, the line should read: bFName = Dir(gsPath & Store & cFName)
pFName = Dir(gsPath & bFName)
Click to expand...

This line totally confuses me. bFName will be either a file name or ZLS.
Either way pFName will be identical. What purpose does this parameter hold?
FileCopy gsPath & sFName, gsPath & "PLTW.csv"
Click to expand...

This copies the file to another file in the same directory with the name
"PLTW.csv". I assume you want an identical copy. It won't change the
contents from tab delimited to comma delimited.
FileCopy gsPath & sFName, gsPath & "Bak\" & bFName
Click to expand...

This line is a problem. bFName may be a ZLS, in which case the line will
fail. Given your objective it would seem to me easier to
just use: FileCopy gsPath & sFName, gsPath & "Bak\" & Store & sFName
Kill gsPath & sFName
Set rs = Nothing
Click to expand...

I think you could use an rs.Close first.

In fact I would do the following:
---
Dim rs As New ADODB.Recordset
Dim sPrefix As String
Dim sFileName As String

'Open table and get value
rs.CursorLocation = adUseClient
rs.Open "Select Store from tStoreNumber", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic
sPrefix = rs.Fields("Store")
rs.close
set rs=nothing

'Get first file from target directory
sExistName = Dir(gsPath & "PL*.PRN")
'Copy File (Assume directory bak exists)
FileCopy gsPath & sFileName , gsPath & "Bak\" & sPrefix & sFileName
'Kill original
Kill gsPath & sFileName
 
Back
Top