vba macro to import text file

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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
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.
 

just_using_excel

Board Regular
Joined
Apr 21, 2010
Messages
52
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
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
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
 

dha_indra

New Member
Joined
Mar 15, 2012
Messages
1
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


thx brother, you give solution fo me .....
 

williamsmacros

New Member
Joined
Apr 16, 2013
Messages
1
thx brother, you give solution fo me .....

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.
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
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.
 

mark hansen

Well-known Member
Joined
Mar 6, 2006
Messages
534
Office Version
  1. 2016
Platform
  1. Windows
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
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
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
 

mark hansen

Well-known Member
Joined
Mar 6, 2006
Messages
534
Office Version
  1. 2016
Platform
  1. Windows
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
 

Forum statistics

Threads
1,181,649
Messages
5,931,216
Members
436,784
Latest member
amuljono

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
Top