In the following code, a selection in excel is selected. All of this information is then transfered to The DestFile. In this case the DestFile is Firm_ATC0.asc. I would like the program to put all the information from the A colum in the Firm_ATC0.asc file, and all the information in the B column in the Firm_ATC1.asc file and all the C column to go in the Firm_ATC2.asc file ect... Basically every time the column increments by 1 the Filename it is going to increments by 1. There are A to CV Columns selected in this example, and I think that is 99 columns and therfore there are Firm_ATC0.asc-Firm_ATC99.asc. Currently the information only goes into the one file.
Thanks for the help in advance,


Sub QuoteCommaExport()
'Pushes all Selected Items to ATCTEST
   ' Dimension all variables.
   Dim DestFile As String
      Dim FileNum As Integer
   Dim ColumnCount As Integer
   Dim RowCount As Integer
    DestFile = "C:\HourlyATC\Firm_ATC0.asc"
   ' Obtain next free file handle number.
   FileNum = FreeFile()

   ' Turn error checking off.
   On Error Resume Next

   ' Attempt to open destination file for output.
   Open DestFile For Output As #FileNum

   ' If an error occurs report it and end.
   If Err <> 0 Then
      MsgBox "Cannot open filename " & DestFile
   End If

   ' Turn error checking on.
   On Error GoTo 0

   ' Loop for each row in selection.
   For RowCount = 1 To Selection.Rows.Count

      ' Loop for each column in selection.
      For ColumnCount = 1 To Selection.Columns.Count

         ' Write current cell's text to file with quotation marks.
         Print #FileNum, "" & Selection.Cells(RowCount, _
            ColumnCount).Text & "";

         ' Check if cell is in last column.
         If ColumnCount = Selection.Columns.Count Then
            ' If so, then write a blank line.
            Print #FileNum,
            ' Otherwise, write a comma.
            Print #FileNum, ;
         End If
      ' Start next iteration of ColumnCount loop.
      Next ColumnCount
   ' Start next iteration of RowCount loop.
   Next RowCount

   ' Close destination file.
   Close #FileNum
 Application.Run "BAT"

End Sub

Try reversing your for statements

For columncount = 1 to Selection.Columns.Count
    'Change and open your file here.
    'Firm_ATC & ColumnCount - 1
    For RowCount = 1 to Selection.Rows.Count
       'Write all the rows for the current column to the current file

You may need to modify the Selection.Rows and Selection.Columns. Don't know what selection is but, I'm sure you can iterate through each row of each column instead of all rows of each column.

Thanks for your reply, but I am not sure how exactly to do what you said. I am a novice programer. A couple of questions I am having trouble with. How do I get the destfile to increment? DestFile = "C:\HourlyATC\Firm_ATC" + ColumnCount + ".asc"?

This article has been dead for over six months. Start a new discussion instead.