Text import out of line?
 

I have the following Macro....

Can you help me to eliminate the use of any cells on the
import? It comes in all chopped up but I just want it as
the text file is that I am bringing from
Filename:="G:\Gas_Control\EXCEL\DEPT\Y2K\Weaternet
Forecast\cgasdisc.txt

Any HELP would be greatly appreciated.


Sub Text()
'
' Text Macro
' Macro recorded 12/21/2004 by Kreft


Dim destCell As Range
Dim totConspWkbk As Workbook

With Workbooks("DegreeDays.xls").Worksheets("text")
.UsedRange.Clear
Set destCell = .Cells(.Rows.Count, "A").End
(xlUp).Offset(1, 0)
End With

Workbooks.OpenText
Filename:="G:\Gas_Control\EXCEL\DEPT\Y2K\Weaternet
Forecast\cgasdisc.txt", _
Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(8, 1), _
Array(30, 1), Array(41, 1), Array(67, 1), Array
(78, 1))

Set DegreeDays = ActiveWorkbook

ActiveSheet.UsedRange.Copy _
Destination:=destCell

DegreeDays.Close savechanges:=False

Application.Goto destCell, Scroll:=True

ActiveSheet.UsedRange.Columns.AutoFit

End Sub

You want the text to show up in column A--nowhere else, right?

I recorded a macro when I opened a text file. The text import wizard showed up
and I chose delimited.

But I unchecked all the options. And I chose Text as my field type (so nothing
was brought in incorrectly).

This was the line that was important:

Workbooks.OpenText Filename:="C:\My Documents\excel\book1.txt", Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, _
Space:=False, Other:=False, FieldInfo:=Array(1, 2)

===
Ps. Couldn't DestCell just be A1?

set Destcell = .range("A1")

You've cleared the cells, so your line of code will always be A2.



Krefty wrote:

I have the following Macro....

Can you help me to eliminate the use of any cells on the
import? It comes in all chopped up but I just want it as
the text file is that I am bringing from
Filename:="G:\Gas_Control\EXCEL\DEPT\Y2K\Weaternet
Forecast\cgasdisc.txt

Any HELP would be greatly appreciated.

Sub Text()
'
' Text Macro
' Macro recorded 12/21/2004 by Kreft

Dim destCell As Range
Dim totConspWkbk As Workbook

With Workbooks("DegreeDays.xls").Worksheets("text")
.UsedRange.Clear
Set destCell = .Cells(.Rows.Count, "A").End
(xlUp).Offset(1, 0)
End With

Workbooks.OpenText
Filename:="G:\Gas_Control\EXCEL\DEPT\Y2K\Weaternet
Forecast\cgasdisc.txt", _
Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(8, 1), _
Array(30, 1), Array(41, 1), Array(67, 1), Array
(78, 1))

Set DegreeDays = ActiveWorkbook

ActiveSheet.UsedRange.Copy _
Destination:=destCell

DegreeDays.Close savechanges:=False

Application.Goto destCell, Scroll:=True

ActiveSheet.UsedRange.Columns.AutoFit

End Sub

--

Dave Peterson

Thanks....

I need the .txt file to be saved in the DegreeDays.xls
workbook. When I record a macro with the wizard it opens
a new worksheet and won't save it to the specified
worksheet in this workbook? ANy additional suggestions...
-----Original Message-----
You want the text to show up in column A--nowhere else,
right?

I recorded a macro when I opened a text file. The text
import wizard showed up
and I chose delimited.

But I unchecked all the options. And I chose Text as my
field type (so nothing
was brought in incorrectly).

This was the line that was important:

Workbooks.OpenText Filename:="C:\My
Documents\excel\book1.txt", Origin:=437, _
StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False, Comma:=False, _
Space:=False, Other:=False, FieldInfo:=Array(1, 2)

===
Ps. Couldn't DestCell just be A1?

set Destcell = .range("A1")

You've cleared the cells, so your line of code will
always be A2.



Krefty wrote:

I have the following Macro....

Can you help me to eliminate the use of any cells on the
import? It comes in all chopped up but I just want it
as
the text file is that I am bringing from
Filename:="G:\Gas_Control\EXCEL\DEPT\Y2K\Weaternet
Forecast\cgasdisc.txt

Any HELP would be greatly appreciated.

Sub Text()
'
' Text Macro
' Macro recorded 12/21/2004 by Kreft

Dim destCell As Range
Dim totConspWkbk As Workbook

With Workbooks("DegreeDays.xls").Worksheets("text")
.UsedRange.Clear
Set destCell = .Cells(.Rows.Count, "A").End
(xlUp).Offset(1, 0)
End With

Workbooks.OpenText
Filename:="G:\Gas_Control\EXCEL\DEPT\Y2K\Weaternet
Forecast\cgasdisc.txt", _
Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(8, 1), _
Array(30, 1), Array(41, 1), Array(67, 1), Array
(78, 1))

Set DegreeDays = ActiveWorkbook

ActiveSheet.UsedRange.Copy _
Destination:=destCell

DegreeDays.Close savechanges:=False

Application.Goto destCell, Scroll:=True

ActiveSheet.UsedRange.Columns.AutoFit

End Sub

--

Dave Peterson
.

Thanks I just just the record macro into the desired
workbook it works just fine. Thanks very much...
-----Original Message-----
Thanks....

I need the .txt file to be saved in the DegreeDays.xls
workbook. When I record a macro with the wizard it opens
a new worksheet and won't save it to the specified
worksheet in this workbook? ANy additional suggestions...
-----Original Message-----
You want the text to show up in column A--nowhere else,
right?

I recorded a macro when I opened a text file. The text
import wizard showed up
and I chose delimited.

But I unchecked all the options. And I chose Text as my
field type (so nothing
was brought in incorrectly).

This was the line that was important:

Workbooks.OpenText Filename:="C:\My
Documents\excel\book1.txt", Origin:=437, _
StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False, Comma:=False, _
Space:=False, Other:=False, FieldInfo:=Array(1, 2)

===
Ps. Couldn't DestCell just be A1?

set Destcell = .range("A1")

You've cleared the cells, so your line of code will
always be A2.



Krefty wrote:

I have the following Macro....

Can you help me to eliminate the use of any cells on
the
import? It comes in all chopped up but I just want it
as
the text file is that I am bringing from
Filename:="G:\Gas_Control\EXCEL\DEPT\Y2K\Weaternet
Forecast\cgasdisc.txt

Any HELP would be greatly appreciated.

Sub Text()
'
' Text Macro
' Macro recorded 12/21/2004 by Kreft

Dim destCell As Range
Dim totConspWkbk As Workbook

With Workbooks("DegreeDays.xls").Worksheets("text")
.UsedRange.Clear
Set destCell = .Cells(.Rows.Count, "A").End
(xlUp).Offset(1, 0)
End With

Workbooks.OpenText
Filename:="G:\Gas_Control\EXCEL\DEPT\Y2K\Weaternet
Forecast\cgasdisc.txt", _
Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(8, 1), _
Array(30, 1), Array(41, 1), Array(67, 1), Array
(78, 1))

Set DegreeDays = ActiveWorkbook

ActiveSheet.UsedRange.Copy _
Destination:=destCell

DegreeDays.Close savechanges:=False

Application.Goto destCell, Scroll:=True

ActiveSheet.UsedRange.Columns.AutoFit

End Sub

--

Dave Peterson
.

.