csv report renaming before import

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

fishy - 17 Jul 2007 15:24 GMT

I have 12 daily csv reports that have a autonumber added to a standard naming
convention.

Is there anyway of renaming these files from a database so that my normal
import spec works without having to manually amend the file names.

Reply to this Message

Ken Snell (MVP) - 17 Jul 2007 22:45 GMT

Import specifications do not rely on the name of the file being imported. I
assume that you're looking for wanting to have the right filename in the
FileName argument of TransferText?

Check out the Name statement in VBA:

Name Path\Folder\OldFilename.csv As Path\Folder\NewFilename.csv

Signature

Ken Snell
<MS ACCESS MVP>

>I have 12 daily csv reports that have a autonumber added to a standard
>naming
> convention.
>
> Is there anyway of renaming these files from a database so that my normal
> import spec works without having to manually amend the file names.

Reply to this Message

fishy - 18 Jul 2007 19:02 GMT

I tried that, is there any declarations etc I have to do to get this to work

> Import specifications do not rely on the name of the file being imported. I
> assume that you're looking for wanting to have the right filename in the
[quoted text clipped - 9 lines]
> > Is there anyway of renaming these files from a database so that my normal
> > import spec works without having to manually amend the file names.

Reply to this Message

Ken Snell (MVP) - 19 Jul 2007 00:03 GMT

Post the code that you tried so that we can help debug ... and give details
about the initial file names of the .csv files.

Signature

Ken Snell
<MS ACCESS MVP>

>I tried that, is there any declarations etc I have to do to get this to
>work
[quoted text clipped - 14 lines]
>> > normal
>> > import spec works without having to manually amend the file names.

Reply to this Message

pietlinden@hotmail.com - 18 Jul 2007 23:47 GMT

> I have 12 daily csv reports that have a autonumber added to a standard naming
> convention.
>
> Is there anyway of renaming these files from a database so that my normal
> import spec works without having to manually amend the file names.

STOLEN CODE... this belongs to Doug Steele...

Untested air-code. Note that I haven't bothered putting in error
handling if
the .txt file already exists in the folder.

Dim strFile As String
Dim strNewName As String
Dim strPath As String

strPath = "C:\Documents and Settings\SDF\"
strFile = Dir$(strPath & "*.sdf")
Do While Len(strFile) > 0

'-- CREATE THE NEW FILE NAME FROM THE OLD ONE...
strNewName = Left(strFile, Len(strFile) - 4) & ".txt"

'---THIS LINE RENAMES THE OLD FILE...
Name strPath & strFile As strPath & strNewName

'---IMPORT THE FILE
DoCmd.TransferText acImportFixed, "importspec", "SDF", _
strPath & strNewName
strFile = Dir$()
Loop

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

Reply to this Message