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

Change the name in the recorded macro to fileToOpen.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Craig" wrote in message
...
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

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" wrote in message
...
Change the name in the recorded macro to fileToOpen.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Craig" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
Change the name in the recorded macro to fileToOpen.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Craig" wrote in message
...
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,

That's great!

Thanks.

Craig

"Bob Phillips" wrote in message
...
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" wrote in message
...
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" wrote in message
...
Change the name in the recorded macro to fileToOpen.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Craig" wrote in message
...
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