Can VBA copy data from an Excel Sheet to a *.csv workbook which is not open?

Hi,

I was able to set up a workbook with an associated macro which copies filtered data to a *.csv file. This one works like a charm. However, since I will be using this *.csv file as input on another program I need to close the *.csv file. Thus, it would be advantageous if the copy process could be done to the *.csv file without opening it.

Is this possible at all without too much trouble?

An alternative solution could be to add VBA code which 1) Opens *.csv file; 2) Copies data using my existing macro; 3) Close the *.csv file.

Any thoughts?

I use one macro for clearing the *.csv before making a copy:

Sub ClearCSV()
    Windows("NinjaTrader CSV.csv").Activate
    Columns("A:B").Select
    Selection.Delete Shift:=xlToLeft
    Windows("Main Sheet.xlsm").Activate
End Sub

And one macro for copying filtered data from the *.csv:

Sub CopyFilteredData()
Dim rng As Range
Dim res, ar
Dim coll As New Collection
For Each rng In ActiveWorkbook.ActiveSheet.ListObjects(1).DataBodyRange.Columns("A:B").SpecialCells(xlCellTypeVisible).Rows
    ar = rng.Value
    coll.Add ar
Next
ReDim res(1 To coll.Count, 1 To UBound(ar, 2))

For i = 1 To coll.Count
    For j = 1 To UBound(coll(i), 2)
        res(i, j) = coll(i)(1, j)
    Next
Next
Workbooks("Ninjatrader CSV.csv").Sheets("NinjaTrader CSV").Range("A1").Resize(UBound(res), UBound(res, 2)) = res

End Sub

I run both together using this:

Sub RunAllMacros()
ClearCSV
CopyFilteredData
End Sub

Best regards,

Elijah