importing data into a sheet

joed

Board Regular
Joined
May 5, 2002
Messages
50
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.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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
 
Upvote 0
I don't have the option to import text data. Only have run query option and when I try that 'query is not installed'.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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))
 
Upvote 0
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
 
Upvote 0
Both of those methods are beyond my knowledge base. I am trying to learn if you care to be more detailed.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top