Macro to Import Text
 

I am working on creating a macro that takes text data and import it into an
existing worksheet. To do this, I normally use the Import Data wizard.
Since I do this several times a day, I am trying to create a macro that will
allow me to select the text file (while I have my worksheet open) and then
select a cell placement for the data once the text files is created. I have
most of the code created, but cannot get it to pause at the file open dialog
box or the cell range box. Are there specific codes I need to use to get
this macro to do this? files into Excel
from Word several times a day.

With ActiveSheet.QueryTables.Add(Connection:="Text Files (*.txt), *.txt", _
Destination:=Range("A2"))
.Name = "subcount"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

Macro to Import Text
 

Try

Sub test()
Dim X As Variant
X = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If X = False Then Exit Sub
MsgBox "You picked " & CStr(X)
End Sub

See also
http://www.cpearson.com/excel/imptext.htm
for another solution on text import.

HTH. Best wishes Harald

"Miasha" skrev i melding
...
I am working on creating a macro that takes text data and import it into
an
existing worksheet. To do this, I normally use the Import Data wizard.
Since I do this several times a day, I am trying to create a macro that
will
allow me to select the text file (while I have my worksheet open) and then
select a cell placement for the data once the text files is created. I
have
most of the code created, but cannot get it to pause at the file open
dialog
box or the cell range box. Are there specific codes I need to use to get
this macro to do this? files into Excel
from Word several times a day.

With ActiveSheet.QueryTables.Add(Connection:="Text Files (*.txt),
*.txt", _
Destination:=Range("A2"))
.Name = "subcount"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1,
1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

Macro to Import Text
 

Thanks for yor reply. My problem is where do I insert this code into my
existing macro that I created (see below). The macro I recorded uses the
Data, Import External Data, Import Data feature. I need it to pause to
select a file (my macro includes a file already, but this will change.) Then
I need it to pause so that I can select a specific cell (the macro shows that
I selected cell "A2) to put the data into.

Sub test4()
'
' test4 Macro
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\jfitz\Desktop\subcount.txt",
Destination _
:=Range("A2"))
.Name = "subcount"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub


"Harald Staff" wrote:

Try

Sub test()
Dim X As Variant
X = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If X = False Then Exit Sub
MsgBox "You picked " & CStr(X)
End Sub

See also
http://www.cpearson.com/excel/imptext.htm
for another solution on text import.

HTH. Best wishes Harald

"Miasha" skrev i melding
...
I am working on creating a macro that takes text data and import it into
an
existing worksheet. To do this, I normally use the Import Data wizard.
Since I do this several times a day, I am trying to create a macro that
will
allow me to select the text file (while I have my worksheet open) and then
select a cell placement for the data once the text files is created. I
have
most of the code created, but cannot get it to pause at the file open
dialog
box or the cell range box. Are there specific codes I need to use to get
this macro to do this? files into Excel
from Word several times a day.

With ActiveSheet.QueryTables.Add(Connection:="Text Files (*.txt),
*.txt", _
Destination:=Range("A2"))
.Name = "subcount"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1,
1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

Macro to Import Text
 

You didn't quote where your original dialog was, so I didn't know where to
put it. But try

Dim X As Variant
X = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If X = False Then Exit Sub
With ActiveSheet.QueryTables.Add(Connection:=X, Destination:=Range("A2"))

HTH. Best wishes Harald


"Miasha" skrev i melding
...
Thanks for yor reply. My problem is where do I insert this code into my
existing macro that I created (see below). The macro I recorded uses the
Data, Import External Data, Import Data feature. I need it to pause to
select a file (my macro includes a file already, but this will change.)
Then
I need it to pause so that I can select a specific cell (the macro shows
that
I selected cell "A2) to put the data into.

Sub test4()
'
' test4 Macro
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\jfitz\Desktop\subcount.txt",
Destination _
:=Range("A2"))
.Name = "subcount"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1,
1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub


"Harald Staff" wrote:

Try

Sub test()
Dim X As Variant
X = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If X = False Then Exit Sub
MsgBox "You picked " & CStr(X)
End Sub

