Saveas CSV from Excel in VBA

[MCubitt]

I have a workbook with 8 worksheets.

At the click of a button I'd like to save sheets 1, 4,5,6,7,8 as csv to the same location of the excel file.

Is this possible? If so, how - please?

Thanks

There's no need for sarcastic replies, we've not all been this sad for that long!

 

[LeighAnne]

Have you tried using the createtextfile and writeline command?

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.createtextfile("c:\yourlocation\test.csv", True, True)
TextString = Empty
For Row = 1 To 100
For Col = 1 To 26
TextString = TextString & "," & ActiveWorkbook.Sheets("Sheet1").Cells(Row, Col).Value
Next
a.writeline (TextString)
Next
a.Close

 

[MCubitt]

LeighAnne,

DOh! Since moving from VBS to VBA in the past week (the script, and myself!) I had forgotten the basics!

Thanks for reminding me and for the script, saves my remaining brain cell(s)!

regards

There's no need for sarcastic replies, we've not all been this sad for that long!

 

[MCubitt]

Right.. you KNEW I'd be back, didn't you?

Using teh following sub, everything appears to work except one thing.. no file (that I can find) is generated.

    ' Sheet 1. from row 1 to 2000, col 1 to 100
    Call writecsv(1, 1, 2000, 1, 100)
    ' Sheet 4. from row 1 to 65535 col 1 to 6
    Call writecsv(4, 1, 65535, 1, 6)

.
.
.

Private Sub writecsv(sheet, startrow, endrow, startcol, endcol)
    file = Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1) + "_" + Sheets(sheet).Name + ".csv"
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set file = FSO.createtextfile(file, True, True)
    For Row = startrow To endrow
      If ActiveWorkbook.Sheets(sheet).Cells(Row, 3).Value <> "" Then
        textstring = Empty
        For col = startcol To endcol
          textstring = textstring & "," & ActiveWorkbook.Sheets(sheet).Cells(Row, col).Value
        Next
        file.writeline (textstring)
      End If
    Next
    file.Close
End Sub

Any ideas? FYI there are spaces in my book and sheet names.

Thanks

There's no need for sarcastic replies, we've not all been this sad for that long!

 

[LeighAnne]

My fault! I forgot to point out that you'll have to put the comma's in the file to make it csv. Change the For-Next loop to somethign like this:

For Col = startcol To endcol
If Col = 1 Then
TextString = ActiveWorkbook.Sheets(sheet).Cells(Row, Col).Value
Else
TextString = TextString & "," & ActiveWorkbook.Sheets(sheet).Cells(Row, Col).Value
End If
Next

 

[MCubitt]

Surely it should have worked, even if it was a single column output? Well spotted anyway!

There is no output file generated with my script... not in the "current folder" anyway!

Private Sub writecsv(sheet, startrow, endrow, startcol, endcol)
    file = Trim(Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1) + "_" + Sheets(sheet).Name + ".csv")
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set file = FSO.createtextfile(file, True, True)
    For Row = startrow To endrow
      If ActiveWorkbook.Sheets(sheet).Cells(Row, 3).Value <> "" Then
        textstring = Empty
        For col = startcol To endcol
          If col > 1 Then textstring = textstring & ","
          textstring = textstring & ActiveWorkbook.Sheets(sheet).Cells(Row, col).Value
        Next
        file.writeline (textstring)
      End If
    Next
    file.Close
End Sub

I checked the value of file before the set file = fso...
and it is:
New User to IFS Core_Users to add to core.csv

..thought trim would have worked.. but I think that only removes leading/trailing spaces and within excel itself it removes innards.



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[MCubitt]

Ok, it's stored the file in the ROOT of the local drive, not current path (where Excel was opened from).

Is there a way I can fix that, do you think?



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[LeighAnne]

When I ran the script, the file that was generated did not appear in the same folder, it appears in your default folder (for most people this is C:\My Documents or C:\Documents and Settings).

Just for testing purposes you could hard code a path and file name so that you know exactly where it should appear.

i.e. Replace:

file = Trim(Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1) + "_" + Sheets(sheet).Name + ".csv")

with

file = "c:\desktop\my_test1.csv"

 

[MCubitt]

SOLVED IT!!!

The line I needed was...

Filename = ActiveWorkbook.Path & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1) + "_" + Sheets(sheet).Name + ".csv"

Thanks for your help, anyway



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[LeighAnne]

Glad you got it sorted in the end!

 

[MCubitt]

Oops, I mean...

    Filename = ActiveWorkbook.Path & "\" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1) + "_" + Sheets(sheet).Name + ".csv"

The / might help!!!

There's no need for sarcastic replies, we've not all been this sad for that long!

 

[MCubitt]

Sorry, just to complete this thread, a small correction.

Set file = FSO.createtextfile(file, True, True)

should be

Set file = FSO.createtextfile(file, True, False)

or

Set file = FSO.createtextfile(file, True)

So it writes as ASCII and then Excel opens the CSV as an Excel file.

Thanks very much LeighAnn.

regards


There's no need for sarcastic replies, we've not all been this sad for that long!