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,

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





On Oct 4, 10:40 am, Joe K. <Joe K...@discussions.microsoft.com> wrote:
> 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,

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



"Joe K." <Joe K.@discussions.microsoft.com> wrote in message
news:4CC83A17-C0DE-4511-951C-(E-Mail Removed)...
>
> 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,
>
>

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

On Oct 4, 11:56 am, "Steve Yandl" <syandl_nos...@comcast.net> wrote:
> 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
>
> "Joe K." <Joe K...@discussions.microsoft.com> wrote in messagenews:4CC83A17-C0DE-4511-951C-(E-Mail Removed)...
>
>
>
> > 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,

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



"dan dungan" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> On Oct 4, 11:56 am, "Steve Yandl" <syandl_nos...@comcast.net> wrote:
>> 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
>>
>> "Joe K." <Joe K...@discussions.microsoft.com> wrote in
>> messagenews:4CC83A17-C0DE-4511-951C-(E-Mail Removed)...
>>
>>
>>
>> > 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,
>
>