File Extension Problem 1

[BAWC01]

Hi

I have a file which is a comma delimited file

but the file I recieve has the extension .CGL

I'm importing it using the Import Wizard using the docmd. command which works find if I rename it to .TXT

Is there a way I can keep the extension the same .CGL but still use the import wizard.

or a way I can rename the files to txt using VBA

Thanks

Phil

 

[Remou]

To rename:
[tt]Name "C:\Docs\File.CGL" As "C:\Docs\File.txt"[/tt]

 

[wemeier]

You should be able to import a file ending in ANY extension as long as you can identify its type. The file may not show up in the Import dialog box when you choose Text as the type, but you can still enter the full name of the file in the File name textbox and it will import.

Cruising the Information Superhighway at the speed of light
[sub] (your mileage may vary)[/sub]

 

[BAWC01]

Wemeier

Thats what I thought, but when you run the wizard against the file, the wizrd come up with a dialog and says you can only import text file of the following extensions *.txt, *.csv etc

Any other ideas

Phil

 

[Remou]

This article:
ACC2000: How to Import a Text File That Has an Extension That Access Does Not Recognize

http://support.microsoft.com/?kbid=306144

suggests renaming (Access 2000), but suggest a more complicated method than Name (above).

 

[BAWC01]

Remou

Thanks for that link has helped

Cheers

 

[BAWC01]

Hi

I have the following code

Sub DataImport()
    Dim objFileSystem
    Dim objFile
    Dim strFileCopy As String
    Dim strFileOrig As String
    Dim intExtPosition As Integer
    Dim fs, ifo, fi, fc, s, fn, txt

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set ifo = fs.GetFolder(GlobalINPUTFile)
    Set ofo = fs.GetFolder(GlobalOUTPUTLoc)

Set fc = ifo.Files
   TxtLines = 0
   For Each fi In fc
     intExtPosition = InStr(fi.Name, ".")
     GTotal = GTotal + 1
     LoadFiles = LoadFiles + 1
     Filecount = Filecount + 1
   Next
  
 For Each fi In fc
     intExtPosition = InStr(fi.Name, ".")
    If Right(fi.Name, 3) = "CGL" Then
      If intExtPosition > 0 Then
        strFileOrig = ifo & "\" & fi.Name
        strFileCopy = ifo & "\" & Left(fi.Name, intExtPosition - 1) & ".txt"
      Else
        strFileCopy = ifo & "\" & fi.Name & ".txt"
      End If
      fi.Copy strFileCopy, True
    End If
Next
End Sub

Which allows me to make a copy of a file but change the extension to .txt.

How can I modify it so that it renames it.

Thanks

Phil

 

[PHV]

Replace this:
fi.Copy strFileCopy, True
with this:
fi.Move strFileCopy

I'd replace this:
intExtPosition = InStr(fi.Name, ".")
with this:
intExtPosition = InStrRev(fi.Name, ".")

And this:
If Right(fi.Name, 3) = "CGL" Then
With this:
If UCase(Right(fi.Name, 3)) = "CGL" Then

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