Save the contents of a cell to a csv without quotes in Excel

Question

I'm trying to create a csv file (without quotes) using VBA - the only contents of the file should be the value in the cell named "MetricValue".

    Dim FileName As String
    Dim PathName As String
    Dim ws As Worksheet

    Set Range.Value = MetricValue
    FileName = "filename.csv"
    PathName = Application.ActiveWorkbook.Path
    ws.Copy
    ActiveWorkbook.SaveAs FileName:=PathName & "\" & FileName, _
        FileFormat:=xlCSV, CreateBackup:=False
End Sub

I know I've stuffed up something with setting the range, but can't figure out why. Everything I've tried to get to work only outputs with quote marks around the value. Can anyone point me in the right direction?

`Set ws = Range.Value = MetricValue` - what's that's supposed to do? — BigBen, Apr 14 '20 at 23:39
@BigBen it's meant to set the value that I'd like to export. — HarpoonHarry, Apr 14 '20 at 23:52
@PGSystemTester no, there are no commas, it's just a number eg. "-34123.32" — HarpoonHarry, Apr 14 '20 at 23:52
`Set Range.Value = MetricValue` is still problematic... what's the code you're actually running? — BigBen, Apr 14 '20 at 23:53
@BigBen - this is the code I'm trying to run. I'm trying to save the value in the named cell "MetricValue' (eg. -4321.12) as the only value inside a csv. But I can't seem to set that to work. I know it's problematic, but I can't work out why. — HarpoonHarry, Apr 14 '20 at 23:55
The code as you've provided it won't run (`Set` is for object variables). Can you make your question a [reprex]? — BigBen, Apr 15 '20 at 00:22

score 3 · Accepted Answer · edited Jun 01 '23 at 13:53

I think your question is confusing because what you really seem to be wanting to do is save a single value as a CSV. The quotes that are being created, are likely from other parts of your file. There's probably better methods to accomplish what your overall objective is. Here's a good StackOverflow post on CSV files via excel that might be useful.

Regardless, here are two solutions to save your Named Range value as a CSV file using a macro. Make sure the named range is at the workbook level (not sheet).

Option One

Sub MakeASingleValueCSVFile()
Dim FileName As String: FileName = "filename.csv"
Dim PathName As String: PathName = Application.ActiveWorkbook.Path
Dim finalValue As Double: finalValue = Range("MetricValue").Value

    
Dim iFileNum As Long
iFileNum = FreeFile
Open PathName & "\" & FileName For Output As iFileNum

Print #iFileNum, finalValue
Close #iFileNum
End Sub

That has some spacing issues you might not like.

Option 2

(updated with more dynamic solution that initial question)

Const FileName As String = "filename2"
Const namedRange As String = "MetricValue" 'make sure at workbook level
Dim PathName As String: PathName = Application.ActiveWorkbook.Path
Dim pullRNG As Range: Set pullRNG = Range(namedRange)
Dim wkBK As Workbook: Set wkBK = Application.Workbooks.Add

With wkBK
    .Sheets(1).Cells(1, 1).Resize(pullRNG.Rows.Count, pullRNG.Columns.Count) = pullRNG.Value
    .SaveAs PathName & "\" & FileName, xlCSV
    .Close
End With

Save the contents of a cell to a csv without quotes in Excel

1 Answers1

Option One

Option 2