Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Opening a text file on an excel spreadsheet, using VB6

Posted on 2003-03-10
Medium Priority
Last Modified: 2010-04-07
I have a text file and I need to imported (open it) onto an excel spreadsheet. Is there any way of doing that. I have no code so far.
Thank you
Question by:caleno
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

Jagov177 earned 80 total points
ID: 8106845
replace "file.txt" with the name of your file

Sub OpenTextDoc()
     Workbooks.OpentText Filename:="file.txt", Tab:=True
End Sub

If you are opening several files try this:

Sub OpenTextDoc2()
     Dim Txtfle As String
     txtfle = InputBox("Enter File Name - ending with .txt")
     Workbooks.OpentText Filename:=txtfle, Tab:=True
End Sub

Expert Comment

ID: 8116461
Paste the below code into a excel module.
Sub Auto_open()
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\temp.txt", Destination _
        .Name = "temp"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = xlWindows
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .Refresh BackgroundQuery:=False
    End With
End Sub

This will import a file called "temp.txt" from the root drive "c:\".

Alternativly to using this code is you could record your own macro. When recording your macro to import the text file you need to go to Data->Get External Data->Import Text File.
This way excel VBA will generate the exact code you require.


Expert Comment

ID: 8504684
Dear caleno

Please check:


Community Support Moderator
Experts Exchange

Expert Comment

ID: 8900369
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Experts: Post your closing recommendations!  Who deserves points here?
LVL 49

Expert Comment

ID: 9074716
Moderator, my recommended disposition is:

    Accept Jagov177's comment(s) as an answer.

DanRollins -- EE database cleanup volunteer

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question