PDA

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



JimS
03-10-2010, 11:43 AM
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


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

GTO
03-10-2010, 03:35 PM
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
03-11-2010, 08:48 AM
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
03-11-2010, 11:04 AM
Nevermined - I decided to simply save it as a new workbook instead of doing a sheet copy.

Thanks for your efforts...

GTO
03-11-2010, 12:00 PM
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...

lucas
03-11-2010, 12:36 PM
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
03-11-2010, 10:07 PM
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: .