We help IT Professionals succeed at work.

.ct = .txt ?

pantoner
pantoner asked
on
I am using DoCmd.TransferText in an access form to import comma delimited files into an access database. The files come to me with extensions like .ct .cw etc. When I try to import them just as they are I get an error message that says that they are read only. What I have been doing is opening them up with notepad and then saving them as .txt files. Then I am able to import them. Is there anyway that I can get around having to do this. Either by writing something into my code, setting a property or something so that I don't have to tediously be opening them and saving them as .txt?  

Here is the code that I am using to import them.

Private Sub Command4_Click()
Dim ctdocument As String
On Error GoTo Err_Command4_Click
DlgCommon.FileName = ""
DlgCommon.Filter = "special cs (*.txt)|*.txt"
DlgCommon.FilterIndex = 1
DlgCommon.InitDir = CurDir
DlgCommon.ShowOpen
If DlgCommon.FileName = "" Then
MsgBox " choose a file name"
Else
ctdocument = DlgCommon.FileName
DoCmd.TransferText acImportDelim, "", "special", ctdocument, True, ""
End If


Exit_Command4_Click:
Exit Sub
Comment
Watch Question

Commented:
Wht not just use the DlgCommon.Filter  to show all files,then use the Name command to rename the .ct or .cw  file as a .txt file.

Commented:
If the file is read-only you will have to change the attributes

strFileName = "C:\Somefile.txt"
SetAttr strFileName, GetAttr(strFileName) And (Not vbReadOnly)

Author

Commented:
How would that fit in the code that I am using.

Commented:
you can try this

Private Sub Command4_Click()
Dim ctdocument As String
On Error GoTo Err_Command4_Click
DlgCommon.FileName = ""
DlgCommon.Filter = "special cs (*.*)|*.*"
DlgCommon.FilterIndex = 1
DlgCommon.InitDir = CurDir
DlgCommon.ShowOpen
If DlgCommon.FileName = "" Then
MsgBox " choose a file name"
Else
ctdocument = DlgCommon.FileName
SetAttr ctdocument, GetAttr(ctdocument) And (Not vbReadOnly)
DoCmd.TransferText acImportDelim, "", "special", ctdocument, True, ""
End If


Err_Command4_Click:
Exit Sub

End Sub

Author

Commented:
When I select the file, the dialog box closes and nothing else happens. There is no error message like before. It just doesn't put the file into the table. As if it is just rejecting the vbReadOnly but not changing it to something it can use.

Author

Commented:
When I select the file, the dialog box closes and nothing else happens. There is no error message like before. It just doesn't put the file into the table. As if it is just rejecting the vbReadOnly but not changing it to something it can use.
Commented:
ok,try this to rename the file as a text file before changing attributes

Private Sub Command4_Click()
Dim ctdocument As String
Dim newctdocument As String
Dim pos As String
On Error GoTo Err_Command4_Click
DlgCommon.FileName = ""
DlgCommon.Filter = "special cs (*.*)|*.*"
DlgCommon.FilterIndex = 1
DlgCommon.InitDir = CurDir
DlgCommon.ShowOpen
If DlgCommon.FileName = "" Then
MsgBox " choose a file name"
Else
ctdocument = DlgCommon.FileName
pos = InStr(ctdocument, ".")
newctdocument = Left(ctdocument, pos) + "txt"
Name ctdocument As newctdocument
ctdocument = newctdocument
SetAttr ctdocument, GetAttr(ctdocument) And (Not vbReadOnly)
DoCmd.TransferText acImportDelim, "", "special", ctdocument, True, ""
End If

Exit_Command4_Click:   ' note : shouldn't this be Err_Command4_Click ?
Exit Sub                              

Author

Commented:
Your right the exit_command should be err_command.

Thank you very much the code works!!