Question Create Copy Of Excel Sheet

[vdennis]

I am trying to copy an excel sheet in a workbook and then rename it. I recorded the excel macro which is what I usually do and can usually slug my way until I can get it to work. But what is happening is that a new 'book1' is also being created with the copy command. So I was wondering it anyone had any experience along this line and get me some pointers?
This code to copy is as follows (understanding all the other handles have been called.)
Procedure NewTabCreate:
Message
"Ok, going to copy a sheet"
view-as alert-box.

hWorkSheet:select.
hWorkSheet:copy:sheets:item(1).

end procedure.

Thanks for looking at this.
-Dennis-

 

[Osborne]

This is one way of doing it, and this example will copy sheet 1 after sheet 2:

hWorksheet2 = hWorkbook:Worksheets(2).
hWorkbook:Worksheets(1):Copy(,hWorksheet).
/* Or as per your case: hWorkbook:Worksheets(hWorkSheet:Name):Copy(,hWorksheet2). */
hWorkbook:ActiveSheet:Name = "Copy Name".

 

[vdennis]

Thanks so much. Was able to use the code and after some changes to yours and mine got it working. It is too bad that a lot of the calls that can be made to Excel are un-documented. In my program, depending if you want to merge the sheets or not, is being written for our purchasing department to use with their vendors. That is why I needed the ability to copy a sheet. Didn't want to have to use the add() method, then copy the first row of headers from one to the other. I use the clearContents method to do that.
BTW, if I were to upload the file, would it show in the box like yours?
Again, thanks,
-Dennis-

 

[Osborne]

I do not know if you have used the COM Object Viewer - Progress KB - Tips for using Excel and Word via ActiveX Automation - and if not give that a try. As the article points out, if Excel 2007 or later open the file C:\Program Files (x86)\Microsoft Office\Office14\Excel.exe for the available options.

Regarding "BTW, if I were to upload the file, would it show in the box like yours?", do you mean the box the code is in or something else?

 

[vdennis]

What I was referring to how you were able to place your sample code in a scroll-box.
I'll have to look for that file. We are running 2010 excel, any may be upgrading later this year. Hope they don't make too many changes before 6/30/2017. (My supposed start of retirement.)
I am still testing aspects of the program, but when done I'll pass along the code I ended up using. Because the user can select a single workbook to cover several vendors, each vendor having their own tab, or a separate excel workbook per vendor, I have to make sure everything works as requested.
Have a great day.
-Dennis-

 

[Osborne]

Just place it between [ CODE] [ /CODE] tags - minus the leading space after the opening bracket.

 

[vdennis]

Ok, this is a test

for each customer:
end.