How to export selected range using SaveAs method

[alwayslrN]

I have searched the web and this site to try and understand and use the SaveAs method. I used the following code, but the problem I have with it is it creates the csv for the whole workbook and not for the range I have selected. How can I change it to use a selected range?

ThisWorkbook.Worksheets("REMITTANCE").SaveAs Filename:="C:\Remittance\testremit.csv", FileFormat:=xlCSV

My range

Sub SelectRemitRange()
    Dim RemitLastRow As Long
    Dim RemitLast As Integer
    
    ThisWorkbook.Worksheets("REMITTANCE").Activate
    
    RemitLastRow = Cells(Cells.Rows.Count, "b").End(xlUp).Row
    RemitLast = RemitLastRow
    Range("A1" & ":X" & RemitLast).Select
    
End Sub

I did find some code on another site that required a whole lot more lines of code and does not use the SaveAs method if I cannot use the SaveAs method.

Thanks

 

[PHV]

You may copy your range in a brand new sheet you may save as csv.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[alwayslrN]

I PH - I meant to say it saves the whole worksheet NOT workbook. So in the file after the lines of data is a bunch of commas on each line. I suspect all the way down to the last row in the worksheet.

The website I made reference to earlier is

http://www.cpearson.com/excel/imptext.htm.

The code actually works for me I just need to combine information and get rid of some code because I do not to open a file and ask for the deliminator and such.

 

[PHV]

Seems you didn't understand my suggestion ?
Typed, untested:
Dim s As Worksheet
Set s = ThisWorkbook.Worksheets.Add
With ThisWorkbook.Worksheets("REMITTANCE")
.Range("A1:X" & .Cells(.Cells.Rows.Count, "b").End(xlUp).Row).Copy s.Range("A1")
End With
s.SaveAs Filename:="C:\Remittance\testremit.csv", FileFormat:=xlCSV
ThisWorkbook.Worksheets(s.Name).Delete

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[alwayslrN]

Hi PH,

You are so right, I did not understand your suggestion.

I will have to give that a try. Though in the mean time I did get the code from the cpearson site to work to my liking.

Thanks for your help.

 

[alwayslrN]

Thanks PH - that worked, but after giving the method a test, it will better if I were to copy the range into a new "workbook" because saving it from the active workbook closes out my workbook with the macro which I need to have available. Or, I could just use the other code I found.

Regardless, at least I now know my options and how to get the method to work for a selected range.

 

[alwayslrN]

Oh yes, the other issue is cells P to X are blank and I need those to be empty comma delimated in the file. Using the SaveAs method, these empty columns do not get picked up in the file eventhough they are selected (at least I think they are). Using the code below, it works like a charm.

Public Sub ExportToTextFile()

'FName As String, Sep As String, SelectionOnly As Boolean

Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String
Dim FName As Variant
Dim Sep As String

Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

'If SelectionOnly = True Then
    With Selection
        StartRow = .Cells(1).Row
        StartCol = .Cells(1).Column
        EndRow = .Cells(.Cells.Count).Row
        EndCol = .Cells(.Cells.Count).Column
    End With
'Else
'    With ActiveSheet.UsedRange
'        StartRow = .Cells(1).Row
'        StartCol = .Cells(1).Column
'        EndRow = .Cells(.Cells.Count).Row
'        EndCol = .Cells(.Cells.Count).Column
'    End With
'End If

FName = "C:\Remittance\testremit.csv"
Sep = ","

Open FName For Output Access Write As #FNum

For RowNdx = StartRow To EndRow
    WholeLine = ""
    For ColNdx = StartCol To EndCol
        If Cells(RowNdx, ColNdx).Value = "" Then
            CellValue = ""
        Else
           CellValue = Cells(RowNdx, ColNdx).Text
        End If
        WholeLine = WholeLine & CellValue & Sep
    Next ColNdx
    WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
    Print #FNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum

End Sub