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

[Mslady]

Hi all, Im new here, Please welcome me by helping me solve my problem


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]

bump


somebody, anybody? :

 

[Guest]

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

 

[Mslady]

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.


Code:
--------------------

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