Copied Worksheet does not maintain it 's properties
 

Hello ,

My VB app , access a particular Excel file (ie Template.xls) and
runs a Macro .
This Macros out is a report which is printed on a Worksheet in the
same Template.xls .

Then I select the Output worksheet of the template xls and copy the
contents into a new Excel file . The reason why I am doing
this is because I dont want the new file to contain the Macro .

The problem is , the formatting of the Output sheet (Column lengths ,
Fonts etc) is NOT effected on the new workesheet of the file when
copied .
So I basically have an unformatted Output in the new file .

How may I overcome this .Any help is greatly appreciated .

My Code is as follows .

'Create Temp xls file for output
strTempFileName = Trim(CStr(oFS.GetDriveName(strAppPath) &
"\Book1.xls"))
If oFS.FileExists(strTempFileName) = False Then
'Create New File
oFS.CreateTextFile (strTempFileName)
End If

Set objTmpWkBk = objExl.Workbooks.Open(strTempFileName)
Set objTmpWkSht = objTmpWkBk.Worksheets.Item(1)

' Copy Output to temp xls file and display
'objTmpWkSht.Range("A1", "T800")
Set objWorksheet = objWorkbook.Worksheets.Item(1)

objWorksheet.Range("A1", "T8000").Select
objWorksheet.Range("A1", "T8000").Copy
objTmpWkSht.Range("A1", "T8000").Select
objTmpWkSht.Range("A1").Select
'objTmpWkSht.Range("A1").Width

objTmpWkSht.Paste --- FORMAT NOT MAINTAINED

objTmpWkSht.Range("A1").Select
objWorksheet.Range("A1").Select

Kind Regards ,
Adrian

Copied Worksheet does not maintain it 's properties
 

Hi Adrian

look in the VBA help for PasteSpecial

XlPasteType can be one of these XlPasteType constants.
xlPasteAll default
xlPasteAllExceptBorders
xlPasteColumnWidths
xlPasteComments
xlPasteFormats
xlPasteFormulas
xlPasteFormulasAndNumberFormats
xlPasteValidation
xlPasteValues
xlPasteValuesAndNumberFormats


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Adrian" wrote in message om...
Hello ,

My VB app , access a particular Excel file (ie Template.xls) and
runs a Macro .
This Macros out is a report which is printed on a Worksheet in the
same Template.xls .

Then I select the Output worksheet of the template xls and copy the
contents into a new Excel file . The reason why I am doing
this is because I dont want the new file to contain the Macro .

The problem is , the formatting of the Output sheet (Column lengths ,
Fonts etc) is NOT effected on the new workesheet of the file when
copied .
So I basically have an unformatted Output in the new file .

How may I overcome this .Any help is greatly appreciated .

My Code is as follows .

'Create Temp xls file for output
strTempFileName = Trim(CStr(oFS.GetDriveName(strAppPath) &
"\Book1.xls"))
If oFS.FileExists(strTempFileName) = False Then
'Create New File
oFS.CreateTextFile (strTempFileName)
End If

Set objTmpWkBk = objExl.Workbooks.Open(strTempFileName)
Set objTmpWkSht = objTmpWkBk.Worksheets.Item(1)

' Copy Output to temp xls file and display
'objTmpWkSht.Range("A1", "T800")
Set objWorksheet = objWorkbook.Worksheets.Item(1)

objWorksheet.Range("A1", "T8000").Select
objWorksheet.Range("A1", "T8000").Copy
objTmpWkSht.Range("A1", "T8000").Select
objTmpWkSht.Range("A1").Select
'objTmpWkSht.Range("A1").Width

objTmpWkSht.Paste --- FORMAT NOT MAINTAINED

objTmpWkSht.Range("A1").Select
objWorksheet.Range("A1").Select

Kind Regards ,
Adrian

Copied Worksheet does not maintain it 's properties
 

Thanks Ron . Will try it out.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!