Error Message: ‘Argument Not optional’

[ryguy7272]

This is probably something easy, but when you don’t know what to do it is
hard. Basically, I copied Chip’s code form the following URL:

http://www.cpearson.com/excel/imptext.htm#Export

Then I run the code and the get an ‘Argument Not optional’ message. The
following line is tinted blue:
ExportToTextFile

I looked at some DG topics that discussed ‘Argument Not optional’ but didn’t
get anywhere. I Googled the topic, and tried to figure out what is going
on...nothing jumped out at me. Basically, I didn’t find anything that seemed
helpful. I also looked under Tools > References (I had my doubts about this,
but tried it anyway). I clicked on Export 1.0 Type Library; it still doesn’t
work.

Typically, I am resourceful, and try to resolve issues by myself, but I’ve
hit a wall, and don’t 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!

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Tom Ogilvy]

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

 

[Rick Rothstein \(MVP - VB\)]

Chips example on calling the function:

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

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

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

Rick

 

[ryguy7272]

Thanks for getting back to me Rick and Tom. Sorry for my ineptitude, but I’m
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’t actually create a file; it just
runs and then ends. I must be remiss; I must still be missing something...
Any additional thoughts?

 

[Dave Peterson]

This is the main routine. It's the one that kicks everything off:

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")

'and this is the statement that calls the routine that does all the work.
'it doesn't belong in the other subroutine
ExportToTextFile "c:\temp\test.txt", Sep, FALSE, FALSE

End Sub

'and Chip's second routine can go here:

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

End Sub

 

[RyGuy]

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

 

[Chip Pearson]

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)

 

[Dave Peterson]

Chip,

At some time, will you make the old page redirect to the new page automatically?

It'll make it slightly easier for google searchers and copy|paste posters (like
me!).

Ps. I just read your "before you email me" page.
http://www.cpearson.com/excel/EmailMe.aspx

And thought that you may want to provide a note to try the newsgroups/MS
Communities or even search google before contacting you.

 

[Chip Pearson]

At some time, will you make the old page redirect to the new page

automatically?

At present, I'm just putting a note and link at the top of the superseded
pages pointing to the new page. Once I get the bulk of the pages converted
(it is a big job -- over 200 pages to convert) each html page will
automatically redirect to the corresponding aspx page. All aspx pages will
have the same name as the original html page, only with an 'aspx' extension
instead of an 'htm' extension. Due to the number of links pointing to my
pages as well as the Google Groups archives, the html pages will never go
away. Once an aspx page has been created, I will no longer update the html
page, but the html page will continue to be available.

I just changed web hosting services (more features, space, and bandwidth for
less money than the old provider) so I have space to keep two version of the
site running, the html version and the aspx version.


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

 

[Dave Peterson]

Thanks for the info.

I looked at the two ImpText pages and didn't see a compelling reason to keep the
..html version -- except for redirection to the newer .aspx page. (At least in
this case.)

I would think it would be better for me, er, the typical user if the .hmtl
content were deleted--and just leave the link (or automatic redirection to the
newer page).

Obviously, the choice is yours, but someday, you're gonna find an error on one
of the .html pages that you just can't live with and you'll be updating that,
too. (I'd put money on it!)

At some time, will you make the old page redirect to the new page
automatically?

At present, I'm just putting a note and link at the top of the superseded
pages pointing to the new page. Once I get the bulk of the pages converted
(it is a big job -- over 200 pages to convert) each html page will
automatically redirect to the corresponding aspx page. All aspx pages will
have the same name as the original html page, only with an 'aspx' extension
instead of an 'htm' extension. Due to the number of links pointing to my
pages as well as the Google Groups archives, the html pages will never go
away. Once an aspx page has been created, I will no longer update the html
page, but the html page will continue to be available.

I just changed web hosting services (more features, space, and bandwidth for
less money than the old provider) so I have space to keep two version of the
site running, the html version and the aspx version.

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

 

[Chip Pearson]

Dave,

I think you're absolutely right about deleting the HTML content and leaving
only the redirect. I'll do that sooner rather than later, maybe even today.


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

Thanks for the info.

I looked at the two ImpText pages and didn't see a compelling reason to
keep the
.html version -- except for redirection to the newer .aspx page. (At
least in
this case.)

I would think it would be better for me, er, the typical user if the .hmtl
content were deleted--and just leave the link (or automatic redirection to
the
newer page).

Obviously, the choice is yours, but someday, you're gonna find an error on
one
of the .html pages that you just can't live with and you'll be updating
that,
too. (I'd put money on it!)

 

[Dave Peterson]

It always made me cry when I had to support multiple versions of the same thing.

Dave,

I think you're absolutely right about deleting the HTML content and leaving
only the redirect. I'll do that sooner rather than later, maybe even today.

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