=?Utf-8?Q?_Error_Message:__=E2=80=98Argument_No?= =?Utf-8?Q?t_optional=E2=80=99?=

=?Utf-8?Q?_Error_Message:__=E2=80=98Argument_No?= =?Utf-8?Q?t_optional=E2=80=99?=

Post by MVP - » Mon, 09 Jul 2007 02:22:42


The subroutine that Chip provided has this declaration header...

Public Sub ExportToTextFile(FName As String, _
Sep As String, SelectionOnly As Boolean, _
AppendData As Boolean)

Notice the four items in parentheses? They are required arguments that must
be provided when you call the subroutine. Chip describes what each one of
these arguments are in the text immediately above the code on his webpage.
You need to provide these arguments when you call the subroutine. The error
message you got is telling you the arguments you didn't provide are
required. The reason it used the wording "Argument not optional" is because
an argument can be designated as being Optional, in which case it could have
been left out of the subroutine call (but Chip did **not** do this with any
of his arguments; hence, they must all be provided).

Rick




> Then I run the code and the get an rgument Not optionalmessage. The >> following line is tinted blue: >> ExportToTextFile >> >> I looked at some DG topics that discussed rgument Not optionalbut didn> > get anywhere. I Googled the topic, and tried to figure out what is going> > on...nothing jumped out at me. Basically, I didn find anything that > > seemed> > helpful. I also looked under Tool> > References (I had my doubts about > > this,> > but tried it anyway). I clicked on Export 1.0 Type Library; it still > > doesn> > work.> >> > Typically, I am resourceful, and try to resolve issues by myself, but Ie> > hit a wall, and don know what to do now. I thought I was just starting > > to> > get somewhat proficient with this VBA stuff and then something that should > > be> > extremely simple (can you say copy/paste) vexes me. Can anyone offer some> > insight or give me some kind of guidance> >> > Thanks so much!> >> > -- > > RyGuy
 
 
 

=?Utf-8?Q?_Error_Message:__=E2=80=98Argument_No?= =?Utf-8?Q?t_optional=E2=80=99?=

Post by VG9tIE9naW » Mon, 09 Jul 2007 02:52:01

Chips example on calling the function:

ExportToTextFile "c:\temp\test.txt", ";" , FALSE

appears to be in error or outdated. The function requires 4 arguments

Public Sub ExportToTextFile( _
FName As String, _
Sep As String, _
SelectionOnly As Boolean, _
AppendData As Boolean)


so that example should be

ExportToTextFile "c:\temp\test.txt", ";" , FALSE, FALSE



--
Regards,
Tom Ogilvy




> Then I run the code and the get an rgument Not optionalmessage. The >> following line is tinted blue: >> ExportToTextFile >> >> I looked at some DG topics that discussed rgument Not optionalbut didn > > get anywhere. I Googled the topic, and tried to figure out what is going > > on...nothing jumped out at me. Basically, I didn find anything that seemed > > helpful. I also looked under Tool> > References (I had my doubts about this, > > but tried it anyway). I clicked on Export 1.0 Type Library; it still doesn > > work. > > > > Typically, I am resourceful, and try to resolve issues by myself, but Ie > > hit a wall, and don know what to do now. I thought I was just starting to > > get somewhat proficient with this VBA stuff and then something that should be > > extremely simple (can you say copy/paste) vexes me. Can anyone offer some > > insight or give me some kind of guidance> > > > Thanks so much!> > > > -- > > RyGuy

 
 
 

=?Utf-8?Q?_Error_Message:__=E2=80=98Argument_No?= =?Utf-8?Q?t_optional=E2=80=99?=

Post by MVP - » Mon, 09 Jul 2007 03:24:27

> Chips example on calling the function:

Yeah, I noticed that and wrote to him privately about it maybe an hour ago.

Rick
 
 
 

=?Utf-8?Q?_Error_Message:__=E2=80=98Argument_No?= =?Utf-8?Q?t_optional=E2=80=99?=

Post by cnlndXk3Mj » Mon, 09 Jul 2007 09:22:00

Thanks for getting back to me Rick and Tom. Sorry for my ineptitude, but I
still not getting it. Below is the code that I have now:

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) And (Dir(FName, vbNormal) <> vbNullString) 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).Text
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

ExportToTextFile "c:\temp\test.txt", ";" , FALSE, FALSE

End Sub


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

FName = Application.GetSaveAsFilename()
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If

Sep = InputBox("Enter a single delimiter character (e.g., comma or
semi-colon)", _
"Export To Text File")
End Sub


The error is gone now, but the code doesn actually create a file; it just
runs and then ends. I must be remiss; I must still be missing something...
Any additional thoughts?



--
RyGuy
 
 
 

=?Utf-8?Q?_Error_Message:__=E2=80=98Argument_No?= =?Utf-8?Q?t_optional=E2=80=99?=

Post by UnlHdX » Mon, 09 Jul 2007 13:46:02

hat was a lot harder for me than it should have been.
Thanks Rick, Tom, and Dave.

"Dave Peterson" wrote:

 
 
 

=?Utf-8?Q?_Error_Message:__=E2=80=98Argument_No?= =?Utf-8?Q?t_optional=E2=80=99?=

Post by Chip Pears » Tue, 10 Jul 2007 06:56:09

Yes, the example code on my web page was in error. I wrote the code years
ago and only very recently added the 4th parameter, which indicates whether
to append the data to an existing output file (AppendData:=True) or to purge
the output file before writing to it (AppendData:=False). I updated the
procedure code but neglected to update the examples. I have updated the page
with the correct example code. I considered making it an Optional parameter
but didn't because I felt that something as important as preserving or
killing off data shouldn't be left as a default behavior. For safety's sake,
I wanted the user to have to explicitly state whether to kill off a data
file.

That said, I'm converting my entire site from simple and crude HTML to fully
compliant XHTML 1.1 Strict with ASP.NET 2.0, and that particular page has
been superseded by the page www.cpearson.com/excel/imptext.aspx . The aspx
page had the correct example code all along.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)




> Then I run the code and the get an rgument Not optionalmessage. The >> following line is tinted blue: >> ExportToTextFile >> >> I looked at some DG topics that discussed rgument Not optionalbut didn> > get anywhere. I Googled the topic, and tried to figure out what is going> > on...nothing jumped out at me. Basically, I didn find anything that > > seemed> > helpful. I also looked under Tool> > References (I had my doubts about > > this,> > but tried it anyway). I clicked on Export 1.0 Type Library; it still > > doesn> > work.> >> > Typically, I am resourceful, and try to resolve issues by myself, but Ie> > hit a wall, and don know what to do now. I thought I was just starting > > to> > get somewhat proficient with this VBA stuff and then something that should > > be> > extremely simple (can you say copy/paste) vexes me. Can anyone offer some> > insight or give me some kind of guidance> >> > Thanks so much!> >> > -- > > RyGuy