Import Text File to Different Worksheet

[squaredealb]

Hey All,
I’m having some trouble with a macro I’m using to import a text file. Currently, the data is imported starting at the selected cell of the active worksheet. My goal is to import into a different worksheet in the same workbook. I’ve searched the forum in hopes of figuring this out on my own but haven’t had any luck. Here’s what I’m using now:

Public Sub ImportTextFile(FName As String, Sep As String)
Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer
Application.ScreenUpdating = False
'On Error GoTo EndMacro:
SaveColNdx = ActiveCell.Row
RowNdx = ActiveCell.Row
Open FName For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) <> Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos >= 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend
EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1
End Sub
Public Sub DoTheImport()
Dim FName As Variant
Dim Sep As String
FName = Application.GetOpenFilename _
(filefilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If
Sep = Chr(9)
ImportTextFile CStr(FName), Sep
End Sub

[mdmackillop]

Try changing this line to
Sheets(2).Cells(RowNdx, ColNdx).Value = TempVal
or
Sheets("Sheet2").Cells(RowNdx, ColNdx).Value = TempVal
or similar

[mdmackillop]

Forgot to welcome you to VBAX!
BTW, when you post code, select it and click the VBA button to format it as shown
Regards
MD

[Charlize]

Why not selecting the destination sheet and row before the linesSaveColNdx = ActiveCell.Row
RowNdx = ActiveCell.Row

[squaredealb]

Thanks for the suggestions and for the welcome, mdmackillop. I changed the line you suggested, modifying "sheet2" to match the name of my worksheet, and the macro executed but I don't see the data. Charlize, how would I go about selecting the destination sheet? I am very to new to working with VBA and I pieced together what I have so far from things I found in other threads. Thanks again for all of the help.

[mdmackillop]

Add a debug line into your code
Debug.Print TempVal
Sheets(2).Cells(RowNdx, ColNdx).Value = TempVal

In the VB Editor, Click View/Immediate window. If you then step through your code, the current value of TempVal should appear in the window. If nothing appears, you're not getting a value to place in your spreadsheet.

[squaredealb]

I added the debug line and values are appearing in the Immediate window. I found that the data imports successfully if I have the target worksheet active when I start the macro but it does not import if I have any other sheet selected.

[mdmackillop]

What is the target sheet name?

[mdmackillop]

You are using the active cell row and column to determine where you are going to insert the data. How will you dertermine where it is to be place on another sheet? Can you post your workbook?

[squaredealb]

I've attached my workbook. My goal is to browse for the data file from the "Start Here" sheet and import the data to the "Import" sheet, starting at cell A1. The Import sheet will be hidden to the user. I was only able to attach a max of 1 file, so an example of the text file is not included. However, it is tab-delimited.

[mdmackillop]

The Split function is much simpler and more efficient than looping to read each item in each line. The code will write successive imports below the previous one in the Imports sheet.
Option Explicit

Public Sub ImportTextFile(FName As String, Sep As String)

Dim Rw As Long
Dim WholeLine As String
Dim WS As Worksheet
Dim txt As Variant

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

Set WS = Sheets("IMPORT")
Rw = WS.Cells(Rows.Count, 1).End(xlUp).Row() + 1

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
txt = Split(WholeLine, Sep)
WS.Cells(Rw, 1).Resize(, UBound(txt) + 1) = txt
Rw = Rw + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

Public Sub DoTheImport()
Dim FName As Variant
Dim Sep As String

FName = Application.GetOpenFilename _
(filefilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If
'chr(9) defines tab as delimiter
Sep = Chr(9)
ImportTextFile CStr(FName), Sep

End Sub

[squaredealb]

Thank you very much for all of the help, mdmackillop. My macro is functional now. The data import starts in Row 2 of the import sheet instead of Row 1 but I modified the parsing worksheets to call from the correct range. Thanks again and cheers!

[mdmackillop]

Glad to help. Often row 1 is handy to reserve for headers. A minor change to Rw will do though to fill from row 1
Set WS = Sheets("IMPORT")
Rw = WS.Cells(Rows.Count, 1).End(xlUp).Row()
If Rw <> 1 Then Rw = Rw + 1