Create CSV
in [Programming]
|
From: Steve on 30 Apr 2010 07:31 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
From: joel on 30 Apr 2010 08:04 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 -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=199718 http://www.thecodecage.com/forumz
From: Jacob Skaria on 30 Apr 2010 08:43 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 (MVP - Excel) "Steve" wrote: > 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
From: Jacob Skaria on 30 Apr 2010 09:15 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 -- Jacob (MVP - Excel) "Jacob Skaria" wrote: > 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 (MVP - Excel) > > > "Steve" wrote: > > > 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
From: joel on 30 Apr 2010 09:28 I left a period out of two statements from rows((LastRow + 1) & ":" & rows.count).delete to .rows((LastRow + 1) & ":" & rows.count).delete -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=199718 http://www.thecodecage.com/forumz
|
Next
|
Last
Pages: 1 2 Prev: range loop? help Next: Getting Data Validation to execute when using Paste Special... |