Don't know how to call another macro(excel 2007, windows xp)

[yonnieboy]

Hi,
I went to this website (http://www.cpearson.com/excel/ImpText.aspx) looking to import around 100 text files from notepad into a single excel worksheet where each file imported is put into its own row or column.
I have very little clue as to what this code means so if possible please provide idiot proof directions.
I'm copying the first code listed under importing a text file and pasting it into one module in the vba editor and the last code listed under importing a text file into a separate module in the vba editor and then i try running the module called DoTheImport (which is the last code, used for specifying files and separators). The directions say this code will call the other code which is called ImportTextFile but instead of calling it, the error message keeps reading:

compile error ambiguous name detected: ImportTextFile

when I run the DoTheImport code. What do I need to do so that this procedure works correctly?? I pasted the code from the website for reference below.

thanks much in advance!!!




''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ImportTextFile
' This imports a text file into Excel.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
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.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)
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 ImportTextFile
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End Sub
Since ImportTextFile takes input parameters, it should be called from other VBA code:

Sub DoTheImport()
ImportTextFile FName:="C:\Test.txt", Sep:="|"
End Sub
In this code, both the file name and the separator are hard coded into the code. If you want to prompt the user for a file name and separator character, use code like the following:
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DoTheImport
' This prompts the user for a FileName as separator character
' and then calls ImportTextFile.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub DoTheImport()
Dim FileName As Variant
Dim Sep As String
FileName = Application.GetOpenFilename(FileFilter:="Text File (*.txt),*.txt")
If FileName = False Then
''''''''''''''''''''''''''
' user cancelled, get out
''''''''''''''''''''''''''
Exit Sub
End If
Sep = Application.InputBox("Enter a separator character.", Type:=2)
If Sep = vbNullString Then
''''''''''''''''''''''''''
' user cancelled, get out
''''''''''''''''''''''''''
Exit Sub
End If
Debug.Print "FileName: " & FileName, "Separator: " & Sep
ImportTextFile FName:=CStr(FileName), Sep:=CStr(Sep)
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END DoTheImport
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 

[wigi]

Welcome on Board.

This is the code that you need - you should not just copy paste code without going through the code and make sure you understand what you are doing:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ImportTextFile
' This imports a text file into Excel.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
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.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)
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 ImportTextFile
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End Sub

Sub DoTheImport()
ImportTextFile FName:="C:\Test.txt", Sep:="|"
End Sub