vba macro to import text file

J

just_using_excel

Board Regular
Joined
Apr 21, 2010
Messages
52
im looking for a macro that will use the import feature that will import text and delimit it.

i want to manually choose the file. all i know is workbooks.open filename: function, not the import :(
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
If you turn on the macro recorder and let it record you doing all the steps one time to import a file, then post the resulting code here, we can help turn that into a more dynamic macro.
 
Upvote 0
I rather have a command that will use the workbook.open command as oppose to a specific file
Code: 
Sub Macro6()
'
' Macro6 Macro
'
'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "c:\sample.txt", Destination:=Range( _
        "$A$1"))
        .Name = "sample"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierNone
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = "" & Chr(10) & ""
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub
 
Upvote 0
Try this:
Code: 
Option Explicit

Sub ImportTextFile()
Dim fName As String

fName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If fName = "False" Then Exit Sub

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fName, _
        Destination:=Range("$A$1"))
            .Name = "sample"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierNone
            .TextFileConsecutiveDelimiter = True
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            .TextFileOtherDelimiter = "" & Chr(10) & ""
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, _
               1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
               1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
               1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
    End With
End Sub
 
Upvote 0
jbeaucaire said:
Try this:
Code: 
Option Explicit

Sub ImportTextFile()
Dim fName As String

fName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If fName = "False" Then Exit Sub

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fName, _
        Destination:=Range("$A$1"))
            .Name = "sample"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierNone
            .TextFileConsecutiveDelimiter = True
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            .TextFileOtherDelimiter = "" & Chr(10) & ""
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, _
               1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
               1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
               1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
    End With
End Sub
Click to expand...


thx brother, you give solution fo me .....
 
Upvote 0
dha_indra said:
thx brother, you give solution fo me .....
Click to expand...

That code is great. However, how can it be modified to import each selected txt file into a new row. I ran this macro and selected file. It worked great. Then i ran it again to grab another file and it just added it the data starting at the last column. Im new to macro writing and am trying to put the results from a Word Form survey I made in a spreadsheet. There are 60+ questions in the survey and I have a bunch of surveys to compile. I'd like to be able to select the individual txt files and have the imported dataput into different rows. Any help would be greatly appreciated.
 
Upvote 0
Post a zip file somewhere that includes two of your text files and an Excel sheet with the data from those files showing the way you need. Then post a link here to where those files are posted and I'll take a look. It's really a whole different thing to do what you've described.
 
Upvote 0
I was searching the forum to import text files and came across this old string that does what I want to do, sort of. The post by Williamsmacros seems to bring up something I want to do, import after the last import.

Here is what I’m doing. I have various tools out in my organization that help people do their job. To show return on (my) investment, I track the usage by writing an entry into a text file when they are used.

I then bring these text (tracking) files into another spreadsheet to number crunch them into reports for my boss to show the effect of my work for our folks. It’s working well, except now they text files are getting quite big and it takes a few minutes to get everything in.

I realized I’m re-importing all the text each time. If I could import the text file, and erase its contents and next time I import the text file, it just adds to the end of the list, I think this would speed things up. But of course each text file imports at a specific cell location.

I see in the code above (jbeaucaire, Apr 29th 2010 01:28 AM) there is a Destination:=Range(“$A$1”) that, I assume, sets the location. If I reset that to the next available row after the data I already imported, would that bring in the text that’s in the file after I erased it after the last import? – basically picking up where I left off?

If this is true, would I just code to do the import and delete the connections from the workbook connections area?

Thanks,
Mark
 
Upvote 0
This version of that macro will put the incoming data into the next empty row on the current sheet.

Rich (BB code): 
Option Explicit

Sub ImportTextFile()
Dim fName As String, LastRow As Long

fName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If fName = "False" Then Exit Sub

LastRow = Range("A" & Rows.Count).End(xlUp).Row + 1

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fName, _
        Destination:=Range("A" & LastRow))
            .Name = "sample"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierNone
            .TextFileConsecutiveDelimiter = True
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            .TextFileOtherDelimiter = "" & Chr(10) & ""
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, _
               1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
               1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
               1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
    End With
End Sub
 
Upvote 0
Thanks jbeaucaire!!
This did the trick and should speed up ingesting the text log files. My logging routine will (re) create the log file when used. So, when I import like you showed, I delete the log file so I don't get duplicate entries.

Thanks for your quick reply.
Mark
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
VBA to import multilpe xml files
Replies
1
Views
21
PippaThePointer
P
J
  • Question
Automatically import .txt files into separate sheets in Excel
Replies
1
Views
165
John_w
J
E
  • Question
Importing Project
Replies
2
Views
166
Fluff
Fluff
W
  • Question
How to add "edit text import" to get data from an external text file?
Replies
3
Views
117
waljbt
W
D
  • Question
Import and format txt file into open workbook.
Replies
4
Views
601
djdbg1
D

Forum statistics

Threads
1,214,587
Messages
6,120,405
Members
448,958
Latest member
Hat4Life

Share this page

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