Importing txt into XL as Delimited, what's wrong w my code?

bump


somebody, anybody?

For reference most of the people around here won't open attachments. Viruses
and the like... Which line of code is throwing the error? As a guess your
text file is not openeing. You are resuming next even if it does not open.
When you go to set the range the error is being thrown... Confirm that the
file is open and that the range rngRaw is not nothing before you go to resize
it...
--
HTH...

Jim Thomlinson


"Mslady" wrote:

>
> Hi all, Im new here, Please welcome me :D by helping me solve my problem
> :D
>
> I am using this code to import text file into excel.
> Can someone tell me what's wrong with my code? Why do i keep getting my
> customized msgbox error. This code works for another txtfile, but not
> this.
>
> please see
>
> I have attached the spreadsheet and the txt file im trying to import.
> Clicking the second button on the sheet will trigger this
> wkscmd_ImportData_Click()
>
>
> Code:
> --------------------
> Private Sub wkscmd_ImportData_Click()
>
> ' Local Variables
> Dim strPath As String, strFile As String
> Dim rngRaw As Range
>
> ' Get Data file parameters
> strPath = Me.Range("C1")
> strFile = Me.Range("C2")
>
> ' Verify that table is empty
> Me.Range("rdi_TableTop", Me.Range("rdi_TableTop").End(xlDown)).EntireRow.ClearContents
>
> ' Open and import datafile
> ' Open file
> Application.DisplayAlerts = False
> On Error Resume Next
> Workbooks.OpenText FileName:=strPath & strFile, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, _
> TextQualifier:=xlTextQualifierDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, _
> Comma:=False, Space:=False, Other:="*", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
> Application.DisplayAlerts = True
> On Error GoTo Err_BadFileName
> ' Import raw file
> Set rngRaw = Workbooks(strFile).ActiveSheet.Range("A1", Workbooks(strFile).ActiveSheet.Range("A1").End(xlDown))
> Me.Range("rdi_TableTop").Resize(rngRaw.Rows.Count, 1) = rngRaw.Value
> Workbooks(strFile).Close False
> Exit_BadFileName:
> Exit Sub
> Err_BadFileName:
> MsgBox "Please enter the correct FilePath in CELL C1, e.g. C:\myfolder\ " _
> & Chr(13) & "Be sure to have a slash behind your Filepath as shown above" _
> & Chr(13) & "And enter the correct FileName in CELL C2, e.g. rawdata.txt", vbOKOnly + vbCritical, "Bad Filepath OR FileName"
> Resume Exit_BadFileName
>
> End Sub
>
> --------------------
>
>
> +-------------------------------------------------------------------+
> |Filename: blah.TXT |
> |Download: http://www.excelforum.com/attachment.php?postid=3879 |
> +-------------------------------------------------------------------+
>
> --
> Mslady
> ------------------------------------------------------------------------
> Mslady's Profile: http://www.excelforum.com/member.php...o&userid=27776
> View this thread: http://www.excelforum.com/showthread...hreadid=473001
>
>

Thanks Jim, I finally got it to work.

I recorded a new macro "inside the same workbook" i have been working with. And copied and inserted the macro code into my code. And it works like charm. This is what i have now.

 
Private Sub wkscmd_ImportData_Click() 
     
     '   Local Variables
    Dim strPath As String, strFile As String 
     
     '   Get Data file parameters
    strPath = Me.Range("C1") 
    strFile = Me.Range("C2") 
     
     '   Verify that table is empty
    Me.Range("rdi_TableTop", "A" & Me.Range("A65536").End(xlDown).Row).EntireColumn.ClearContents 
     
     '   Open and import datafile
     ' Open file
    Application.DisplayAlerts = False 
    On Error Goto Err_BadFileName 
    Range("rdi_TableTop").Select 
    With Selection.QueryTable 
        .Connection = "TEXT;" & strPath & strFile 
        .TextFilePlatform = 1252 
        .TextFileStartRow = 1 
        .TextFileParseType = xlDelimited 
        .TextFileTextQualifier = xlTextQualifierDoubleQuote 
        .TextFileConsecutiveDelimiter = True 
        .TextFileTabDelimiter = False 
        .TextFileSemicolonDelimiter = False 
        .TextFileCommaDelimiter = False 
        .TextFileSpaceDelimiter = False 
        .TextFileOtherDelimiter = "*" 
        .TextFileColumnDataTypes = Array(1) 
        .TextFileTrailingMinusNumbers = True 
        .Refresh BackgroundQuery:=False 
    End With 
    Application.DisplayAlerts = True 
Exit_BadFileName: 
    Exit Sub 
Err_BadFileName: 
    MsgBox "Please enter the correct FilePath in CELL C1, e.g. C:\myfolder\  " _ 
    & Chr(13) & "Be sure to have a slash behind your Filepath as shown above" _ 
    & Chr(13) & "And enter the correct FileName in CELL C2, e.g. rawdata.txt", vbOKOnly + vbCritical, "Bad Filepath OR FileName" 
    Resume Exit_BadFileName 
     
End Sub

Thanks for your help and taking the time to look at my code