Importing text file into an existing excel workbook.

  • Thread starter Thread starter Juan Melero
  • Start date Start date
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.
 
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
 
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.
 
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
 
Back
Top