See also
http://www.cpearson.com/excel/imptext.htm
for another solution on text import.

HTH. Best wishes Harald

"Miasha" skrev i melding
...
I am working on creating a macro that takes text data and import it
into
an
existing worksheet. To do this, I normally use the Import Data
wizard.
Since I do this several times a day, I am trying to create a macro
that
will
allow me to select the text file (while I have my worksheet open) and
then
select a cell placement for the data once the text files is created.
I
have
most of the code created, but cannot get it to pause at the file open
dialog
box or the cell range box. Are there specific codes I need to use to
get
this macro to do this? files into Excel
from Word several times a day.

With ActiveSheet.QueryTables.Add(Connection:="Text Files (*.txt),
*.txt", _
Destination:=Range("A2"))
.Name = "subcount"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1,
1,
1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

Macro to Import Text
 

I don't use these querytables enough to be helpful, but looking at this line:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\jfitz\Desktop\subcount.txt",

would you need something like:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & X,

(Untested, uncompiled, but it matches the other string <vbg.)



Harald Staff wrote:

You didn't quote where your original dialog was, so I didn't know where to
put it. But try

Dim X As Variant
X = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If X = False Then Exit Sub
With ActiveSheet.QueryTables.Add(Connection:=X, Destination:=Range("A2"))

HTH. Best wishes Harald

"Miasha" skrev i melding
...
Thanks for yor reply. My problem is where do I insert this code into my
existing macro that I created (see below). The macro I recorded uses the
Data, Import External Data, Import Data feature. I need it to pause to
select a file (my macro includes a file already, but this will change.)
Then
I need it to pause so that I can select a specific cell (the macro shows
that
I selected cell "A2) to put the data into.

Sub test4()
'
' test4 Macro
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\jfitz\Desktop\subcount.txt",
Destination _
:=Range("A2"))
.Name = "subcount"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1,
1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub


"Harald Staff" wrote:

Try

Sub test()
Dim X As Variant
X = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If X = False Then Exit Sub
MsgBox "You picked " & CStr(X)
End Sub

See also
http://www.cpearson.com/excel/imptext.htm
for another solution on text import.

HTH. Best wishes Harald

"Miasha" skrev i melding
...
I am working on creating a macro that takes text data and import it
into
an
existing worksheet. To do this, I normally use the Import Data
wizard.
Since I do this several times a day, I am trying to create a macro
that
will
allow me to select the text file (while I have my worksheet open) and
then
select a cell placement for the data once the text files is created.
I
have
most of the code created, but cannot get it to pause at the file open
dialog
box or the cell range box. Are there specific codes I need to use to
get
this macro to do this? files into Excel
from Word several times a day.

With ActiveSheet.QueryTables.Add(Connection:="Text Files (*.txt),
*.txt", _
Destination:=Range("A2"))
.Name = "subcount"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1,
1,
1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub






--

Dave Peterson

Macro to Import Text
 

Yes, this works! Thanks so much for all of your assistance.

"Dave Peterson" wrote:

I don't use these querytables enough to be helpful, but looking at this line:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\jfitz\Desktop\subcount.txt",

would you need something like:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & X,

(Untested, uncompiled, but it matches the other string <vbg.)



Harald Staff wrote:

You didn't quote where your original dialog was, so I didn't know where to
put it. But try

Dim X As Variant
X = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If X = False Then Exit Sub
With ActiveSheet.QueryTables.Add(Connection:=X, Destination:=Range("A2"))

HTH. Best wishes Harald

"Miasha" skrev i melding
...
Thanks for yor reply. My problem is where do I insert this code into my
existing macro that I created (see below). The macro I recorded uses the
Data, Import External Data, Import Data feature. I need it to pause to
select a file (my macro includes a file already, but this will change.)
Then
I need it to pause so that I can select a specific cell (the macro shows
that
I selected cell "A2) to put the data into.

Sub test4()
'
' test4 Macro
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\jfitz\Desktop\subcount.txt",
Destination _
:=Range("A2"))
.Name = "subcount"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1,
1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub


"Harald Staff" wrote:

