Creating a new CSV file using VBA code

[volabos]

Dear all, Here I have following VBA code :

[VBA]Sub calculation()
Dim test(1 To 5, 1 To 5) As Double
Dim i, j As Integer
For i = 1 To 5
For j = 1 To 5
test(i, j) = 5
Next
Next

end sub[/VBA]

Here I want to save the above "test" array into a CSV file, which itself needs to be generated on the fly once the sub "calculation" is being executed. Can anyone please tell me how to do that? Your help will be highly appreciated.

Thanks

[mdmackillop]

Simplest to write to a new sheet and save that
[VBA]
Sub calculation()
Dim test(1 To 5, 1 To 5) As Double
Dim i, j As Integer
Dim sh As Worksheet
Dim Pth As String

Pth = ActiveWorkbook.Path
Set sh = Sheets.Add
For i = 1 To 5
For j = 1 To 5
sh.Cells(i, j) = 5
Next
Next
sh.Move
ActiveWorkbook.SaveAs Filename:=Pth & "\Test.csv", FileFormat:=xlCSV
End Sub

[/VBA]

[volabos]

Thanks for this reply. This is working fine. However here "test" becomes visible and it becomes the active workbook. Is there any trick to save "test" in some invisible way and keep existing workbook as active?

[lucas]

Try savecopyas instead of saveas

[mdmackillop]

[VBA]Sub calculation()
Dim test(1 To 5, 1 To 5) As Double
Dim i, j As Integer
Dim sh As Worksheet
Dim Pth As String

Application.ScreenUpdating = False

Pth = ActiveWorkbook.Path
Set sh = Sheets.Add
For i = 1 To 5
For j = 1 To 5
sh.Cells(i, j) = 5
Next
Next
sh.Move
With ActiveWorkbook
.SaveAs Filename:=Pth & "\Test1.csv", FileFormat:=xlCSV
.Close False
End With
Application.ScreenUpdating = True
End Sub[/VBA]

[volabos]

Thanks it worked.