Public Const MY_FILE_PATH As String = "C:\myWorkbook.xls"
Public varFileName As Variant
'----------------------------------------
Sub OpenTextFileAndSave()
Call MyGetOpenFileName
Call MyOpenTextFile
Call MySaveWorkbook
End Sub
'----------------------------------------
Private Sub MyGetOpenFileName()
varFileName = Application.GetOpenFilename(Title:="Select a File to Import")
'All code ends if user clicks cancel or close
If varFileName = False Or varFileName = "" Then End
End Sub
'----------------------------------------
Private Sub MySaveWorkbook()
ActiveWorkbook.SaveAs Filename:=MY_FILE_PATH, FileFormat:= _
xlWorkbookNormal
End Sub
'----------------------------------------
Private Sub MyOpenTextFile()
'I recorded this code with the macro recorder by opening a text file with excel.
'you may want to do this and use your code in case any parameters are different --
'If you do, the filename may be replaced with the variable from the routine above
Workbooks.OpenText Filename:= _
varFileName, Origin:= _
437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
Array(10, 1)), TrailingMinusNumbers:=True
End Sub