Hi all,
I am trying to write a code that will help me convert an .xls extension file to the correct .xls File Format 56. The file that needs to be converted will be user selected. Currently, the files are in txt format but saved with .xls extension. So I am converting the file into text and then trying to save the file as .xls. The file name of the final excel file will be the same as the converted text file.
Please can you help in understanding how to proceed in saving the converted text file with the name of the text file as xls file format 56.
Thanks.
I am trying to write a code that will help me convert an .xls extension file to the correct .xls File Format 56. The file that needs to be converted will be user selected. Currently, the files are in txt format but saved with .xls extension. So I am converting the file into text and then trying to save the file as .xls. The file name of the final excel file will be the same as the converted text file.
Please can you help in understanding how to proceed in saving the converted text file with the name of the text file as xls file format 56.
Rich (BB code):
Sub folderaccess()
Dim fd As Office.FileDialog
Dim strFile As String
Dim BaseName As String
Dim File_Path As String
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.Filters.Clear
.Filters.Add "Excel Files", "*.xls?", 1
.Title = "Choose an Excel 97-2003 file"
.AllowMultiSelect = False
.InitialFileName = "C:\VBA Folder"
If .Show = True Then
strFile = .SelectedItems(1)
End If
ThisWorkbook.Names("File_Path1").RefersToRange.Value = strFile
End With
Base_Name = Mid(strFile, InStrRev(strFile, "\") + 1, InStrRev(strFile, ".") - InStrRev(strFile, "\") - 1)
ThisWorkbook.Names("Base_Name").RefersToRange.Value = Base_Name
Name strFile As (Base_Name & ".txt")
End Sub
Thanks.