Solved: Save a Worksheet using VBA with more then 255 Characters

[JimS]

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

[vba]
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

[/vba]

[GTO]

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:

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

[JimS]

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

[JimS]

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

Thanks for your efforts...

[GTO]

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

Thanks for your efforts...

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. Glad you got it working.

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

[lucas]

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.

[GTO]

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 .