Solved: Compile error: sub or function not defined

[xena2305]

Hi,
I have a code that exports sheet to a text file and it works fine on my home pc with excel 2007.
when i run it on my work pc i get the above error, both in excel 2003 and 2007.
i found few posts in google about this issue but so far none of these helped me.
this is the first part of the code where i get the error:

[VBA]Sub DoTheExport()
Dim FileName As Variant
Dim Sep As String
FileName = Application.GetSaveAsFilename(InitialFileName:=vbNullString, FileFilter:="Text Files (*.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
ExportToTextFile FName:=CStr(FileName), Sep:=CStr(Sep), _
SelectionOnly:=False, AppendData:=False
End Sub[/VBA]

[Bob Phillips]

Sounds as though you don't have a procedure called ExportToTetxFile on those machines.

[Bob Phillips]

Just Googled that, and it looks like a procedure Chip Perason wrote, you can get it at http://www.cpearson.com/excel/ImpText.aspx

[xena2305]

thnx for the fast reply.
i did copy it from the cpearson site i just couldnt attach the link.
like i mentioned i run this code on my pc without any issue, only
in the transfer to my work pc something goes wrong.

[VBA]Public Sub ExportToTextFile(FName As String, _
Sep As String, SelectionOnly As Boolean, _
AppendData As Boolean)
Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String
Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile
If SelectionOnly = True Then
With Selection
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With
Else
With ActiveSheet.UsedRange
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With
End If
If AppendData = True Then
Open FName For Append Access Write As #FNum
Else
Open FName For Output Access Write As #FNum
End If
For RowNdx = StartRow To EndRow
WholeLine = ""
For ColNdx = StartCol To EndCol
If Cells(RowNdx, ColNdx).Value = "" Then
CellValue = Chr(34) & Chr(34)
Else
CellValue = Cells(RowNdx, ColNdx).Value
End If
WholeLine = WholeLine & CellValue & Sep
Next ColNdx
WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
Print #FNum, WholeLine
Next RowNdx
EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum
End Sub[/VBA]

[Bob Phillips]

Yeah, but where do you have it on the one machine? Is it on the others too?

[xena2305]

Yeah, but where do you have it on the one machine? Is it on the others too?

The 2 parts of the code are together in one module on my home pc
and my work pc, they are not separate.

[Aussiebear]

Which line does it error out on?

[xena2305]

Which line does it error out on?

i get a yellow mark and an arrow on the first line "Sub DoTheExport() "

btw, i tried to google this issue and many posts suggest to
add "worksheetfunction" to some functions to point that its a worksheet function and not vba, so far this hasnt worked for me.

i will mention again that this code works very good on my pc, maybe this can point the problem.

[Bob Phillips]

What is the code that calls that procedure?

[xena2305]

What is the code that calls that procedure?

this is the full code, the first part calls the procedure at the second part if thats what you ment.

you can view the original code in cpearson.com/excel/ImpText.aspx

[vba]
Sub DoTheExport()
ExportToTextFile FName:="C:\Test.txt", Sep:=";", _
SelectionOnly:=False, AppendData:=True
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ExportToTextFile
' This exports a sheet or range to a text file, using a
' user-defined separator character.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub ExportToTextFile(FName As String, _
Sep As String, SelectionOnly As Boolean, _
AppendData As Boolean)

Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String


Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

If SelectionOnly = True Then
With Selection
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With
Else
With ActiveSheet.UsedRange
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With
End If

If AppendData = True Then
Open FName For Append Access Write As #FNum
Else
Open FName For Output Access Write As #FNum
End If

For RowNdx = StartRow To EndRow
WholeLine = ""
For ColNdx = StartCol To EndCol
If Cells(RowNdx, ColNdx).Value = "" Then
CellValue = Chr(34) & Chr(34)
Else
CellValue = Cells(RowNdx, ColNdx).Value
End If
WholeLine = WholeLine & CellValue & Sep
Next ColNdx
WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
Print #FNum, WholeLine
Next RowNdx

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

End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END ExportTextFile


[/vba]

[dougbert]

Check out my post at: http://www.vbaexpress.com/forum/show...23&postcount=4

Your symptoms are identical to what I discovered when I received the same error.

If not, add Option Explicit and see if it points to your problem when you compile the code again.

-dougbert

[shrivallabha]

Check out my post at: http://www.vbaexpress.com/forum/show...23&postcount=4

Your symptoms are identical to what I discovered when I received the same error.

If not, add Option Explicit and see if it points to your problem when you compile the code again.

-dougbert

Yes, its a good suggestion. Maybe 3 weeks back, we were trying to run a macro (selecting through listbox) and it would give above error. So I placed the macro in a Module1 and then referred it explicitly like:
[VBA]Module1.MyMacro1[/VBA]
And it worked!

[xena2305]

Check out my post at: http://www.vbaexpress.com/forum/show...23&postcount=4

Your symptoms are identical to what I discovered when I received the same error.

If not, add Option Explicit and see if it points to your problem when you compile the code again.

-dougbert

That help, many thanks for the reply's and help.