I am generating data that is exported into tab separated value files with .exp extensions. I can export with an eight-line header and labels or data only with no labels. Unfortunately, a “labels only” option will not be available from the manufacturer for the foreseeable future. Huh?

The program that I am reading the data into is not smart enough to bypass the header, pick up my labels and read the data. Fortunately, Excel does a beautiful job of doing this, and I can use the import wizard to read my files and specify starting on line 9. Then, I can save the new file in my desired format to read into the next program for further processing.

Currently, I am working with only 26 such files, but this will be expending to several hundred files, which will be too time consuming to do manually.

I am experienced with Excel, but I have not used the macro feature much before. I thought this was a pretty straightforward task, so I have searched multiple sites for help. I found code that I made minor modifications to read all the files.

This works adequately. It opens a dialog box to let me specify a folder, then reads all the .exp files in the folder. This is good, but It would be nice to have the option of selecting files.

Sub OpenMMFiles()
GetDir = Application.GetOpenFilename("All Files (*.exp), *.exp")

If GetDir <> "False" Then
MyPath = CurDir & "\"
Else
MsgBox "Directory not selected"
Exit Sub
End If

Application.ScreenUpdating = False

NextFile = Dir(MyPath & "*.exp") 'gets the list of text files in the directory

While NextFile <> ""
Workbooks.OpenText Filename:= _
NextFile, Origin:=437, StartRow:=9, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True

'ActiveSheet.Copy Before:=Workbooks("Book1").Sheets(1)
'MsgBox NextFile 'uncomment this line for testing
'ActiveWorkbook.Close
NextFile = Dir()

Wend

End Sub


What I am having trouble with is finding code to Save As these open files using the same file name but capable of specifying a new format. I thought this would be the most simple case and easy to find, but most the examples I have found save multiple files into a single file or make the user specify each new file name rather than using the current name. In my own attempts, I have had trouble getting the loops or while statements to work correctly. I am just so inexperienced with VBA that I don’t really know what I am doing.

From this point, I need to save the open files as a new format using the current name, replacing the extension. Usually, I will save in the same directory from which I read, but I can foresee need to specify a different directory.

Thanks for taking time to help.