Error message "Argument Not Optional" Please Help

[tsteele2000]

I'm coding a macro that is attached to a button. The macro should basically take a range of values from a specified sheet in the workbook (always the same so it's hardcoded) and export as a comma delimited text file to a new file.

I copied the code from another website and edited it and when I run it, I'm getting the above mentioned error with the first line of the macro highlighted. Am I overlooking something obvious? Please advise, it's been a long time since I'm used VBA. Here is the code (sorry it's kind of long):

Sub ExportAsDelimited(SourceWB As String, SourceWS As String, SourceAddress As String, _
TargetFile As String, SepChar As String, SaveValues As Boolean, ExportLocalFormulas As Boolean, AppendToFile As Boolean)

ExportAsDelimited ThisWorkbook.Name, "ExportSheet", "A1:K136", "C:\temp\MortgagebotImportFile.txt", ",", True, True, False
' Exports the data in Workbooks(SourceWB).Worksheets(SourceWS).Range(SourceAddress) to
' the textfile TargetFile in CSV format, uses SepChar as column delimiter
' Example: ExportRangeAsDelimitedText ThisWorkbook.Name, "ExportSheet", "A3:E23", "C:\FolderName\DelimitedText.txt", ";", True, True, False

Dim SourceRange As Range, SC As String * 1
Dim A As Integer, r As Long, c As Integer, totr As Long, pror As Long
Dim fn As Integer, LineString As String, tLine As String
' validate the input data if necessary
Workbooks(SourceWB).Activate
Worksheets(SourceWS).Activate
If Application.WorksheetFunction.CountA(Range(SourceAddress)) = 0 Then Exit Sub
If Not AppendToFile Then
If Dir(TargetFile) <> &quot;&quot; Then
On Error Resume Next
Kill TargetFile
On Error GoTo 0
If Dir(TargetFile) <> &quot;&quot; Then
MsgBox TargetFile & &quot; already exists, rename, move or delete the file before you try again.&quot;, vbInformation, &quot;Export range to textfile&quot;
Exit Sub
End If
End If
End If
If UCase(SepChar) = &quot;TAB&quot; Or UCase(SepChar) = &quot;T&quot; Then
SC = Chr(9)
Else
SC = Left(SepChar, 1)
End If

' perform export
Set SourceRange = Range(SourceAddress)
On Error GoTo NotAbleToExport
fn = FreeFile
Open TargetFile For Append As #fn ' open textfile for new input
On Error GoTo 0
' determine the total number of rows to process
totr = 0
For A = 1 To SourceRange.Areas.Count
totr = totr + SourceRange.Areas(A).Rows.Count
Next A
' start writing the character-separated textfile
pror = 0
For A = 1 To SourceRange.Areas.Count
For r = 1 To SourceRange.Areas(A).Rows.Count
LineString = &quot;&quot;
For c = 1 To SourceRange.Areas(A).Columns.Count
tLine = &quot;&quot;
On Error Resume Next
If SaveValues Then
tLine = SourceRange.Areas(A).Cells(r, c).Value
Else
If ExportLocalFormulas Then
tLine = SourceRange.Areas(A).Cells(r, c).FormulaLocal
Else
tLine = SourceRange.Areas(A).Cells(r, c).Formula
End If
End If
On Error GoTo 0
LineString = LineString & tLine & SC
Next c
pror = pror + 1
If pror Mod 50 = 0 Then
Application.StatusBar = &quot;Writing delimited textfile &quot; & Format(pror / totr, &quot;0 %&quot & &quot;...&quot;
End If
If Len(LineString) > 1 Then LineString = Left(LineString, Len(LineString) - 1)
If LineString = &quot;&quot; Then
Print #fn,
Else
Print #fn, LineString
End If
Next r
Next A
Close #fn ' close the textfile
NotAbleToExport:
Set SourceRange = Nothing
Application.StatusBar = False
End Sub

 

[RoyVidar]

when you say first line of the macro, do you mean:
Sub ExportAsDelimited(SourceWB As String, SourceWS As String, SourceAddress As String, _
TargetFile As String, SepChar As String, SaveValues As Boolean, ExportLocalFormulas As Boolean, AppendToFile As Boolean)?

Then the errormsg means you don't have a value in all the parameters. Be sure to either send values in all parameters OR declare them as:
&quot;...ExportAsDelimited(Optional SourceWB As String, ...&quot;

HTH Roy-Vidar

 

[tsteele2000]

Actually, I think I was mistaken - nothing is being highlighted when the error message appears.

I'm matching the arguments up and I'm not missing any:

Sub ExportAsDelimited(SourceWB As String, SourceWS As String, SourceAddress As String, _
TargetFile As String, SepChar As String, SaveValues As Boolean, ExportLocalFormulas As Boolean, AppendToFile As Boolean)

ExportAsDelimited ThisWorkbook.Name, &quot;ExportSheet&quot;, &quot;A1:K136&quot;, &quot;C:\temp\MortgagebotImportFile.txt&quot;, &quot;,&quot;, True, True, False

Matching them up:
SourceWB = ThisWorkbook.Name
SourceWS = &quot;ExportSheet&quot;
SourceAddress = &quot;A1:K136&quot;
TargetFile = &quot;C:\temp\MortgagebotImportFile.txt&quot;
SepChar = &quot;,&quot;
SaveValues = True
ExportLocalFormulas = True
AppendtoFile = False

Any other suggestions?

 

[RoyVidar]

OK, in your text, the line:

&quot;ExportAsDelimited ThisWorkbook.Name, &quot;ExportSheet&quot;, &quot;A1:K136&quot;, &quot;C:\temp\MortgagebotImportFile.txt&quot;, &quot;,&quot;, True, True, False&quot;

appears within within the sub it's calling. It should be within a &quot;button_click&quot; thingie, or you call the sub without arguments, and retrieve the values thru the sub.

Something should be highlited, either when you use Debug | Compile, or when you try to run it.

btw are you doing this thru Access or Excel (if the latter, the forum forum707 might be more correct)

Roy-Vidar