Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,comp.lang.basic.visual.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,comp.lang.basic.visual.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I copy and paste a worksheet and maintain all formatting? | Excel Worksheet Functions | |||
maintain worksheet reference | Excel Worksheet Functions | |||
Copy worksheet & maintain cell reference across worksheets | Excel Worksheet Functions | |||
how do i maintain one worksheet from data from multiple worksheet | Excel Worksheet Functions | |||
worksheet Name and (Name) properties | Excel Programming |