Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Cell Content to CSV file
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Cell Content to CSV file
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 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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Cell Content to CSV file
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 wrote in message ... 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, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Cell Content to CSV file
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" 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 wrote in ... 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, |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Cell Content to CSV file
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" wrote in message ups.com... 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" 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 wrote in ... 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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I copy a row depending on the content of a cell in the row | Excel Worksheet Functions | |||
copy comment content to cell content as data not as comment | Excel Discussion (Misc queries) | |||
Copy row based on cell content | New Users to Excel | |||
copy cell content only | Excel Programming | |||
Copy a cell content into a TextBox | Excel Programming |