larz


Can anyone help with converting the following from VBA code generated in Excel to VFP code:

Sheets("Notes").Copy Before:=Workbooks("AB0KF3FK.XLS").Sheets(1)

Thank you




Re: VBA to VFP code conversion

dni


You like to copy sheet "Notes" before Sheets is same workbook Am I understood correct

Do you like to keep excel format or convert to tables







Re: VBA to VFP code conversion

Tamar E. Granor

Assuming your reference to the workbook is in oWorkbook and your reference to Excel is in oXL:

oWorkbook.Sheets("Notes").Copy( ;
oXL.Workbooks("AB0KF3FK").Sheets(1))


Tamar





Re: VBA to VFP code conversion

MarciaAkins

And here are the steps that you can use to translate the VBA into VFP:

1. Add parentheses around the parameter list

2. Examine the calling prototype for the method in the object browser and rearrange the parameters in the generated macro so that they appear in the same order that they are listed here. Because VBA uses named parameters, their order does not matter. This is not so in VFP where parameters are positional. Regardless of the order of the parameters in the VBA macro, the Object Browser and IntelliSense always display them in the order required for correct Visual FoxPro syntax.

3. Remove the names of the parameters on the left side of the ¡®:=¡¯ as well as the ¡®:=¡¯

4. Replace the named constants with their values. This includes replacing True with 1 and False with 0. Note: An alternative would have been to add #DEFINE statements at the top of the method like this after looking up the values for the named constants in the Object Browser:

#DEFINE TRUE 1

#DEFINE FALSE 0

#DEFINE WDOPENFORMATAUTO 0






Re: VBA to VFP code conversion

larz

Hi Tamar,
Thanks so much for the syntax help; however, I am having problems getting it to work. I included sample code below. Briefly, what I am trying to do is copy one tab from one excel file to another (the Notes tab from copy_this_tab.xls to the first tab in into_this_file.xls). The first line in my trial section (based on your input) is what I thought it should be --> loWBFrom.sheets("Notes").copy(loXLTo.Workbooks("into_this_file.xls").sheets(1)) --> but it gives me the error "... unable to get the copy property of the worksheet class"
Thanks again
local lcFrom, loXLFrom, loWBFrom, lcTo, loXLTo, loWBTo
lcFrom = "c:\temp\copy_this_tab.xls" && this xls file has a Notes tab
loXLFrom = getobject('','Excel.Application')
loWBFrom = loXLFrom.Workbooks.open(lcFrom)
lcTo = "c:\temp\into_this_file.xls" && this xls file can have any variety of tabs in it
loXLTo = getobject('','Excel.Application')
loWBTo = loXLTo.Workbooks.open(lcTo)
****************************************************************************************
&& code here should copy the Notes tab from c:\temp\copy_this_tab.xls as the first tab in c:\temp\into_this_file.xls
&& I've tried these and more (I certainly wasn't expecting some of them to work) && error message of each below
&& I thought it should be the first one
*loWBFrom.sheets("Notes").copy(loXLTo.Workbooks("into_this_file.xls").sheets(1)) && unable to get the copy property of the worksheet class
*loWBFrom.sheets("Notes").copy(loXLTo.Workbooks("into_this_file").sheets(1)) && member workbooks does not evaluate to an object
*loWBFrom.sheets("Notes").copy(loXLTo("into_this_file.xls").sheets(1)) && loXLTo is not an array
*loWBFrom.sheets("Notes").copy(loXLTo("into_this_file").sheets(1)) && loXLTo is not an array
*loWBFrom.sheets("Notes").copy(("into_this_file.xls").sheets(1)) && unrecognized command verb
*loWBFrom.sheets("Notes").copy(Workbooks("into_this_file.xls").sheets(1)) && workbooks is not an array
*loWBFrom.sheets("Notes").copy(loWBTo.Workbooks("into_this_file.xls").sheets(1)) && member workbooks does not evaluate to an object
*loWBFrom.sheets("Notes").copy(loWBTo("into_this_file.xls").sheets(1)) && loXLTo is not an array
&& this doesn't fail but it copies the Notes tab to a new Book1.xls file
*loWBFrom.sheets("Notes").copy()
****************************************************************************************
loWBFrom.close(0)
loXLFrom.quit
release loWBFrom, loXLFrom
loXLTo.application.range('A1').select
loXLTo.application.visible = .t.
return
&& Steps used to generate VBA code to copy notes tab of copy_this_tab.xls as 1st tab in the file into_this_file.xls
&& 1) open both c:\temp\copy_this_tab.xls and c:\temp\into_this_file.xls
&& 2) go to the copy_this_tab.xls file
&& 3) right click the "Notes" tab
&& 4) select "Move or Copy"
&& 5) select "into_this_file.xls" from the "To Book" dropdown
&& 6) check "Create a Copy" checkbox
&& 7) click "OK" button
&& VBA code generated is --> Sheets("Notes").Copy Before:=Workbooks("into_this_file.xls").Sheets(1)




