Needed: worksheet export help

I have a worksheet with four tabs of which three are really needed. I
need to have these three worksheets exported to CSV. I was told on
here that since my cell lengths are over 255 characters, I will first
need to copy the data. This is the order I want all this to be done..

1) User closes the worksheet {Private Sub Workbook_BeforeClose(Cancel
As Boolean)}

2) Each individual worksheet is then:
a) check to see if the final exported .csv is already open
1. if so, close it
b) copy worksheet
c) the copy is then saved as tab name.csv without prompting the
user

3) Save the original worksheet

On the web, I saw something about having to do something special about
copying cells over 255 characters in length...

Set wsSource = ActiveSheet
ActiveSheet.Copy After:=Sheets(Sheets.Count)
Set wsNew = ActiveSheet
'--fixup for cell lengths greater than 255
wsSource.UsedRange.Copy
wsNew.Range("A1").PasteSpecial
Cells.Calculate
'-- following code from MS KB 213548 --
'Clear out the clipboard and select cell A1.
Application.CutCopyMode = False
Range("A1").Select

Do I really need to do this? Up to this point, all the code I've been
writing will only export only the ActiveSheet, but will do it four
times and all with the same name.

This is the code I currently have (that doesn't function properly).
Don't put too much heart into it since I've done alot of fiddling:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim wks As Worksheet
Dim newWks As Worksheet
Dim MyPath As String
Dim FName As String
Dim wksToCopy As Worksheet

Me.Saved = False
MyPath = ActiveWorkbook.Path

Application.DisplayAlerts = False

For Each wks In ActiveWorkbook.Worksheets

FName = wks.Name & ".csv"

' If WorkbookOpen(FName) Then
' Workbooks(FName).Close savechanges:=False
' End If

Set wksToCopy = Worksheets(wks)
wksToCopy.Copy 'copies to a new workbook
Set newWks = ActiveSheet
wksToCopy.Cells.Copy Destination:=newWks.Range("a1")

Next wks

Application.DisplayAlerts = True
ActiveWorkbook.SaveAs Filename:=MyPath & "\" & FName,
FileFormat:=xlCSV
ActiveWorkbook.Close savechanges:=False

End Sub

Any help would be greatly appreciated! This is for Excel 2003.


--
mainemike

if the cell lengths are more that 255 then
dim strCell as string
strCell=cstr(activecell.value)

you may have to change the Activecell.Value if an error occurs to
Activecell.Text


With a string you are limitied to 32,767 characters.

then copy each strcell to where ever

etc..



Send me the file and maybe I can do it for you.


or see my site, there maybe something there, I cannot recall.

http:www.geocities.com/excelmarksway

excelmarksway@yahoo.com.au




"mainemike" wrote:

>
> I have a worksheet with four tabs of which three are really needed. I
> need to have these three worksheets exported to CSV. I was told on
> here that since my cell lengths are over 255 characters, I will first
> need to copy the data. This is the order I want all this to be done..
>
> 1) User closes the worksheet {Private Sub Workbook_BeforeClose(Cancel
> As Boolean)}
>
> 2) Each individual worksheet is then:
> a) check to see if the final exported .csv is already open
> 1. if so, close it
> b) copy worksheet
> c) the copy is then saved as tab name.csv without prompting the
> user
>
> 3) Save the original worksheet
>
> On the web, I saw something about having to do something special about
> copying cells over 255 characters in length...
>
> Set wsSource = ActiveSheet
> ActiveSheet.Copy After:=Sheets(Sheets.Count)
> Set wsNew = ActiveSheet
> '--fixup for cell lengths greater than 255
> wsSource.UsedRange.Copy
> wsNew.Range("A1").PasteSpecial
> Cells.Calculate
> '-- following code from MS KB 213548 --
> 'Clear out the clipboard and select cell A1.
> Application.CutCopyMode = False
> Range("A1").Select
>
> Do I really need to do this? Up to this point, all the code I've been
> writing will only export only the ActiveSheet, but will do it four
> times and all with the same name.
>
> This is the code I currently have (that doesn't function properly).
> Don't put too much heart into it since I've done alot of fiddling:
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
>
> Dim wks As Worksheet
> Dim newWks As Worksheet
> Dim MyPath As String
> Dim FName As String
> Dim wksToCopy As Worksheet
>
> Me.Saved = False
> MyPath = ActiveWorkbook.Path
>
> Application.DisplayAlerts = False
>
> For Each wks In ActiveWorkbook.Worksheets
>
> FName = wks.Name & ".csv"
>
> ' If WorkbookOpen(FName) Then
> ' Workbooks(FName).Close savechanges:=False
> ' End If
>
> Set wksToCopy = Worksheets(wks)
> wksToCopy.Copy 'copies to a new workbook
> Set newWks = ActiveSheet
> wksToCopy.Cells.Copy Destination:=newWks.Range("a1")
>
> Next wks
>
> Application.DisplayAlerts = True
> ActiveWorkbook.SaveAs Filename:=MyPath & "\" & FName,
> FileFormat:=xlCSV
> ActiveWorkbook.Close savechanges:=False
>
> End Sub
>
> Any help would be greatly appreciated! This is for Excel 2003.
>
>
> --
> mainemike
>