vba macro to import text file

[just_using_excel]

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

 

[scottiedawg4]

I used record macro to record the code for importing a .txt file using the import wizard.


When I try to rerun the resulting code, it errors "invalid procedure or argument" highlighting the

.CommandType = 0

[/COLOR] With Sheets("Draw").QueryTables.Add(Connection:= _        "TEXT;E:\Quizzing\Drawzip\meetSch.txt", Destination:=Range("$A$1"))
        .CommandType = 0
        .Name = "meetSch_3"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1250
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

Any ideas how to fix this code?

 

[jbeaucaire]

Maybe try removing that one line?

 

[njchaltry]

This version of that macro will put the incoming data into the next empty row on the current sheet.

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

Sorry to bump this thread but this is the best code I have been able to find. Instead of adding a new row, I'd like to add a new column to the right. I'm working with a file that needs to be imported daily into a new column for each day of the month.

 

[jbeaucaire]

Like so:

Option Explicit

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

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

LastCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fName, _
        Destination:=Cells(1, LastCol))
            .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

 

[tiger3p]

Hello,

I'm trying to figure out the code to state that if the text file you are importing is blank data (empty) then display a message to state "You are importing blank file." I'm getting an out of memory error because of this. The ideal way would it to process and just display empty results but I think that is more complicated.

Any help would be great appreciated.

Thank you!

 

[Emrill]

I have been trying to tweak this macro with little success, I want it to allow me to select a text file with the extension of ".nstrpx" but when I run the macro i get an empty window! There are only these types of files stored in the directory so a all files list would also work. Below is what i am using at he moment.



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;C:\Program Files (x86)\SCM Group\Maestro\Report\Nesting\*.nstrptx" _
, 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]

This is the line of code you would edit:

fName = Application.GetOpenFilename("Text Files (*.txt), *.txt")

Can you guess the two little edits you make here?

 

[Emrill]

I tried editing that line, changing the txt to nstrptx but nothing shoes up in the window

 

[Ansh1502]

Hi Guys,

Need help for one macro , i just want to change the file location and file name while importing text file to excel.

Below is the code

Sub Macro9()

'

' Macro9 Macro

'

'

Dim FP As String, FN As String

FP = "\\seskcifssvm01.emea.astrazeneca.net\gfs_daas\DesktopProd\Desktop\Testing"

FN = Range("K33").Value

With ActiveSheet.QueryTables.Add(Connection:= _

"TEXT;FP & & ".txt_

, Destination:=Range("$A$1"))

.CommandType = 0

.Name = "ISR"

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.TextFilePromptOnRefresh = False

.TextFilePlatform = 1252

.TextFileStartRow = 1

.TextFileParseType = xlDelimited

.TextFileTextQualifier = xlTextQualifierDoubleQuote

.TextFileConsecutiveDelimiter = False

.TextFileTabDelimiter = True

.TextFileSemicolonDelimiter = True

.TextFileCommaDelimiter = False

.TextFileSpaceDelimiter = False

.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)

.TextFileTrailingMinusNumbers = True

.Refresh BackgroundQuery:=False

End With

Range("C12").Select

End Sub

<colgroup><col></colgroup><tbody>
</tbody>

 

[s_smile]

Hey. I am also working with taht code, but i need to modify it, so that if i hit a specific word in the textfile it will skip one or two lines in that txt file and proceed by importing the nextline that it finds. so for example when the text file looks like this:

1 numbers
2 numbers
3 Start (if "start" then skip row and jump to next)
4 numbers
5 stop (if "stop" then skip 2 rows and jump to next)
6 different numbers
7 numbers

so that i only have the rows with numbers left.

also, the nubers are seherated by spaces from each other in one row. is it possible to code, if it hits a space, than it displays the next number in that row in a new column?

hope you can help! thanks