Re: VBA to VFP code conversion

larz

Hi,
No, what I want to do is copy one tab from one excel file to another (the Notes tab from copy_this_tab.xls to the first tab in into_this_file.xls). The first line in my trial section of the sample code below is what I thought it should be --> loWBFrom.sheets("Notes").copy(loXLTo.Workbooks("into_this_file.xls").sheets(1)) --> but it gives me the error "... unable to get the copy property of the worksheet class"
I am doing this to be able to keep all the formats of what someone else does on various excel file and I just want to pull the tabs into a VFP generated excel file.
Thanks for your response
local lcFrom, loXLFrom, loWBFrom, lcTo, loXLTo, loWBTo
lcFrom = "c:\temp\copy_this_tab.xls" && this xls file has a Notes tab
loXLFrom = getobject('','Excel.Application')
loWBFrom = loXLFrom.Workbooks.open(lcFrom)
lcTo = "c:\temp\into_this_file.xls" && this xls file can have any variety of tabs in it
loXLTo = getobject('','Excel.Application')
loWBTo = loXLTo.Workbooks.open(lcTo)
****************************************************************************************
&& code here should copy the Notes tab from c:\temp\copy_this_tab.xls as the first tab in c:\temp\into_this_file.xls
&& I've tried these and more (I certainly wasn't expecting some of them to work) && error message of each below
&& I thought it should be the first one
*loWBFrom.sheets("Notes").copy(loXLTo.Workbooks("into_this_file.xls").sheets(1)) && unable to get the copy property of the worksheet class
*loWBFrom.sheets("Notes").copy(loXLTo.Workbooks("into_this_file").sheets(1)) && member workbooks does not evaluate to an object
*loWBFrom.sheets("Notes").copy(loXLTo("into_this_file.xls").sheets(1)) && loXLTo is not an array
*loWBFrom.sheets("Notes").copy(loXLTo("into_this_file").sheets(1)) && loXLTo is not an array
*loWBFrom.sheets("Notes").copy(("into_this_file.xls").sheets(1)) && unrecognized command verb
*loWBFrom.sheets("Notes").copy(Workbooks("into_this_file.xls").sheets(1)) && workbooks is not an array
*loWBFrom.sheets("Notes").copy(loWBTo.Workbooks("into_this_file.xls").sheets(1)) && member workbooks does not evaluate to an object
*loWBFrom.sheets("Notes").copy(loWBTo("into_this_file.xls").sheets(1)) && loXLTo is not an array
&& this doesn't fail but it copies the Notes tab to a new Book1.xls file
*loWBFrom.sheets("Notes").copy()
****************************************************************************************
loWBFrom.close(0)
loXLFrom.quit
release loWBFrom, loXLFrom
loXLTo.application.range('A1').select
loXLTo.application.visible = .t.
return
&& Steps used to generate VBA code to copy notes tab of copy_this_tab.xls as 1st tab in the file into_this_file.xls
&& 1) open both c:\temp\copy_this_tab.xls and c:\temp\into_this_file.xls
&& 2) go to the copy_this_tab.xls file
&& 3) right click the "Notes" tab
&& 4) select "Move or Copy"
&& 5) select "into_this_file.xls" from the "To Book" dropdown
&& 6) check "Create a Copy" checkbox
&& 7) click "OK" button
&& VBA code generated is --> Sheets("Notes").Copy Before:=Workbooks("into_this_file.xls").Sheets(1)




Re: VBA to VFP code conversion

larz

