file save as tab delimited change to direct file save

All,

A co-worker inherited a process to create a daily file that gets uploaded into email. Currently the macro prompts the user to click on okay to save the file. I'm not sure how to make the macro to just save the file. It gets written to the same file name each day. The filename is "call_data.txt". I'm not sure why they set it up this way. Everything, I've tried hasn't worked. Any help would be greatly appreciated. I'm not sure why they're testing for "Win".

Here's the exiting code that I want to change to just save the file.

If Left(Application.OperatingSystem, 3) = "Win" Then
SaveFileName = Application.GetSaveAsFilename(CurFile, _
"Text Delimited (*.txt), *.txt", , "Text Delimited Exporter")
Else
SaveFileName = Application.GetSaveAsFilename(CurFile, _
"TEXT", , "Text Delimited Exporter")
End If

Thanks in advance.

Brian

Tony,

Hey. I've been side tracked and trying to figure it out myself, but I'm back.

"changed the line to,
"Workbook("xxx").SaveAs Filename:="c:\test\tulanesss.csv", FileFormat:=xlTextMSDOS, CreateBackup:=False"

With that line I get "sub or function not defined". If I used "ActiveWorkbook", I get "run time error 438". I'm not sure who created this macro, but I'm not sure why they put all this fluff in. Thanks for any help.

Okay, I'm now getting a "Run time error 9 subscript out of range" error. I'm assuming I'm missing a "Dim", but am not sure what I'm missing. I set up a DIM saveas, but that's obviously not what I'm needing. Any help as always, is appreciated. Thanks!

Sorry about that, thanks for all your help. Here's the code. I've put the line in question in bold.

   
   Function WriteFile(delimiter As String, quotes As Integer) As String
   
   ' Dimension variables to be used in this function.
   Dim CurFile As String
   Dim SaveFileName
   Dim CellText As String
   Dim RowNum As Integer
   Dim ColNum As Integer
   Dim FNum As Integer
   Dim TotalRows As Integer
   Dim TotalCols As Integer
   Dim saveas
   
   
   ' Show Save As dialog box with the .TXT file name as the default.
   ' Test to see what kind of system this macro is being run on.
   If Left(Application.OperatingSystem, 3) = "Win" Then
 '     SaveFileName = Application.GetSaveAsFilename(CurFile, _
 '     "Text Delimited (*.txt), *.txt", , "Text Delimited Exporter")
 Workbooks("xxx").saveas Filename:="c:\test\tulanesss.txt", FileFormat:=xlTextMSDOS, CreateBackup:=False
   Else
       SaveFileName = Application.GetSaveAsFilename(CurFile, _
      "TEXT", , "Text Delimited Exporter")
   End If
 
 
   
   ' Check to see if Cancel was clicked.
  '    If SaveFileName = False Then
  '       WriteFile = "Canceled"
  '       Exit Function
  '    End If
   ' Obtain the next free file number.
      FNum = FreeFile()
   
   ' Open the selected file name for data output.
      Open SaveFileName For Output As #FNum
   
   ' Store the total number of rows and columns to variables.
      TotalRows = Selection.Rows.Count
      TotalCols = Selection.Columns.Count
   
   ' Loop through every cell, from left to right and top to bottom.
      For RowNum = 1 To TotalRows
         For ColNum = 1 To TotalCols
            With Selection.Cells(RowNum, ColNum)
            Dim ColWidth As Integer
            ColWidth = Application.RoundUp(.ColumnWidth, 0)
            ' Store the current cells contents to a variable.
            Select Case .HorizontalAlignment
               Case xlRight
                  CellText = Space(Abs(ColWidth - Len(.Text))) & .Text
               Case xlCenter
                  CellText = Space(Abs(ColWidth - Len(.Text)) / 2) & .Text & _
                             Space(Abs(ColWidth - Len(.Text)) / 2)
               Case Else
                  CellText = .Text & Space(Abs(ColWidth - Len(.Text)))
            End Select
            End With
   ' Write the contents to the file.
   ' With or without quotation marks around the cell information.
            Select Case quotes
               Case vbYes
                  CellText = Chr(34) & CellText & Chr(34) & delimiter
               Case vbNo
                  CellText = CellText & delimiter
            End Select
            Print #FNum, CellText;
   
   ' Update the status bar with the progress.
            Application.StatusBar = Format((((RowNum - 1) * TotalCols) _
               + ColNum) / (TotalRows * TotalCols), "0%") & " Completed."
   
   ' Loop to the next column.
         Next ColNum
   ' Add a linefeed character at the end of each row.
         If RowNum <> TotalRows Then Print #FNum, ""
   ' Loop to the next row.
      Next RowNum
       
   ' Close the .prn file.
      Close #FNum
   
   ' Reset the status bar.
      Application.StatusBar = False
      'WriteFile = "Exported"
      
   End Function

bump to get this back in a recent list of messages to view.