Hello!

I'm working on creating a utility that will import files from an FTP t
the user's hard drive, and then, if the user chooses, import th
particular file last downloaded into excel in a new worksheet. I'v
found a lot of code online and added it to what I've already made, an
this is what I have so far:

///////////////////////////////////////////////////////////////////////////////////////////////////////

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

Dim RowNdx As Integer
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.Column
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)
Sheets("Import Results").Select
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 Import()
Dim Sep As String

Sep = InputBox("Enter a single delimiter character.", _
"Import Text File")
ImportTextFile CStr(txtPC.Value), Sep

End Sub

///////////////////////////////////////////////////////////////////////////////////////////////////////

This code works, and imports the file, delimited by a character of th
user's choice, but it absolutely refuses to import on a new or eve
seperate worksheet. It instead just pastes over on the main page, whic
is pretty annoying. Does anyone have an idea on how to adapt this cod
to start posting the import on a new worksheet? I'd be ever s
grateful!

Respectfully yours,
Mat

--
Mystrunne
-----------------------------------------------------------------------
Mystrunner's Profile: http://www.excelforum.com/member.php...fo&userid=3686
View this thread: http://www.excelforum.com/showthread.php?threadid=56578

Mystrunner,

The problem in your code appears to be that there is no mechanism to add a
new worksheet or to change the active worksheet.

Try adding the following line to the input sub:

-----

Public Sub Import()
Dim Sep As String

Sep = InputBox("Enter a single delimiter character.", _
"Import Text File")

' Add this line here
ActiveWorkbook.Sheets.Add

ImportTextFile CStr(txtPC.Value), Sep

End Sub

-----

Adding that line will add a new worksheet named "Sheet #" where # is the
next available number. This new sheet will automatically get the focus and
the import should go to it.

David

"Mystrunner" wrote
in message ...

Hello!

I'm working on creating a utility that will import files from an FTP to
the user's hard drive, and then, if the user chooses, import the
particular file last downloaded into excel in a new worksheet. I've
found a lot of code online and added it to what I've already made, and
this is what I have so far:

///////////////////////////////////////////////////////////////////////////////////////////////////////

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

Dim RowNdx As Integer
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.Column
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)
Sheets("Import Results").Select
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 Import()
Dim Sep As String

Sep = InputBox("Enter a single delimiter character.", _
"Import Text File")
ImportTextFile CStr(txtPC.Value), Sep

End Sub

///////////////////////////////////////////////////////////////////////////////////////////////////////

This code works, and imports the file, delimited by a character of the
user's choice, but it absolutely refuses to import on a new or even
seperate worksheet. It instead just pastes over on the main page, which
is pretty annoying. Does anyone have an idea on how to adapt this code
to start posting the import on a new worksheet? I'd be ever so
grateful!

Respectfully yours,
Matt


--
Mystrunner
------------------------------------------------------------------------
Mystrunner's Profile:
http://www.excelforum.com/member.php...o&userid=36864
View this thread: http://www.excelforum.com/showthread...hreadid=565781