VBA range export to csv file

[Duncan23]

Hi,

I am trying to achieve two things with one macro, the first I have solved with the sub below, I now need to populate the file with only the range L2:L11 and not the rest of the data in the sheet, maintaining the rows so each cell contents are on a seperate row in the csv file:

Private Sub CommandButton1_Click()
Dim content As String
Dim rng As Range
Set rng = Range("L2:L11")
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
Dim FileName3 As String
Path = "C:\Duncans File\"
'Path = "T:\Richards Reports\MG Orders\"
FileName1 = Range("A2")
FileName2 = Range("I2")
FileName3 = Range("H2")
ActiveWorkbook.SaveAs FileName:=Path & FileName1 & "_" & FileName2 & "_" & FileName3 & ".txt", FileFormat:=xlTextMSDOS
End Sub

If anyone has a suggestion, I would much appreciate it, am just finding my way round macro's and at this time Friday afternoon, my brain is fried!

TIA,

Duncan

 

[MrKowz]

Welcome to the forums!

Try:

Private Sub CommandButton1_Click()
Dim content As String
Dim rng As Range
Set rng = Range("L2:L11")
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
Dim FileName3 As String

Dim sWB     As Workbook, _
    sWS     As Worksheet
    
Dim dWB     As Workbook, _
    dWS     As Worksheet
    
Path = "C:\Duncans File\"
'Path = "T:\Richards Reports\MG Orders\"
FileName1 = Range("A2")
FileName2 = Range("I2")
FileName3 = Range("H2")
Set sWB = ActiveWorkbook
Set sWS = sWB.ActiveSheet

Set dWB = Workbooks.Add
Set dWS = dWB.Sheets(1)

sWS.Range("L2:L11").Copy Destination:=dWS.Range("a1")
dWB.SaveAs filename:=Path & FileName1 & "_" & FileName2 & "_" & FileName3 & ".txt", FileFormat:=xlTextMSDOS
End Sub

 

[Duncan23]

Many thanks for the input, it is close but output is #REF! in each row. Is it possible to force Excel not to open the file after creation?
Thanks again, your help is much appreciated
Duncan

 

[MrKowz]

It sounds like the cells you're copying over contain formulas. In this case, we can have it paste-special>values. We can also close the new workbook after it's saved.

Try:

Private Sub CommandButton1_Click()
Dim content As String
Dim rng As Range
Set rng = Range("L2:L11")
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
Dim FileName3 As String

Dim sWB     As Workbook, _
    sWS     As Worksheet
    
Dim dWB     As Workbook, _
    dWS     As Worksheet
    
Path = "C:\Duncans File\"
'Path = "T:\Richards Reports\MG Orders\"
FileName1 = Range("A2")
FileName2 = Range("I2")
FileName3 = Range("H2")
Set sWB = ActiveWorkbook
Set sWS = sWB.ActiveSheet

Set dWB = Workbooks.Add
Set dWS = dWB.Sheets(1)

sWS.Range("L2:L11").Copy
dWS.Range("A1").PasteSpecial xlPasteValues
dWB.SaveAs filename:=Path & FileName1 & "_" & FileName2 & "_" & FileName3 & ".txt", FileFormat:=xlTextMSDOS
dWB.Close False
End Sub

 

[Duncan23]

Brilliant! That worked right out of the box - thank you for your help, I really appreciate it.

 

[MrKowz]

Happy to be of assistance - thanks for the feedback. Have a great day/weekend!