I’ve tried everything I know to try and I cannot find the Excel Automation string to copy the active worksheet to a new sheet (in the same workbook). I’ve already searched the visual basic help in Excel, and tried all the following: xlSheet[SheetNumber].Copy(NewSheetName); and tried xlSheet[SheetNumber].Copy; and xlWorksheets(SheetNumber).Copy after := Worksheets(NewSheetName); I’m now in serious distress. [xx(]I just need a simple line of code that will allow me to copy the current worksheet to a new sheet - and it would be nice if I could have it placed after the current sheet, and name it. HELP! I’m begging! (Attain v3.60)
try something like that Sheets(“Sheet1”).Select Sheets(“Sheet1”).Copy Before:=Sheets(2) i recorded a new macro, do the activities and then expected the macro and this was the result. so the code in navision should be more or less like this.
Stupid dog tricks with xlSheets…
CREATE(xlApp); xlApp.Visible(FALSE); // create new workbook (defaults to 3 sheets: 'Sheet1', 'Sheet2', and 'Sheet3')... xlWrkBook := xlApp.Workbooks.Add; // get a handle to the Worksheets collection... xlSheets := xlWrkBook.WorkSheets; // get a handle to 'Sheet1', and scribble on it... xlSheet := xlSheets.Item('Sheet1'); xlSheet.Range('A1').Value := 'Hello, Sheet1...'; // make a copy of 'Sheet1', placing it before 'Sheet2'... xlSheet.Copy(xlSheets.Item('Sheet2')); // let go of 'Sheet1', and grab onto the new sheet by its index number... CLEAR(xlSheet); xlSheet := xlSheets.Item(2); xlSheet.Name := 'NewSheet'; xlSheet.Range('A2').Value := 'Hello, NewSheet...'; // move it to just before the last worksheet... xlSheet.Move(xlSheets.Item(xlSheets.Count)); // now, move the last worksheet before the copy... CLEAR(xlSheet); xlSheet := xlSheets.Item(xlSheets.Count); xlSheet.Move(xlSheets.Item(xlSheets.Count - 1)); // tada... the new sheet is now at the end... // release the handles, and show off ... CLEAR(xlSheet); CLEAR(xlSheets); CLEAR(xlWrkBook); XlApp.Visible(TRUE); XlApp.UserControl(TRUE); CLEAR(XlApp);
I’ve messed with this for hours now, and I keep getting the same message “it is not possible to assign a sheets to a worksheets”. Perhaps I have the variables assigned differently than yours??? Name DataType Subtype Length xlApp Automation ‘Microsoft Excel 8.0 Object Library’.Application xlBook Automation ‘Microsoft Excel 8.0 Object Library’.Workbook xlSheet Automation ‘Microsoft Excel 8.0 Object Library’.Worksheet xlSheet1 Automation ‘Microsoft Excel 8.0 Object Library’.Worksheet xlWorksheet Automation ‘Microsoft Excel 8.0 Object Library’.Worksheet xlWorksheets Automation ‘Microsoft Excel 8.0 Object Library’.Worksheets Note that my xlSheets is an array. I refer to the sheetnumber. Does the error make any sense? Thanks for all help!
errr…I meant my xlSheet was an array…as you can see I don’t have an xlSheets… I’ve tried adding variables to match yours as well, but my error remains the same.
errr…I meant my xlSheet was an array…as you can see I don’t have an xlSheets… I’ve tried adding variables to match yours as well, but my error remains the same. What I’ve done is create a codeunit of functions I can call that all use these variables. The functions then are passed the sheet number, the cell or cell range, and then my code for excel is all in one place…t’s crossed, i’s dotted. If it helps, I’ll send a copy of the CU. I’m in desperate need of some help! [^]
quote:
“it is not possible to assign a sheets to a worksheets”. Name DataType Subtype Length … xlWorksheets Automation ‘Microsoft Excel 8.0 Object Library’.Worksheets
The ‘worksheets’ that the error refers is probably your ‘xlWorksheets’. You are probably trying to grab the Worksheets collection with something like: xlWorksheets := xlBook.WorkSheets; which seems eminently reasonable. However, for some strange reason, ‘xlBook.WorkSheets’ doesn’t return a ‘Worksheets’ collection but rather a ‘Sheets’ collection. (They are almost the same thing – if you don’t have any Charts in your workbook, then ‘Sheets’ and ‘Worksheets’ are identical.) The fix: change the SubType of ‘xlWorksheets’ to ‘Microsoft Excel 8.0 Object Library’.Sheets…
Greetings my friend Fritz![8D] I finally got it to work by creating a variant variable, and assigning the excel sheet to it. Here’s the code once the sheet is in and active: Name xlSheet1 Automation’Microsoft Excel 8.0 Object Library’.Worksheet ExcelSheet Variant … xlSheet1 := xlApp.ActiveSheet; ExcelSheet := xlSheet[SheetNumber]; xlSheet1.Copy(ExcelSheet); xlSheet1.Name := FORMAT(NewSheetName); … My thanks to you for your enduring patience. I can now enjoy my weekend! [8D]