Reading a text file (.txt) into current workbook, NOT loading as new workbook

JR_Chicago

New Member
Joined
Apr 20, 2010
Messages
23
Hey folks... I have two tab delimited text files that I'm trying to read into an Excel workbook which I've already created...

I've been unable to find the code for reading a text file into an existing workbook, rather, the book I am using, "Writing Excel Macros with VBA" by Steven Roman only has examples for loading text files as NEW workbooks.

I've been successful in loading the text files as new workbooks using the following code:

Code:
Sub ReadInTextFiles()
Workbooks.OpenText _
    Filename:="E:\New Folder\__New_Product_File.txt", _
    Origin:=xlWindows, _
    StartRow:=1, _
    DataType:=xlDelimited, _
    TextQualifier:=xlTextQualifierNone, _
    ConsecutiveDelimiter:=True, _
    Tab:=True, _
    FieldInfo:=Array(Array(1, 2), Array(2, 1), Array(3, 1), Array(4, 1))
End Sub
As I said, when I run this macro from the current workbook it opens a new instance of Excel and populates the values from the text file in that workbook.

I want to read both text files into my current workbook.

Anyone know how to do that?

Thanks.
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
Look in the menu at DATA > Import External Data > File Type: Text.

This wizard lets you import a text file, set all the import settings and column type, then select whether the data comes into a specific cell on a specific sheet or as a new worksheet. Either way, it comes into the current workbook.

You should be able to record your desired settings/usage and tweak that into a permanent macro.
 

JR_Chicago

New Member
Joined
Apr 20, 2010
Messages
23
This definitely helps, unfortunately, I need to be able to automate this task (reading in the files) with VBA as it is a task that will be repeated many times on an intraday basis.

The end user of the workbook will not have time to use the import wizard.

Are you familiar with how to execute this task in code?
 

JR_Chicago

New Member
Joined
Apr 20, 2010
Messages
23
Nevermind... I didn't realize you had indicated to record a macro while using the wizard. That works... Thanks
 

Forum statistics

Threads
1,082,254
Messages
5,364,055
Members
400,776
Latest member
JimmyLee

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top