GetOpenFilename

[Craig]

1. I am able to use Getopenfilename to allow the user to select a file to open.

a.. fileToOpen = Application _
.GetOpenFilename("Text Files (*.txt), *.txt")
If fileToOpen <> False Then
MsgBox "Open " & fileToOpen
End If


2. What I would really like to do is allow the user to select the file (a TXT file), but then get the same macro to apply predefined settings to the "Text Import wizard" i.e what the data type is, what the delimiter is and what the Column data format is. I have recorded the macro to open a file with the required settings, but the recorded macro obviously specifies the file name used in the recording. The macro line also seems to be one continuous line. How do I split it into 2 lines, one for filename and one for settings?

Any help would be greatly appreciated.

Thanks in advance

Craig

 

[Bob Phillips]

Change the name in the recorded macro to fileToOpen.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

1. I am able to use Getopenfilename to allow the user to select a file
to open.

a.. fileToOpen = Application _
.GetOpenFilename("Text Files (*.txt), *.txt")
If fileToOpen <> False Then
MsgBox "Open " & fileToOpen
End If


2. What I would really like to do is allow the user to select the file (a
TXT file), but then get the same macro to apply predefined settings to the
"Text Import wizard" i.e what the data type is, what the delimiter is and
what the Column data format is. I have recorded the macro to open a file
with the required settings, but the recorded macro obviously specifies the
file name used in the recording. The macro line also seems to be one
continuous line. How do I split it into 2 lines, one for filename and one
for settings?

Any help would be greatly appreciated.

Thanks in advance

Craig

 

[Craig]

Thanks Bob, but I don't know how to do that.

My macro is as follows:

Workbooks.OpenText fileName:="C:\spirit.txt", 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,
2), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1),
Array(8, 1), Array(9, 1), _
Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14,
1), Array(15, 1), Array( _
16, 2), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1),
Array(21, 1), Array(22, 1), _
Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27,
1), Array(28, 1), Array( _
29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1),
Array(34, 1), Array(35, 1), _
Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1)),
TrailingMinusNumbers:=True
End Sub

Craig

 

[Bob Phillips]

Like this, after the GetOpenFilename

Workbooks.OpenText Filename:=filetoopen, _
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, 2), Array(3, 1), _
Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), _
Array(10, 1), Array(11, 1), Array(12,
1), _
Array(13, 1), Array(14, 1), Array(15,
1), _
Array(16, 2), Array(17, 1), Array(18,
1), _
Array(19, 1), Array(20, 1), Array(21,
1), _
Array(22, 1), Array(23, 1), Array(24,
1), _
Array(25, 1), Array(26, 1), Array(27,
1), _
Array(28, 1), Array(29, 1), Array(30,
1), _
Array(31, 1), Array(32, 1), Array(33,
1), _
Array(34, 1), Array(35, 1), Array(36,
1), _
Array(37, 1), Array(38, 1), Array(39,
1)), _
TrailingMinusNumbers:=True


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Craig]

Bob,

That's great!

Thanks.

Craig

Like this, after the GetOpenFilename

Workbooks.OpenText Filename:=filetoopen, _
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, 2), Array(3, 1),
_
Array(4, 1), Array(5, 1), Array(6, 1),
_
Array(7, 1), Array(8, 1), Array(9, 1),
_
Array(10, 1), Array(11, 1), Array(12,
1), _
Array(13, 1), Array(14, 1), Array(15,
1), _
Array(16, 2), Array(17, 1), Array(18,
1), _
Array(19, 1), Array(20, 1), Array(21,
1), _
Array(22, 1), Array(23, 1), Array(24,
1), _
Array(25, 1), Array(26, 1), Array(27,
1), _
Array(28, 1), Array(29, 1), Array(30,
1), _
Array(31, 1), Array(32, 1), Array(33,
1), _
Array(34, 1), Array(35, 1), Array(36,
1), _
Array(37, 1), Array(38, 1), Array(39,
1)), _
TrailingMinusNumbers:=True


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)