+ Reply to Thread
Results 1 to 7 of 7

Importing text file from current workbook directory

  1. #1
    Registered User
    Join Date
    07-09-2008
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    91

    Importing text file from current workbook directory

    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!!!



    PHP Code: 
    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(111111)
            .
    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(111111)
            .
    TextFileTrailingMinusNumbers True
            
    .Refresh BackgroundQuery:=False
        End With
    End Sub 

  2. #2
    Valued Forum Contributor
    Join Date
    08-26-2006
    Location
    -
    MS-Off Ver
    2010
    Posts
    388
    Change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    07-09-2008
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    91
    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?

  4. #4
    Registered User
    Join Date
    07-09-2008
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    91
    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:

    PHP Code: 
    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!!!

  5. #5
    Valued Forum Contributor
    Join Date
    08-26-2006
    Location
    -
    MS-Off Ver
    2010
    Posts
    388
    Quote Originally Posted by hluk
    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.
    Quote Originally Posted by hluk
    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.
    Quote Originally Posted by hluk
    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.

  6. #6
    Registered User
    Join Date
    07-09-2008
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    91
    Thank you TJ for the replies.

    PHP Code: 
    '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?

    PHP Code: 
    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.

  7. #7
    Valued Forum Contributor
    Join Date
    08-26-2006
    Location
    -
    MS-Off Ver
    2010
    Posts
    388
    Quote Originally Posted by hluk
    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?

    PHP Code: 
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1