Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 . |
#4
|
|||
|
|||
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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import comma delimited text | Excel Discussion (Misc queries) | |||
Any way to get around the 240-character line limit on text output? | Excel Discussion (Misc queries) | |||
How can I asign a number value to a text line in Excel? | Charts and Charting in Excel | |||
Adding a text label to a line. | Charts and Charting in Excel | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |