Create CSV



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")

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

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

Application.ScreenUpdating = True

On Error Resume Next
Exit 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?



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
Close #intFile


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Save 3
Save with ref. to cell A1 2
BeforeSave 7
Simplify save code 11
BeforeSave Event 5
Saving a workbook to an iKnow portal 2
Code to share a file 3
Conversion from text to excel 2