Save a Worksheet using VBA with more then 255 Characters

The following code copies a worksheet and saves it into a folder.
Unfortunately, I need to get around the cell 255 Character Limit.

Is there a way to get around this?

Thanks for any help…

JimS

Code:

---

Public Sub Export2()
    ' Creates the DM Report File and saves it in the Tracking folder
    Dim wb As Workbook
    Dim wb2 As Workbook
    Dim ws As Worksheet
    Dim MyDir As String
    Dim MyFile As String
    Dim DTAddress As String
    Dim newFile As String, fName As String
    fName = Range("SaveName").Value
    newFile = fName
    Const FLDR_NAME As String = "C:\Data\Tracking\"
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
    Set wb = ThisWorkbook
    Set ws = Sheets("DM Report")
    Sheets("DM Report").Select
    Set wb2 = Workbooks.Add
    ws.Copy Before:=wb2.Sheets(1)
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs FLDR_NAME & (fName) & ".xls"
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
    Sheets("DM Report").Select
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
End Sub

---

Hi there,

I know I've seen articles on this and thought there was something in the kb, but all I found was re 1024+ chars printing. Anyways, I think the easy way to get past it would be to copy twice. Once for the sheet layout and such, then copy the used range.

See if this helps:

Code:

---

Option Explicit
   
'//*********Tested, just example****************************************//
Sub CopyAllChar()
Dim wks As Worksheet
Dim wbNew As Workbook
   
    '// Guessing a bit, that you just want the one sheet in the new wb...//
   
    '// Set a reference to a new, one-sheet wb.                        //
    Set wbNew = Workbooks.Add(xlWBATWorksheet)
   
    '// Change name to source sheet                                    //
    With ThisWorkbook.Worksheets("Sheet1")
       
        .Copy Before:=wbNew.Worksheets(1)
       
        '// Delete the second (blank) sheet in the new wb.              //
        Application.DisplayAlerts = False
        wbNew.Worksheets(2).Delete
        Application.DisplayAlerts = True
       
        '// The first .Copy copied the sheet in its entirety, cell widths, //
        '// borders, layout, so on...  But... dropped chars over 255.      //
        '// Go back and copy the cells, from A1 to the end of the used range, //
        '// with the destination of A1 on the new wb's sheet.              //
        '// This way we don't lose chars and nothing "moves" should the    //
        '// usedrange start down/over a ways from A1.                      //
        Range(.Range("A1"), .UsedRange.SpecialCells(xlCellTypeLastCell)).Copy _
            wbNew.Worksheets(1).Range("A1")
    End With
   
    '// You already set a reference to the newly created wb.  Ditch using  //
    '// active workbook, activesheet, selecting and so on when you already  //
    '// have more assured references.                                      //
    wbNew.SaveAs "D:\2010_PC\Temp.xls"
    wbNew.Close False
End Sub
   
'//****************NOT tested**********************************************//
Public Sub Export2()
   
 ' Creates the DM Report File and saves it in the Tracking folder
   
Dim wb As Workbook
Dim wb2 As Workbook
Dim ws As Worksheet
Dim MyDir As String
Dim MyFile As String
Dim DTAddress As String
Dim newFile As String, fName As String
   
Const FLDR_NAME As String = "C:\Data\Tracking\"
       
    fName = Range("SaveName").Value
    newFile = fName
   
    With Application
    '.DisplayAlerts = False
    .ScreenUpdating = False
    End With
   
   
    Set wb = ThisWorkbook
    Set ws = Sheets("DM Report")
   
    '// You can skip selecting here, you have a referene from above.            //
    'Sheets("DM Report").Select
   
    Set wb2 = Workbooks.Add
   
    ws.Copy Before:=wb2.Sheets(1)
   
    '// Just a suggestion:  Only keep alerts killed as long as actually needed. //
    '// Just a rookie coder opinion, but similar to glossing over errror        //
    '// checking when unnecessary, warnings before things go KABOOM are usually //
    '// a good idea.                                                            //
    Application.DisplayAlerts = False
    '// Use the reference already set.                                          //
    wb.SaveAs FLDR_NAME & (fName) & ".xls"
    Application.DisplayAlerts = True
   
    wb.Close False
    '// Probably not needed                                                    //
    Sheets("DM Report").Select
   
    Application.ScreenUpdating = True
End Sub

---

Hope that helps,

Mark

GTO,
Using your method works better but now I receive a "The picture is too large and will be truncated" error.

There is a small logo on the source sheet.

It also is not copying over the Page-setup Options (for example: the Print Titles: Rows to repeat at top setting).

Any other ideas?

Thanks...

JimS

Nevermined - I decided to simply save it as a new workbook instead of doing a sheet copy.

Thanks for your efforts...

Quote:

---

Originally Posted by JimS

Nevermined - I decided to simply save it as a new workbook instead of doing a sheet copy.

Thanks for your efforts...

---

:doh:I think I will go slunk back off to the corner now... I guess a saveas and deleting the un-needed sheets was just too obvious. :drooling3 Glad you got it working.

...slunk....slink...slunk...

Hey Mark, don't beat yourself up for trying to help.

You offer great options.

I count on guys like you and Malcolm, Bob, etc. to follow me around and make sure I'm not giving bad advice.

Howdy Steve,

Shucks, I wasn't really irritated with myself as much as laughing at myself a bit after after seeing how I had picked out a much tougher way to get it done. Sort of like spying a well marked trail only after reaching the top of some hill by slugging it out through a bunch of brush :bug: .