Using Left([FieldName],3) to look at the name of a file

GODZILLA · Apr 13, 2010

Hello,

Im looking to import a bunch of files to a table all at once using vba.

I want vba to be able to look at the first 3 letters of the file name to identify it. Then take the next 3 and import all the data to a table.

i.e. file name

999APT130410.txt

I want it to look at [999] which is a code linked to a table
import all the data to a table and add [APT] to the table as an identifyer.

Im sure there is a better way of doing this.

Can anyone help?

Thanks

gemma-the-husky · Apr 13, 2010

if the file names ALWAYS follow that naming convention, then its fine.

you may need these VBA commands

dir - enables you to loop round all the files in the folder

left - enables you to take the first 3 chars from the filename

mid - enables you to select a substring of 3 chars in the middle of the file name

docmd.transferttext - enables you to import a csv
docmd.transferspreadsheet - enables you to import a spreadsheet (note that the former option is better and more controllable)

name - enables you to rename a file after it has been processed

these things should enable you to automate the process.

GODZILLA · Apr 21, 2010

Can you explain a little more about the Dir property.

Or if you know of any good websites could you please link them.

Update on what im doing:

I have a folder with a lot of txt files and i need to import them all in to table using parts of the file name as an identifyer.

Galaxiom · Apr 21, 2010

Dir function

http://office.microsoft.com/en-us/access/HA012288241033.aspx

A simple example of its use:

Code:

Sub GetFileName()
 
Dim FolderName As String
Dim Filename As String
 
   FolderName = "C:\"
   Filename = Dir(FolderName)
 
   Do While Filename <> ""
      Debug.Print Filename ' just shows name in immediate window
      ' Do your stuff here with FileName variable
      Filename = Dir
   Loop
 
End Sub

gemma-the-husky · Apr 21, 2010

basically, calling dir the first time finds any file matching the selected search string

calling dir further times, finds other files matching, until all files have been examined

so you can do this sort of thing

Code:

dim filename as string
filename = dir(somesearchstring)

while filename<>""
  if filename matches some test then
    import the file (docmd.transfertext), or something else
  end if
  name filename newname  (rename the file to another name -which can be in another folder - if you need to do this)
  filename = dir (get the next file)
wend

Using Left([FieldName],3) to look at the name of a file

GODZILLA

Registered User.

gemma-the-husky

Super Moderator

GODZILLA

Registered User.

Galaxiom

Super Moderator

gemma-the-husky

Super Moderator

Similar threads

Users who are viewing this thread