I need to open an excel file that is not in an excel format and requires "fixed width" parameters to open correctly. Initially I had this written to open the specific file just for myself, but am looking to modify this so that anyone can use for any file location.
Original Code:
Workbooks.OpenText Filename:= _
"C:\Users\a614889\Documents\One time Reports\xnet", Origin:=437, StartRow:= _
1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(5, 9), Array(7, 1 _
), Array(13, 9), Array(15, 1), Array(18, 9), Array(19, 1), Array(25, 9), Array(32, 1), Array _
(63, 1), Array(69, 9), Array(72, 1), Array(73, 9), Array(76, 1), Array(82, 9), Array(101, 1) _
), TrailingMinusNumbers:=True
Now, I am adding this to prompt the user to select the location they have their file saved
Dim fNameAndPath As Variant
fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLS), *.XLS", Title:="Select File To Be Opened")
If fNameAndPath = False Then Exit Sub
Workbooks.Open Filename:=fNameAndPath
Where I am struggling, is what the syntax is to open the file with the fixed with settings. I tried this and get a compile error on StartRow. I know sytnax can get iffy when broken into lines and tried moving some of it up a line and breaking in a different spot, but always get the compile error.
Dim fNameAndPath As Variant
fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLS), *.XLS", Title:="Select File To Be Opened")
If fNameAndPath = False Then Exit Sub
Workbooks.Open Filename:=fNameAndPath, Origin:=437, StartRow:= _
1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(5, 9), Array(7, 1 _
), Array(13, 9), Array(15, 1), Array(18, 9), Array(19, 1), Array(25, 9), Array(32, 1), Array _
(63, 1), Array(69, 9), Array(72, 1), Array(73, 9), Array(76, 1), Array(82, 9), Array(101, 1) _
), TrailingMinusNumbers:=True
Bookmarks