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

[Mslady]

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()

Please Login or Register  to view this content.

[Mslady]

bump


somebody, anybody?

[Jim Thomlinson]

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

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

Please Login or Register  to view this content.

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