Try

Sub test()
Dim X As Variant
X = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If X = False Then Exit Sub
MsgBox "You picked " & CStr(X)
End Sub

See also
http://www.cpearson.com/excel/imptext.htm
for another solution on text import.

HTH. Best wishes Harald

"Miasha" skrev i melding
...
I am working on creating a macro that takes text data and import it
into
an
existing worksheet. To do this, I normally use the Import Data
wizard.
Since I do this several times a day, I am trying to create a macro
that
will
allow me to select the text file (while I have my worksheet open) and
then
select a cell placement for the data once the text files is created.
I
have
most of the code created, but cannot get it to pause at the file open
dialog
box or the cell range box. Are there specific codes I need to use to
get
this macro to do this? files into Excel
from Word several times a day.

With ActiveSheet.QueryTables.Add(Connection:="Text Files (*.txt),
*.txt", _
Destination:=Range("A2"))
.Name = "subcount"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1,
1,
1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub






--

Dave Peterson

Macro to Import Text
 

I'm sure Harald is pleased, too.

Miasha wrote:

Yes, this works! Thanks so much for all of your assistance.

"Dave Peterson" wrote:

I don't use these querytables enough to be helpful, but looking at this line:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\jfitz\Desktop\subcount.txt",

would you need something like:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & X,

(Untested, uncompiled, but it matches the other string <vbg.)



Harald Staff wrote:

You didn't quote where your original dialog was, so I didn't know where to
put it. But try

Dim X As Variant
X = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If X = False Then Exit Sub
With ActiveSheet.QueryTables.Add(Connection:=X, Destination:=Range("A2"))

HTH. Best wishes Harald

"Miasha" skrev i melding
...
Thanks for yor reply. My problem is where do I insert this code into my
existing macro that I created (see below). The macro I recorded uses the
Data, Import External Data, Import Data feature. I need it to pause to
select a file (my macro includes a file already, but this will change.)
Then
I need it to pause so that I can select a specific cell (the macro shows
that
I selected cell "A2) to put the data into.

Sub test4()
'
' test4 Macro
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\jfitz\Desktop\subcount.txt",
Destination _
:=Range("A2"))
.Name = "subcount"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1,
1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub


"Harald Staff" wrote:

Try

Sub test()
Dim X As Variant
X = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If X = False Then Exit Sub
MsgBox "You picked " & CStr(X)
End Sub

See also
http://www.cpearson.com/excel/imptext.htm
for another solution on text import.

HTH. Best wishes Harald

"Miasha" skrev i melding
...
I am working on creating a macro that takes text data and import it
into
an
existing worksheet. To do this, I normally use the Import Data
wizard.
Since I do this several times a day, I am trying to create a macro
that
will
allow me to select the text file (while I have my worksheet open) and
then
select a cell placement for the data once the text files is created.
I
have
most of the code created, but cannot get it to pause at the file open
dialog
box or the cell range box. Are there specific codes I need to use to
get
this macro to do this? files into Excel
from Word several times a day.

With ActiveSheet.QueryTables.Add(Connection:="Text Files (*.txt),
*.txt", _
Destination:=Range("A2"))
.Name = "subcount"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1,
1,
1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub






--

Dave Peterson


--

Dave Peterson

Macro to Import Text
 

Absolutely.
(And I've informed the people in my Reply Typist Department that if Dave
spots another error in a post here, I'll have no choice but to let them go)

Best wishes Harald

"Dave Peterson" skrev i melding
...
I'm sure Harald is pleased, too.

Miasha wrote:

Yes, this works! Thanks so much for all of your assistance.

Macro to Import Text
 

Just be glad that the Can(a)dian Quality Control Council isn't one of your
editors!



Harald Staff wrote:

Absolutely.
(And I've informed the people in my Reply Typist Department that if Dave
spots another error in a post here, I'll have no choice but to let them go)

Best wishes Harald

"Dave Peterson" skrev i melding
...
I'm sure Harald is pleased, too.

Miasha wrote:

Yes, this works! Thanks so much for all of your assistance.


--

Dave Peterson