Importing text file into an existing excel workbook.

J

Juan Melero

I was wondering if it was possible to get a text file and
import it into an excel worksheet that already exist. For
example, I have an excel worksheet that has Headings and a
fixed format of where I key in data. I want to import a
text file that has the data that I key in everyday into
these columns. Is there a quick way of doing this or do I
have to write lots of code for this?
Thanks to whomever can help.
 
T

Tom Ogilvy

with this code in your workbook where you key in data and the sheet where
you key in the data active, run:

Sub GetData()
Dim rng as Range, wkbk as Workbook
set rng = cells(rows.count,1).End(xlup)(2)
set wkbk = workbooks.Open(Filename:="C:\Myfolder\mytextfile.txt")
wkbk.worksheets(1).Range("A1").CurrentRegion.Copy _
Destination:=rng
wkbk.close savechanges:=false
End Sub
 
J

Juan Melero

Is there a way of rearranging the data in the file to go
into different parts of the spreadsheet. Say for example
I have the following text file.
Store #
01008
01009
01010

I want to put this on the spreadsheet as :

A1 B1 B2 B3
Store # 01008 01009 01010
Is this possible? Really appreciate your quick response.
Thanks.
 
T

Tom Ogilvy

Sub GetData()
Dim rng as Range, wkbk as Workbook
set rng = cells(rows.count,1).End(xlup)(2)
set wkbk = workbooks.Open(Filename:="C:\Myfolder\mytextfile.txt")
wkbk.worksheets(1).Range("A1").CurrentRegion.Columns(1).Copy
rng.PasteSpecial paste:=xlPasteAll, Transpose:=True
wkbk.close savechanges:=false
End Sub
 
Top