importing data into a sheet

joed · May 10th, 2002, 12:50 PM

Is it possible to imoport data from a text file into an existing sheet. All I can find is file open and that creates a new sheet.

Tom Schreiner · May 10th, 2002, 02:00 PM

Sure...
From the worksheet menu bar, choose Data, Get External Data, Import Text File.
Pretty straight-forward.
If you need any further assistance, repost.
Tom

joed · May 10th, 2002, 06:36 PM

I don't have the option to import text data. Only have run query option and when I try that 'query is not installed'.

Tom Schreiner · May 10th, 2002, 06:43 PM

Hi Joed.
Maybe this is an installation option?
If nobody can help you, we can write a macro which will do the import.
Will wait and see...
Tom

joed · May 13th, 2002, 10:47 AM

I would like to use the macro option. That is what I am trying to do anyway.
Currently I am using the opentext command. That opens a new sheet. What command would import into the current sheet?

Thanks for the help.

Mark W. · May 13th, 2002, 11:00 AM

Quote:

On 2002-05-10 13:00, TsTom wrote:
Sure...
From the worksheet menu bar, choose Data, Get External Data, Import Text File.
Pretty straight-forward.
If you need any further assistance, repost.
Tom

...not available in XL97.

Mark W. · May 13th, 2002, 11:05 AM

Quote:

On 2002-05-10 11:50, joed wrote:
Is it possible to imoport data from a text file into an existing sheet. All I can find is file open and that creates a new sheet.

What's the nature of your text file? Is it delimited? If so, by what? Is it a report or a data list?

joed · May 13th, 2002, 11:44 AM

I should state I am using excel97. That's what we have in the office.
The file is fixed length records of 200.
Here is what I am currently using to open the file. 'Which' is the file name variable.

Workbooks.OpenText Which, _
Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 2), Array(10, 2), Array(34, 2), Array(42, 2), Array(54, 2), Array(57, 2), _
Array(66, 2), Array(79, 2), Array(97, 2), Array(113, 1), Array(123, 2), Array(133, 2))

kkknie · May 13th, 2002, 11:50 AM

I've had similar issues and have gone one of two ways.

1. Open the file, move it to the current workbook, copy the data from the imported sheet to the existing sheet, then delete the old sheet. Set Application.ScreenUpdating = False while doing this and your users won't see all of the automation.

2. Open the file directly through VB using the Open FileName For Input as #1 command and then loop through the data and put it into the correct cells of your worksheet.

Either works fine and I doubt I could recommend one over the other. Personal preferences...

Good luck,

K

P.S. One note if using the first method. Capture the name of your workbook into a variable first since you will need to use it to move the imported sheet. This is only really necessary if you ever rename the workbook.

[ This Message was edited by: kkknie on 2002-05-13 10:52 ]

joed · May 13th, 2002, 12:14 PM

Both of those methods are beyond my knowledge base. I am trying to learn if you care to be more detailed.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 10th, 2002, 12:50 PM	#1
joed New Member Join Date: May 2002 Posts: 48	Is it possible to imoport data from a text file into an existing sheet. All I can find is file open and that creates a new sheet.

May 10th, 2002, 02:00 PM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Sure... From the worksheet menu bar, choose Data, Get External Data, Import Text File. Pretty straight-forward. If you need any further assistance, repost. Tom

May 10th, 2002, 06:36 PM	#3
joed New Member Join Date: May 2002 Posts: 48	I don't have the option to import text data. Only have run query option and when I try that 'query is not installed'.

May 10th, 2002, 06:43 PM	#4
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Hi Joed. Maybe this is an installation option? If nobody can help you, we can write a macro which will do the import. Will wait and see... Tom

May 13th, 2002, 10:47 AM	#5
joed New Member Join Date: May 2002 Posts: 48	I would like to use the macro option. That is what I am trying to do anyway. Currently I am using the opentext command. That opens a new sheet. What command would import into the current sheet? Thanks for the help.

May 13th, 2002, 11:44 AM	#8
joed New Member Join Date: May 2002 Posts: 48	I should state I am using excel97. That's what we have in the office. The file is fixed length records of 200. Here is what I am currently using to open the file. 'Which' is the file name variable. Workbooks.OpenText Which, _ Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _ Array(Array(0, 2), Array(10, 2), Array(34, 2), Array(42, 2), Array(54, 2), Array(57, 2), _ Array(66, 2), Array(79, 2), Array(97, 2), Array(113, 1), Array(123, 2), Array(133, 2))

May 13th, 2002, 11:50 AM	#9
kkknie Board Regular Join Date: Apr 2002 Location: Greenwood, SC Posts: 677	I've had similar issues and have gone one of two ways. 1. Open the file, move it to the current workbook, copy the data from the imported sheet to the existing sheet, then delete the old sheet. Set Application.ScreenUpdating = False while doing this and your users won't see all of the automation. 2. Open the file directly through VB using the Open FileName For Input as #1 command and then loop through the data and put it into the correct cells of your worksheet. Either works fine and I doubt I could recommend one over the other. Personal preferences... Good luck, K P.S. One note if using the first method. Capture the name of your workbook into a variable first since you will need to use it to move the imported sheet. This is only really necessary if you ever rename the workbook. [ This Message was edited by: kkknie on 2002-05-13 10:52 ]

May 13th, 2002, 12:14 PM	#10
joed New Member Join Date: May 2002 Posts: 48	Both of those methods are beyond my knowledge base. I am trying to learn if you care to be more detailed.