Member Avatar for Joe Hart

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,

Joe

Sub QuoteCommaExport()
'Pushes all Selected Items to ATCTEST
   ' Dimension all variables.
    Sheets("SHEET2").Select
    Range("A2:CV72").Select
   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
   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,
         Else
            ' 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
  • 2 Contributors
  • 2 Replies
  • 121 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Joe Hart

Dani AI

Generated

— the simplest fix is to iterate columns on the outside and open one output file per column, then write every row in that column to that file. was correct to suggest reversing the loops. Also use string concatenation with & and subtract 1 from the column index so column A becomes Firm_ATC0.asc, B -> Firm_ATC1.asc, etc. Note: A..CV is 100 columns (so file suffixes 0..99).

Here is a compact VBA approach that uses the FileSystemObject (avoids fiddling with FreeFile/Open/Print semantics):

Sub ExportEachColumnToFiles()
    Dim ws As Worksheet, rng As Range
    Dim fso As Object, outF As Object
    Dim r As Long, c As Long
    Dim folder As String, fname As String

    Set ws = ThisWorkbook.Worksheets("SHEET2")
    Set rng = ws.Range("A2:CV72")
    folder = "C:\HourlyATC\"

    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FolderExists(folder) Then
        MsgBox "Folder not found: " & folder: Exit Sub
    End If

    For c = 1 To rng.Columns.Count
        fname = folder & "Firm_ATC" & (c - 1) & ".asc"
        Set outF = fso.OpenTextFile(fname, 2, True)   '2 = ForWriting
        For r = 1 To rng.Rows.Count
            outF.WriteLine rng.Cells(r, c).Text
        Next r
        outF.Close
    Next c
End Sub

Troubleshooting tips:

  • Confirm the output folder exists and you have write permission.
  • Use (c - 1) to get a 0-based filename suffix.
  • If keeping CSV formatting or quoting is required, build each line explicitly before writing.
  • For large ranges, disable ScreenUpdating and auto-calculation to speed the macro.
  • If you prefer the old Open/Print style, call FreeFile inside the column loop so each file gets a fresh handle.

Recommended Answers

All 2 Replies

Member Avatar for Member #857553

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
    Next
Next

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.

Member Avatar for Joe Hart

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.