Create CSV

[Steve]

Thanks Jacob Skaria the paste values is working now
I was hoping this would fix another issue I am having but it didn't. Once I
copy to sheet2 I want to save that sheet as a csv. The code creates an
archive copy (with timestamp suffix) and also a working copy (without time
stamp) that is uploaded via FTP. This is the code that I am using:

Dim strWksheet As String
Dim strPath As String
Dim strFileName As String
Dim strTimeStamp As String


strWksheet = "sheet2"
strPath = "mypath"
strFileName = "NewFile"
strTimeStamp = Format(Now(), "yyyy-mm-dd_hhmm")


Sheets(strWksheet).Copy
ActiveWorkbook.SaveAs Filename:=strPath & strFileName & _
strTimeStamp & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

'close the new worksheet
ActiveWindow.Close
Application.DisplayAlerts = False
Sheets(strWksheet).Copy
ActiveWorkbook.SaveAs Filename:=strPath & Left(strFileName, 10) _
& ".txt", FileFormat:=xlCSV, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Application.DisplayAlerts = True
'close the new worksheet

Application.ScreenUpdating = True


exit_Sub:
On Error Resume Next
Exit Sub

err_Sub:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & _
") - Sub: Copy_Data_Worksheet - " & Now()
GoTo exit_Sub


This works but is the filtered data has fewer lines of data on subsequent
runs there is an issue. The site I am uploading to requires that plank lines
be blank and as it is now the file uploads with commas separating the fields.
How can I clear the commas before upload?
Thanks

 

[joel]

I can't guarentee my solution will work. Sometimes when data is written
to a cell and then cleared excel still thinks the cell contains data. I
delete the rows after the last row of data hoping this will solve your
problems. The method I used to find the last row sometimes doesn't find
the last row because a cell previously had data and you will get the
same results you have now.

Dim strWksheet As String
Dim strPath As String
Dim strFileName As String
Dim strTimeStamp As String


strWksheet = "sheet2"
strPath = "mypath"
strFileName = "NewFile"
strTimeStamp = Format(Now(), "yyyy-mm-dd_hhmm")


Sheets(strWksheet).Copy
with Activesheet
LastRow = .range("A" & rows.count).end(xlup).row
rows((LastRow + 1) & ":" & rows.count).delete
end with
ActiveWorkbook.SaveAs Filename:=strPath & strFileName & _
strTimeStamp & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

'close the new worksheet
ActiveWindow.Close
Application.DisplayAlerts = False
Sheets(strWksheet).Copy
with Activesheet
LastRow = .range("A" & rows.count).end(xlup).row
rows((LastRow + 1) & ":" & rows.count).delete
end with
ActiveWorkbook.SaveAs Filename:=strPath & Left(strFileName, 10) _
& ".txt", FileFormat:=xlCSV, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Application.DisplayAlerts = True
'close the new worksheet

Application.ScreenUpdating = True


exit_Sub:
On Error Resume Next
Exit Sub

err_Sub:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & _
") - Sub: Copy_Data_Worksheet - " & Now()
GoTo exit_Sub

 

[Jacob Skaria]

I cannot recreate the issue you are mentioning...

When you try with the filter..the csv file generated will still have all
rows..isnt it?

--One option is to delete the unused rows (if that is within the used range)
before exporting to .csv
--OR try a row by row export to csv using code..

 

[Jacob Skaria]

Steve, If Joel's suggestion doesnt work try row by row exporting....The below
code assumes that Column A is mandatory....Change to suit...


Dim intFile As Integer, strData As String, lngRow as Long, lngCol As Long

intFile = FreeFile
Open strFileName For Output As #intFile
lngRow = 1
lngCol = Cells(1, Columns.Count).End(xlToLeft).Column
Do While Range("A" & lngRow) <> ""
If Trim(Range("A" & lngRow)) <> "" Then
strData = Join(WorksheetFunction.Transpose(WorksheetFunction. _
Transpose(Range("A" & lngRow).Resize(, lngCol))), ",")
Print #intFile, strData
End If
lngRow = lngRow + 1
Loop
Close #intFile

 

[joel]

I left a period out of two statements

from
rows((LastRow + 1) & ":" & rows.count).delete

to
.rows((LastRow + 1) & ":" & rows.count).delete

 

[Steve]

This appears to be working
Thanks very much!