VBA range export to csv file

Duncan23

New Member
Joined
Jul 28, 2017
Messages
3
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:

Code:
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

Well-known Member
Joined
Jun 30, 2008
Messages
6,649
Welcome to the forums!

Try:

Code:
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
 
Last edited:

Duncan23

New Member
Joined
Jul 28, 2017
Messages
3
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

Well-known Member
Joined
Jun 30, 2008
Messages
6,649
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:

Code:
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
 
Last edited:

Duncan23

New Member
Joined
Jul 28, 2017
Messages
3
Brilliant! That worked right out of the box - thank you for your help, I really appreciate it.
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,649
Happy to be of assistance - thanks for the feedback. Have a great day/weekend!
 

Forum statistics

Threads
1,082,380
Messages
5,365,124
Members
400,824
Latest member
Themilkybarkid

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top