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

 

[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

 

[Steve]

This appears to be working
Thanks very much!