Copy Cell Content to CSV file

[Guest]

I have a spreadsheet with a worksheet that I need to create a simple macro
to copy the contents from cells a1 to g100 to a csv file format.

Please help me with this macro.

Thanks,

 

[dan dungan]

Try this from Tom Ogilvy:

Sub CreateFile()
Dim sh as Worksheet, sh1 as Worksheet
set sh = Activesheet
workbooks.Add Template:=xlWBATWorksheet
set sh1 = Activesheet
sh.Range("A1:F20").Copy
sh1.Range("A1").PasteSpecial xlValues
Application.DisplayAlerts = False
sh1.parent.SaveAs "C:\Myfolder\Myfile.txt", xlCSV '<== Change
Application.DisplayAlerts = True
End Sub

or this from NickHK:

Private Sub CommandButton1_Click()
Dim DatObj As DataObject
Dim FileNum As Long
Dim TempStr As String

Const TEXTFILE As String = "C:\routes.txt"
Const TEXTFORMAT As Long = 1

Range("A1:F20").Copy

Set DatObj = New DataObject
With DatObj
.GetFromClipboard
If .GetFormat(TEXTFORMAT) = True Then
FileNum = FreeFile
TempStr = .GetText(TEXTFORMAT)
'Seem to get an extra vbCrLf from the DataObject, so remove
TempStr = Left(TempStr, Len(TempStr) - 2)

Open TEXTFILE For Output As #FileNum
Print #FileNum, TempStr
Close #FileNum
End If
End With

Application.CutCopyMode = False

End Sub

 

[Steve Yandl]

Here is one option.

This subroutine assumes that a folder named "C:\Test" already exists on the
system. It will save the CSV file to that folder as myData.csv

_____________________________________

Sub ExportCSV()

strFldrPath = "C:\Test"

Set FSO = CreateObject("Scripting.FileSystemObject")
Set objCSVfile = FSO.CreateTextFile(strFldrPath & "\myData.csv")

For R = 1 To 100
strRecord = Cells(R, 1).Value
For C = 2 To 7
strRecord = strRecord & "," & Cells(R, C)
Next C
objCSVfile.WriteLine strRecord
Next R

objCSVfile.Close

Set FSO = Nothing

End Sub

_____________________________________

Steve

 

[dan dungan]

Hi Steve,

I'm using option explicit, so I needed to dim each variable before the
subroutine would run.

However, I must have gotten something wrong, because it fails at
strRecord = Cells(R, 1).Value with the message Run-time error '91':
Object variable or With block variable not set.

Do you know where I went wrong? Here is how I've revised your code.

Sub ExportCSV()

Dim strFldrPath As String
Dim FSO As Object
Dim objCSVfile As Object
Dim R As Integer
Dim strRecord As Range
Dim C As Integer

strFldrPath = "K:\Customer Service\Quote\"

Set FSO = CreateObject("Scripting.FileSystemObject")
Set objCSVfile = FSO.CreateTextFile(strFldrPath & "\myData.csv")

For R = 1 To 100
strRecord = Cells(R, 1).Value
For C = 2 To 7
strRecord = strRecord & "," & Cells(R, C)
Next C
objCSVfile.WriteLine strRecord
Next R

objCSVfile.Close

Set FSO = Nothing

End Sub

Thanks,

Dan

 

[Steve Yandl]

dan,

Use
Dim strRecord As String
instead of as a Range.

Also, I got away with it during testing but I think I should have used
strRecord = strRecord & "," & Cells(R, C).Value
instead of
strRecord = strRecord & "," & Cells(R, C)


Steve