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

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

' 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(xlD own))
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

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

bump :)


somebody, anybody? :

--
Mslad
-----------------------------------------------------------------------
Mslady's Profile: http://www.excelforum.com/member.php...fo&userid=2777
View this thread: http://www.excelforum.com/showthread.php?threadid=47300

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

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

' 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(xlD own))
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

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

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

' 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 :)


--
Mslady
------------------------------------------------------------------------
Mslady's Profile: http://www.excelforum.com/member.php...o&userid=27776
View this thread: http://www.excelforum.com/showthread...hreadid=473001