Thanks Marcia for the guidance.
I am still having problems getting it to work. I included sample code below. Briefly, what I am trying to do is copy one tab from one excel file to another (the Notes tab from copy_this_tab.xls to the first tab in into_this_file.xls). The first line in my trial section (which I believe is converted properly per your guidance) is what I thought it should be --> loWBFrom.sheets("Notes").copy(loXLTo.Workbooks("into_this_file.xls").sheets(1)) --> but it gives me the error "... unable to get the copy property of the worksheet class"
Thanks again for any help you might suggest
local lcFrom, loXLFrom, loWBFrom, lcTo, loXLTo, loWBTo
lcFrom = "c:\temp\copy_this_tab.xls" && this xls file has a Notes tab
loXLFrom = getobject('','Excel.Application')
loWBFrom = loXLFrom.Workbooks.open(lcFrom)
lcTo = "c:\temp\into_this_file.xls" && this xls file can have any variety of tabs in it
loXLTo = getobject('','Excel.Application')
loWBTo = loXLTo.Workbooks.open(lcTo)
****************************************************************************************
&& code here should copy the Notes tab from c:\temp\copy_this_tab.xls as the first tab in c:\temp\into_this_file.xls
&& I've tried these and more (I certainly wasn't expecting some of them to work) && error message of each below
&& I thought it should be the first one
*loWBFrom.sheets("Notes").copy(loXLTo.Workbooks("into_this_file.xls").sheets(1)) && unable to get the copy property of the worksheet class
*loWBFrom.sheets("Notes").copy(loXLTo.Workbooks("into_this_file").sheets(1)) && member workbooks does not evaluate to an object
*loWBFrom.sheets("Notes").copy(loXLTo("into_this_file.xls").sheets(1)) && loXLTo is not an array
*loWBFrom.sheets("Notes").copy(loXLTo("into_this_file").sheets(1)) && loXLTo is not an array
*loWBFrom.sheets("Notes").copy(("into_this_file.xls").sheets(1)) && unrecognized command verb
*loWBFrom.sheets("Notes").copy(Workbooks("into_this_file.xls").sheets(1)) && workbooks is not an array
*loWBFrom.sheets("Notes").copy(loWBTo.Workbooks("into_this_file.xls").sheets(1)) && member workbooks does not evaluate to an object
*loWBFrom.sheets("Notes").copy(loWBTo("into_this_file.xls").sheets(1)) && loXLTo is not an array
&& this doesn't fail but it copies the Notes tab to a new Book1.xls file
*loWBFrom.sheets("Notes").copy()
****************************************************************************************
loWBFrom.close(0)
loXLFrom.quit
release loWBFrom, loXLFrom
loXLTo.application.range('A1').select
loXLTo.application.visible = .t.
return
&& Steps used to generate VBA code to copy notes tab of copy_this_tab.xls as 1st tab in the file into_this_file.xls
&& 1) open both c:\temp\copy_this_tab.xls and c:\temp\into_this_file.xls
&& 2) go to the copy_this_tab.xls file
&& 3) right click the "Notes" tab
&& 4) select "Move or Copy"
&& 5) select "into_this_file.xls" from the "To Book" dropdown
&& 6) check "Create a Copy" checkbox
&& 7) click "OK" button
&& VBA code generated is --> Sheets("Notes").Copy Before:=Workbooks("into_this_file.xls").Sheets(1)




Re: VBA to VFP code conversion

Tamar E. Granor

Well, Worksheet has a Copy method, so I'm not sure what the problem is.

Try saving a reference to the before worksheet and using that reference in the call:

oBefore = loWBTo.Sheets(1)
loWBFrom.Sheets("Notes").Copy(oBefore)

You might also try working with this from the Command Window to help you see exactly what's going on.

Tamar




Re: VBA to VFP code conversion

MarciaAkins

When I have automated Excel and used the copy function, I have always copied a specific range like this and it has worked just fine:

*** First get the attrib_ids for the attributes that

*** we are saving. They are in column B of the output page

lcRange = [B1:B] + Transform( OUTPUT_COUNT )

oSheet.Range( lcRange ).Copy()

The only time I have ever seens the error That the copy property did not exists was when Excel's automatic catculations was on - you will get better results if you turn automatic calculations off and call that method after the workbook has been populated.






Re: VBA to VFP code conversion

larz

Thanks Tamar but I still get the "... unable to get the copy property of the worksheet class" error even when I save the reference before the copy. If I have empty parenthesis after the copy it doesn't fail but copies the sheet to another xls file (book1.xls maybe). Don't worry about it though, I created a work-around by creating the VBA macro in the xls file that I wanted to copy the sheet from and I call that macro from VFP. I wanted to avoid creating that macro in all the xls files that I want to copy a sheet from and just pull whatever sheet from whatever xls file I wanted. The reason I copy/move the whole sheet from the xls file is that a system user is responsible for creating this part of the final output and they can change the notes and format at any time then it is pulled in when the data is processed. Also, I pull the entire tab because I don't want to reformat the columns and cells and they draw arrows etc. that don't copy with a regular range copy (just easier to get the whole sheet copied). If I figure out what the problem is with the copy I will let you know. Thanks again, Gar.