CODEWITHSUNDEEP
excelvba
HarpoonHarry
HarpoonHarry

Reputation: 215

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

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?

Upvotes: 1

Views: 1732

Answers (1)

pgSystemTester
pgSystemTester

Reputation: 9917

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

Upvotes: 3

Related Questions