Thread: Create CSV
View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
joel[_961_] joel[_961_] is offline
external usenet poster
 
Posts: 1
Default Create CSV


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/sh...d.php?t=199718

http://www.thecodecage.com/forumz