Import a file with no extension

[sb4216]

I am trying to write a macro that will import a file. The file is created from a mainframe export and the file does not have a file extension. I am using Access 2007 and it will not let me import from a file that does not have one of the standard extensions and gives an error of such when I try. I am trying to create a process that does not require manually having to change the file in any way prior to importing into my Access table. Is there a work-around that will allow me to import this file on a monthly basis without having to manually add a file extension to the file first?

Thanks
Sherri

 

[boblarson]

For this you will need to use some VBA to add the file extension. Are you okay with adding the file extension to the original file or would you like to make a copy and then add the extension?

To add the extension you can use this function (pasted into a STANDARD MODULE - not a form, report or class module and the module named something like basTextOps)

Function AddFileExt(strOrigFileName As String, strExtToAdd As String)
   If strOrigFileName = vbNullString OR strExtToAdd = vbNullString Then
      Msgbox "You need to supply values for each argument", vbExclamation, "Parameter Input Error"
      Exit Function
   End If
 
   If Dir(strOrigFileName) <> vbNullString Then
        Name strOrigFileName strOrigFileName & strExtToAdd
   Else
        Msgbox "File name passed doesn't exist.", vbExclamation, "Error"
   End If
End Function

Then you can just call this function in your macro and pass it the file path and name (needs to have both the path and name in the first argument) and then the extension.

 

[sb4216]

Thank you Bob. That lead me in the right direction, however Access still couldn't open the file even though it now had the appropriate extension. I did some additional research and found that it is because changing the extension and saving as an excel workbook aren't exactly the same thing. So I had to open the file and then save as and excel workbook. Here is the code I used based on what you had posted:

Function AddFileExt(strOrigFileName As String)
Dim xlApp As New Excel.Application
Dim ws As Excel.Worksheet
If strOrigFileName = vbNullString Then
MsgBox "You need to supply values for each argument", vbExclamation, "Parameter Input Error"
Exit Function
End If

If Dir(strOrigFileName) <> vbNullString Then

xlApp.Workbooks.Open (strOrigFileName)
Set ws = ActiveSheet
ws.Rows(1).Delete
xlApp.ActiveWorkbook.SaveAs FileName:=strOrigFileName, FileFormat:=51
xlApp.Application.Quit


Else
MsgBox "File name passed doesn't exist.", vbExclamation, "Error"
End If

End Function


Thanks again for your help!
Sherri

 

[boblarson]

Guess I should have asked what KIND of file you were trying to import instead of assuming a text file.

 

[sb4216]

Not a problem, your answer led me down the right path. I didn't really have a path before you answered.

Thanks again.
Sherri