Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Text file into an existing Worksheet
My code opens a new workbook. Instead I need the data to be imported into an
existing worksheet name GLFBCALO. Is there a way I can do this? Public Sub ImportOKdata() Dim MyFile As String Dim ColumnsDesired Dim DataTypeArray Dim ColumnArray(0 To 11, 1 To 2) Dim x Sheets("GLFBCALO").Select Cells.Select Selection.Clear 'fill the column and data type info 'Data Type 1 = general 2 = text, 9 skip ColumnsDesired = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) DataTypeArray = Array(1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 1) 'populate the array for fieldinfo For x = LBound(ColumnsDesired) To UBound(ColumnsDesired) ColumnArray(x, 1) = ColumnsDesired(x) ColumnArray(x, 2) = DataTypeArray(x) Next x ' Get file With Application.FileDialog(msoFileDialogFilePicker) .AllowMultiSelect = False .Filters.Add "Text files", "*.txt" If .Show = True Then MyFile = .SelectedItems(1) 'Import data For x = LBound(ColumnsDesired) To UBound(ColumnsDesired) ColumnArray(x, 1) = ColumnsDesired(x) ColumnArray(x, 2) = DataTypeArray(x) Next x Workbooks.OpenText Filename:=MyFile, _ DataType:=xlDelimited, Tab:=True, FieldInfo:=ColumnArray End If End With End Sub -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200609/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Text file into an existing Worksheet
Public Sub ImportOKdata()
Dim MyFile As String Dim ColumnsDesired Dim DataTypeArray Dim ColumnArray(0 To 11, 1 To 2) Dim x, sh as worksheet Sheets("GLFBCALO").Select set sh = Activesheet Cells.Select Selection.Clear 'fill the column and data type info 'Data Type 1 = general 2 = text, 9 skip ColumnsDesired = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) DataTypeArray = Array(1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 1) 'populate the array for fieldinfo For x = LBound(ColumnsDesired) To UBound(ColumnsDesired) ColumnArray(x, 1) = ColumnsDesired(x) ColumnArray(x, 2) = DataTypeArray(x) Next x ' Get file With Application.FileDialog(msoFileDialogFilePicker) .AllowMultiSelect = False .Filters.Add "Text files", "*.txt" If .Show = True Then MyFile = .SelectedItems(1) 'Import data For x = LBound(ColumnsDesired) To UBound(ColumnsDesired) ColumnArray(x, 1) = ColumnsDesired(x) ColumnArray(x, 2) = DataTypeArray(x) Next x Workbooks.OpenText Filename:=MyFile, _ DataType:=xlDelimited, Tab:=True, FieldInfo:=ColumnArray End If End With Activesheet.usedRange.Copy Destination:=sh.Range("A1") ActiveWorkbook.close SaveChanges:=False End Sub -- Regards, Tom Ogilvy "Fredriksson via OfficeKB.com" wrote: My code opens a new workbook. Instead I need the data to be imported into an existing worksheet name GLFBCALO. Is there a way I can do this? Public Sub ImportOKdata() Dim MyFile As String Dim ColumnsDesired Dim DataTypeArray Dim ColumnArray(0 To 11, 1 To 2) Dim x Sheets("GLFBCALO").Select Cells.Select Selection.Clear 'fill the column and data type info 'Data Type 1 = general 2 = text, 9 skip ColumnsDesired = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) DataTypeArray = Array(1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 1) 'populate the array for fieldinfo For x = LBound(ColumnsDesired) To UBound(ColumnsDesired) ColumnArray(x, 1) = ColumnsDesired(x) ColumnArray(x, 2) = DataTypeArray(x) Next x ' Get file With Application.FileDialog(msoFileDialogFilePicker) .AllowMultiSelect = False .Filters.Add "Text files", "*.txt" If .Show = True Then MyFile = .SelectedItems(1) 'Import data For x = LBound(ColumnsDesired) To UBound(ColumnsDesired) ColumnArray(x, 1) = ColumnsDesired(x) ColumnArray(x, 2) = DataTypeArray(x) Next x Workbooks.OpenText Filename:=MyFile, _ DataType:=xlDelimited, Tab:=True, FieldInfo:=ColumnArray End If End With End Sub -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200609/1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Text file into an existing Worksheet
Thank you very much. I was trying to used a previous example that you replied.
I now know what I did wrong. I forgot to declare sh as a worksheet. Thanks for your help. I appreciate it Tom Ogilvy wrote: Public Sub ImportOKdata() Dim MyFile As String Dim ColumnsDesired Dim DataTypeArray Dim ColumnArray(0 To 11, 1 To 2) Dim x, sh as worksheet Sheets("GLFBCALO").Select set sh = Activesheet Cells.Select Selection.Clear 'fill the column and data type info 'Data Type 1 = general 2 = text, 9 skip ColumnsDesired = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) DataTypeArray = Array(1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 1) 'populate the array for fieldinfo For x = LBound(ColumnsDesired) To UBound(ColumnsDesired) ColumnArray(x, 1) = ColumnsDesired(x) ColumnArray(x, 2) = DataTypeArray(x) Next x ' Get file With Application.FileDialog(msoFileDialogFilePicker) .AllowMultiSelect = False .Filters.Add "Text files", "*.txt" If .Show = True Then MyFile = .SelectedItems(1) 'Import data For x = LBound(ColumnsDesired) To UBound(ColumnsDesired) ColumnArray(x, 1) = ColumnsDesired(x) ColumnArray(x, 2) = DataTypeArray(x) Next x Workbooks.OpenText Filename:=MyFile, _ DataType:=xlDelimited, Tab:=True, FieldInfo:=ColumnArray End If End With Activesheet.usedRange.Copy Destination:=sh.Range("A1") ActiveWorkbook.close SaveChanges:=False End Sub My code opens a new workbook. Instead I need the data to be imported into an existing worksheet name GLFBCALO. Is there a way I can do this? [quoted text clipped - 35 lines] End With End Sub -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do a import data from a text file to an excel worksheet | Excel Discussion (Misc queries) | |||
import data from txt file to an existing excel file | Excel Discussion (Misc queries) | |||
how to import more than 1 text file into the same Excel worksheet. | Excel Discussion (Misc queries) | |||
Changing only source file of pre-existing text import QueryTable? | Excel Programming | |||
Help: Impot Text File to existing worksheet | Excel Programming |