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

Originally Posted by Elijah

... Thus, it would be advantageous if the copy process could be done to the *.csv file without opening it ...

It is not possible, every file (disk file/collection/set) to which you want to save something must be opened from the disk in the appropriate mode (for writing), saved, then closed, this cannot be done otherwise.

Originally Posted by porucha vevrku

It is not possible, every file (disk file/collection/set) to which you want to save something must be opened from the disk in the appropriate mode (for writing), saved, then closed, this cannot be done otherwise.

Here, I assume you're referring to VBA specifically?

Because I have other software updating another *.csv file while it's closed.

If it is indeed not possible, I will try to add Open & Save&Close at beginning/end of my current macro.

I found some links here which should be helpful, but won't have time until later tonight.

https://www.excel-easy.com/vba/examples/close-open.html

https://analysistabs.com/excel-vba/o...ting-workbook/

Regards.

Originally Posted by porucha vevrku

Not only, it's just your illusion. Every disk file, in order to write something in it, must first be opened with the appropriate mechanism of a given program, that does it. Only the GUI of this mechanism of this program is not always visible.

Understood.

Originally Posted by porucha vevrku

Did you look for it on the forum? A lot of this should be here.

Well, as you can see above, I was able to piece together 4 steps which does exactly what I want.

The problem is that when executing all 4 together, my *.csv file seems to be saved incorrectly. That is, the local settings on my computer are not used... I end up with all data in one column.

I've tried correcting the code and implementing it, but can't figure it out on my own...

But still working on it.

Any ideas?

So, I've tried this in order to force a close & save using my local settings and data split in separate columns. But it does not work...

Sub ClosePartDayCsv()

With Workbooks("Ninjatrader CSV.csv")
.SaveAs Filename:="Ninjatrader CSV.csv", FileFormat:=xlCSV, Local:=True
.Close SaveChanges:=False
End With

End Sub

Allright. It seems like that actually worked.

BUT, I didn't notice because it's the opening of the *.csv which causes me trouble and I've been using that method.

Any idea why?

More precisely:

1. If I open manually by clicking the *.csv file it opens correctly (data in separate columns) now when using my last sub above in post #9 to save the *.csv.

2. If I open using the single VBA code shown above, it opens incorrectly.

For my purposes, I don't need to open it with VBA correctly, so problem solved, I think. I only need to be stored correctly.