How to move a Excel-Worksheet within Vfp6 2

[mkendi]

I have to move the first Sheet after the 3th sheet.
The VB code is:
Worksheets("Sheet1&quot.Move after:=Worksheets("Sheet3&quot

But I have no idea how to translate it to Vfp6

Thanks in advance
Mehmet Kendi

 

[danceman]

assuming you did this in VB

Set xlObj = CreateObject("Excel.Application&quot

xlObj.Worksheets("Sheet1&quot.Move after:=Worksheets("Sheet3&quot
Attitude is Everything

 

[Mike Gagnon]

danceman

That is still VB code. I think he needed it translated in VFP code.

 

[mkendi]

danceman, this is not vfp-language

 

[Mike Gagnon]

mkenki

In order to move between sheets of a workbook in Excel, you can use this:

oXl = CREATEOBJECT("excel.application")
oXl.Workbooks.Add()
oXl.Sheets(3).Select && This will make page 3 the activesheet

 

[mkendi]

Hi mgagnon

It is not my problem to move between sheets. My problem is to change the order of the sheets. Because as soon as I add a new sheet, the name of the sheet is e.g. "Sheet 4" and its position is left most. In my report the name of every sheet is a date, but unfortunately the order is wrong.
If I am able to move every new added sheet to the last position, the report-design would be very nice.

 

[danceman]

sorry, thought you want it in VB

oXl = CREATEOBJECT("excel.application&quot

oXl.Worksheets("Sheet1&quot.Move after:=Worksheets("Sheet3&quot


Attitude is Everything

 

[Mike Gagnon]

Danceman

Copying straight macros don't always work.
>oXl.Worksheets("Sheet1&quot.Move after:=Worksheets("Sheet3&quot

This throws an error. Does it work for you? ":=" Is not VFP syntax.

 

[rgbean]

While I don't often suggest forum participants go elsewhere for advice, it may be warranted this time. Try posting this question on the UT (

http://www.universalthread.com/)

- like here you'll have to "register", but asking questions is free.

At least one of the authors of "Microsoft Office Automation with Visual FoxPro" (

http://www.hentzenwerke.com/catalogavailability/autofox.htm)

hangs out there. Also, more really smart VFP people "live" there, but some can be more difficult to deal with than those here.

Rick

 

[Mike Gagnon]

Rick

Touche!!! (maybe Ed?)

 

[Mike Gagnon]

Rick

Touche!!! (maybe E.R.?)

 

[baltman]

I got around that very problem by creating my sheets in reverse order.

Brian

 

[Mike Gagnon]

mKendi

Although I have not found (yet) how to move a sheet's position within Excel, this might help depending what you need to do. This code will add a new blank sheet within a workbook, where you sepcify.

oXl=creat("excel.application")
oXl.Workbooks.Add()
oXl.sheets(2).Select
oXl.Worksheets.Add && The sheet #4 will be added before sheet number 2, just change the above select to insert a blank sheet where ever you need
oXl.Visible=.t.

 

[Mike Gagnon]

mKendi

To continue with my suggestion above. If you need to move a sheet for one place to another.
1. Locate the sheet you need to move
2. Select the content and copy
3. Create a sheet in the appropriate place as shown above
4. Paste
5. Go back to the original page and delete
Here is an example

SET STEP ON
LOCAL ldDate
STORE DATE() TO ldDate
ldDate = DTOS(ldDate)
oXl=CREAT("excel.application")
oXl.Workbooks.ADD()
oXl.sheets(3).SELECT
WITH oXl
WITH .RANGE("A1")
    .VALUE = "Testdata Customer Report"
    WITH .FONT
        .Bold = .T.
        .SIZE = 14
    ENDWITH
ENDWITH
ENDWITH
oXl.sheets(3).RANGE("a1:D1").SELECT
WITH oXl 
   .selection.copy
ENDWITH
oXl.sheets(2).SELECT
oXl.worksheets.add()
oXl.activesheet.paste
oXl.sheets(3).select
oXl.sheets(3).delete
oXl.VISIBLE = .T.

 

[danceman]

this is the sheet you want to move
objt = obj.workbooks(1).worksheets('Sheet1')

move it before sheet2
obj.workbooks(1).worksheets('Sheet2').move(objt)

move it after sheet2
obj.workbooks(1).worksheets('Sheet2').move(,objt)


it took awhile to find out what data type excell was looking for. it was object. Attitude is Everything

 

[mkendi]

Hi danceman

Thaaaaat's it!! A big thank-you from Turkey!

 

[danceman]

you proberly have figured it out by now that I have the order wrong

objt = obj.workbooks(1).worksheets('Sheet1')

move it before sheet2
obj.workbooks(1).worksheets('Sheet2').move(objt)
this actual moves sheet2 after sheet1

move it after sheet2
obj.workbooks(1).worksheets('Sheet2').move(,objt)
this actualy moves sheet2 after sheet1

also, documentation says

If you don't specify either Before or After, Microsoft Excel creates a new workbook that contains the moved sheet. Attitude is Everything