+ Reply to Thread
Results 1 to 10 of 10

Error Message usin code from Thread: Import Multiple Text Files into Excel - for beginners

Hybrid View

ATGM Error Message usin code from... 02-04-2013, 04:58 PM
ATGM Re: Error Message usin code... 02-04-2013, 05:00 PM
tigeravatar Re: Error Message usin code... 02-05-2013, 11:46 AM
ATGM Re: Error Message usin code... 02-11-2013, 05:39 PM
tigeravatar Re: Error Message usin code... 02-11-2013, 05:58 PM
ATGM Re: Error Message usin code... 02-11-2013, 07:00 PM
tigeravatar Re: Error Message usin code... 02-11-2013, 07:05 PM
ATGM Re: Error Message usin code... 02-11-2013, 07:35 PM
tigeravatar Re: Error Message usin code... 02-12-2013, 11:44 AM
ATGM Re: Error Message usin code... 02-12-2013, 04:15 PM
  1. #1
    Registered User
    Join Date
    01-30-2013
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 2003
    Posts
    6

    Error Message usin code from Thread: Import Multiple Text Files into Excel - for beginners

    Hi - I am a total beginner so was very interested to see a solution to my requirement, including "for beginner" instructions in the thread at
    http://www.excelforum.com/excel-prog...html?p=3107472 .

    Code provided there is:
            Sub ImportTextFilesMacro_rapidasia()
                
                'Change the folder path as needed
                Const strFolderPath As String = "C:\Test"
                
                'Declare variables
                Dim FSO As Object                   'Used to access the Windows FileSystem
                Dim strFileName As String           'Used to store the filename
                Dim arrText(1 To 65000) As String   'Used to store the .txt file's text
                Dim TextIndex As Long               'Used by arrText for storing the text in the correct location
                
                'Access the Windows FileSystem
                Set FSO = CreateObject("Scripting.FileSystemObject")
                
                'Use the folderpath to find all the first .txt file in that folder
                strFileName = Dir(strFolderPath & "\*.txt")
                
                'Loop through all the text files
                Do While Len(strFileName) > 0
                    'Found a text file, increase the TextIndex
                    TextIndex = TextIndex + 1
                    
                    'Use the Windows FileSystem to read the text file and store that information in arrText
                    arrText(TextIndex) = FSO.OpenTextFile(strFolderPath & "\" & strFileName).ReadAll
                    
                    strFileName = Dir   'Advance the loop
                Loop
                
                'If at least 1 text file was found, output the results in column C
                If TextIndex > 0 Then Range("C1").Resize(TextIndex).Value = Application.Transpose(arrText)
                
                'Cleanup objects and arrays
                Set FSO = Nothing
                Erase arrText
                
            End Sub
    I copied the listing and pasted it in, every time I try to run it I get

    Run-time error '1004':
    Application-defined or object-defined error

    in the debug screen a block of text is highlit yellow, it is not liking the text between the **'s:

    If TextIndex > 0 Then **Range("C1").Resize(TextIndex).Value = Application.Transpose(arrText)**
    i.e. flagged yellow in this section:
    Range("C1").Resize(TextIndex).Value = Application.Transpose(arrText)
    I am running Excel 2003, if that makes a difference.

    My fingers are crossed that someone can point me in the right direction to fix this.

    Andrew

  2. #2
    Registered User
    Join Date
    01-30-2013
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Error Message usin code from Thread: Import Multiple Text Files into Excel - for begin

    D'oh, please forgive "usin" in place of the more standard and correct "using" in the title.

  3. #3
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Error Message usin code from Thread: Import Multiple Text Files into Excel - for begin

    Andrew,

    Welcome to the forum!
    I am unable to reproduce that error. I tried with a directory that contained no text files, a single text file, and multiple text files and each time the code ran as intended.
    Can you provide a sample text file and sample workbook that is experiencing the issue?
    Also, is your workbook/worksheet protected?
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    01-30-2013
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Error Message usin code from Thread: Import Multiple Text Files into Excel - for begin

    Thank you for your quick reply...and apologies for my slow one. I think I have found a thing or two that may explain my problem. The files I would like to import as a single column per file are actually comma separated. There are a LOT of entries for each column and, on re-reading the original request and trying some simple text files, the macro will import each file into a single cell in the spreadsheet. I am probably trying to overfill the cells. My excel sheet with the macro and desired files are available at this location:
    <https://dl.dropbox.com/u/2489377/test_rapidasia.zip>

    Please forgive my misunderstanding of the original intention of this macro. If it is possible to extend the macro to do the following "wish list" please let me know how to go about it:

    Import a number of comma delimited CSV or TXT files while:
    - placing the title of the original file in the top row
    - putting each entry in a single row, stacking up in a column under the file name
    - starting a new column for the next file

    Thank you for your thoughts,

    Andrew

  5. #5
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Error Message usin code from Thread: Import Multiple Text Files into Excel - for begin

    Andrew,

    Awhile ago I wrote this code for a task very similar to this. The First code calls the ImportDelimitedTestFiles code, which will prompt you to select one (or more) .txt and/or .csv files. Each file will be imported into its own worksheet and the sheet name will be renamed to match the file name. Is that something you can work with?
    Sub tgr()
        
        ImportDelimitedTextFiles ","
        
    End Sub



    Public Sub ImportDelimitedTextFiles(ByVal sOtherChar As String, _
                                        Optional ByVal bConsecutiveDelimiter As Boolean = False, _
                                        Optional ByVal lTextQualifier As XlTextQualifier = xlTextQualifierDoubleQuote)
        
        Dim ws As Worksheet
        Dim FSO As Object
        Dim strText() As String
        Dim strName As String
        Dim i As Long, j As Long
        
        With Application.FileDialog(msoFileDialogFilePicker)
            .Filters.Clear
            .Filters.Add "Text and CSV Files", "*.txt, *.csv"
            .AllowMultiSelect = True
            .Title = "Select Text Files to Import"
            If .Show = False Then Exit Sub  'Pressed cancel
            Set FSO = CreateObject("Scripting.FileSystemObject")
            For i = 1 To .SelectedItems.Count
                strText = Split(FSO.OpenTextFile(.SelectedItems(i)).ReadAll, vbNewLine)
                strName = Replace(Mid(.SelectedItems(i), InStrRev(.SelectedItems(i), Application.PathSeparator) + Len(Application.PathSeparator)), ".txt", vbNullString)
                For j = 1 To 7
                    strName = Replace(strName, Mid(":\/?*[]", j, 1), " ")
                Next j
                strName = Trim(Left(WorksheetFunction.Trim(strName), 31))
                Select Case (Not Evaluate("IsRef('" & strName & "'!A1)"))
                    Case True:  Set ws = Sheets.Add(After:=Sheets(Sheets.Count))
                                ws.Name = strName
                    Case Else:  Set ws = Sheets(strName)
                                ws.UsedRange.Clear
                End Select
                With ws.Range("A1").Resize(UBound(strText) - LBound(strText) + 1)
                    .Value = Application.Transpose(strText)
                    .TextToColumns .Cells, xlDelimited, lTextQualifier, bConsecutiveDelimiter, False, False, False, False, True, sOtherChar
                End With
                Erase strText
                Set ws = Nothing
            Next i
            Set FSO = Nothing
        End With
        
    End Sub

  6. #6
    Registered User
    Join Date
    01-30-2013
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Error Message usin code from Thread: Import Multiple Text Files into Excel - for begin

    Hi - is the right process to enter these as module 1 and module 2, and the public sub does not show up in the macros list? If so, I get the Run-time error '1004' when I try to run the macro(s) on one or more of my test files except for the very short "simple_text_file_works.x" where x is either txt or csv. It also imports into long rows rather than columns, which I think may run out of cells sooner. Do you see this error when importing (e.g.) 1181FG8Q.MIC.csv from my zipped folder?

    I had found some code, earlier, which I (brutalized) tweaked to have similar functionality; the importing into a single worksheet with one column per file would be really helpful for my application, since I will otherwise end up manually merging the worksheets together by copying and pasting...and probably transposing too. I did like the easy folder navigation and file selection in this code.

  7. #7
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Error Message usin code from Thread: Import Multiple Text Files into Excel - for begin

    Unfortunately, I am at work and dropbox is blocked for me so I am unable to test using the data you have there. Can you upload a zip file to this thread? To attach a file, click the "Go Advanced" button and then click the paperclip icon to open the Manage Attachments dialog.

  8. #8
    Registered User
    Join Date
    01-30-2013
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Error Message usin code from Thread: Import Multiple Text Files into Excel - for begin

    test_rapidasia.zip

    Hope this works. I wasn't in the advanced mode before so did not see how to attach files.

    Andrew

  9. #9
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Error Message usin code from Thread: Import Multiple Text Files into Excel - for begin

    Andrew,

    The reason the code I provided was failing was because the text files didn't actually have any carriage returns, they were just long strings of text. I have adjusted the code into a single sub to fit your situation and tested it using your provided text files. The code now puts all of the data in a single column with the top cell being the name of the text file. Give it a try:
    Sub tgr()
        
        Dim ws As Worksheet
        Dim rngDest As Range
        Dim FSO As Object
        Dim strText() As String
        Dim i As Long
        
        With Application.FileDialog(msoFileDialogFilePicker)
            .Filters.Clear
            .Filters.Add "Text and CSV Files", "*.txt, *.csv"
            .AllowMultiSelect = True
            .Title = "Select Text Files to Import"
            If .Show = False Then Exit Sub  'Pressed cancel
            Set ws = Sheets.Add(After:=Sheets(Sheets.Count))
            Set FSO = CreateObject("Scripting.FileSystemObject")
            ws.Name = "Imported Text Files"
            For i = 1 To .SelectedItems.Count
                strText = Split(FSO.OpenTextFile(.SelectedItems(i)).ReadAll, ",")
                Select Case Len(ws.Range("A1").Text) = 0
                    Case True:  Set rngDest = ws.Range("A1")
                    Case Else:  Set rngDest = ws.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
                End Select
                rngDest.Value = Mid(.SelectedItems(i), InStrRev(.SelectedItems(i), Application.PathSeparator) + 1)
                rngDest.Offset(1).Resize(UBound(strText) - LBound(strText) + 1).Value = Application.Transpose(strText)
                Erase strText
                Set rngDest = Nothing
            Next i
            Set ws = Nothing
            Set FSO = Nothing
        End With
        
    End Sub

  10. #10
    Registered User
    Join Date
    01-30-2013
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Error Message usin code from Thread: Import Multiple Text Files into Excel - for begin

    That is very, VERY cool to see in operation. Thank you very much indeed. I have rated this thread as excellent but really that is because I do not see a forum mechanism for rating your help, where the 5 star rating belongs.

+ 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