Importing text file from current workbook directory

[hluk]

Hi everyone...

I apologize in advance because I know I am asking a simple question. I am VERY new to programming in excel and I looked for hours on the forum before posting this.

I want to import text files (without extensions - eg: matsum, not matsum.txt) into excel from the same folder as where the workbook is located. I recorded a macro and changed only one line. The error message displays as run time error: 1004, excel can't find text file... Marco 1 is the one I tried to change, Marco 2 is the one that was recorded.

Please help, I am so frustrated. THANKS in advanced!!!

Sub Macro1()
'
' Macro1 Macro
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT; & Thisworkbook.Path & \matsum.", Destination:= _
        Range("$T$12"))
        .Name = "matsum."
        .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 = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

----------------------------------------------------------

Sub Macro2()
'
' Macro2 Macro
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Documents and Settings\harry\Desktop\test\matsum.", Destination:= _
        Range("$G$9"))
        .Name = "matsum."
        .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 = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub 

[T-J]

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[hluk]

Thank you very much!!! And for the quick reply.

Can I also ask how I can specify which row/ which column I want to start and finish importing text from?

[hluk]

Instead of adding the information on the ActiveSheet, can I specify the name of the sheet I want this to add to? If there is a better way, please advise. Right now, I am doing the following:

Sheets("Info").Select
With ActiveSheet.QueryTables.Add(Connection:=... 

In addition, every time I run this macro, it adds a new column next to what is there already. Can I specify the import text to REPLACE/ UPDATE the existing information in the SAME COLUMN "$T$12"?

Thank you very much!!!

[T-J]

Can I also ask how I can specify which row/ which column I want to start and finish importing text from?

You may need to experiment with some of this to get the right result:

Please Login or Register  to view this content.

Instead of adding the information on the ActiveSheet, can I specify the name of the sheet I want this to add to?

Please Login or Register  to view this content.

In addition, every time I run this macro, it adds a new column next to what is there already. Can I specify the import text to REPLACE/ UPDATE the existing information in the SAME COLUMN "$T$12"?

Use

Please Login or Register  to view this content.

[hluk]

Thank you TJ for the replies.

'this starts importing from row 3
.TextFileStartRow = 3 

So now I can choose to start importing at a chosen row. Is there an option to skip rows 10-30 for example? Also, can I choose to end import row at 50 for example?

In addition, can I display the name of the file I am importing above the cells? For example, messag is my filename. Can I insert this in the cell above to classify my imported data?

With Worksheets("Info").QueryTables.Add(Connection:= _
        "TEXT;" & ThisWorkbook.Path & "\messag.", Destination _
        :=Worksheets("Info").Range("A1"))
        .Name = "messag" 

I was wondering also if there is a help/ reference manual for EXCEL VB to figure these things out. I don't find the help in excel very useful. This forum is super helpful but I don't want to keep posting and bother you guys if I don't have to.

Thanks again for helping.

[T-J]

So now I can choose to start importing at a chosen row. Is there an option to skip rows 10-30 for example? Also, can I choose to end import row at 50 for example?

From the little I know about Querytables I dont think they have this flexibility.

You may want to look at other methods for reading in text files. Some possibilities are:

• FileSystemObject
  
• Line Input #
  
• ADO Recordset using MoveNext

In addition, can I display the name of the file I am importing above the cells? For example, messag is my filename. Can I insert this in the cell above to classify my imported data?

With Worksheets("Info").QueryTables.Add(Connection:= _
        "TEXT;" & ThisWorkbook.Path & "\messag.", Destination _
        :=Worksheets("Info").Range("A1"))
        .Name = "messag" 

Please Login or Register  to view this content.

I was wondering also if there is a help/ reference manual for EXCEL VB to figure these things out. I don't find the help in excel very useful. This forum is super helpful but I don't want to keep posting and bother you guys if I don't have to.
Thanks again for helping.

I find it generally ok, half the battle for a beginner is knowing what is available in VBA and finding things.

A useful learning tool is recording macros and stepping through the code with the debugger, setting breakpoints and watches. If you highlight a keyword and press F1 it will display the help topic.

There are lots of people on here who will help you out.