Converting VBA to VFP 2

[kyletreyfield]

I am trying to use the following VBA code but don't know how to put it in the language VFP can use.

With .ActiveWorkBook.ActiveSheet  

    Dim tbl As ListObject
    Dim rng As Range

    Set rng = Range(Range("A1"), Range("A1").SpecialCells(xlLastCell))
    Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes)
    tbl.TableStyle = "TableStyleMedium15"
              
  Endwith

I know I need to express the variables differently but don't know exactly how to do it.

Thanks in advance.

 

[Chris Miller]

1. Variable declaration can be done with LOCAL instead of DIM.
2. Assignments don't need SET in VFP, simply variablename = value
3. VFP also has WITH
4. VFP doesn't have predefined constants like xlLastCell. So find out what value that is and #DEFINE xlLastCell

In the bigger context of your code, this must be nested into another WITH, The first level of WITH needs to be an object or an object reference variable.

For example:

WITH _VFP
   WITH [highlight #FCE94F].[/highlight]Application
      .Caption = 'Test'
   ENDWITH 
ENDWITH

In VFP you'd start automation with something like

LOCAL loExcel
loExcel = CreateObject('Excel.Application')

Then the WITH usage could be:

WITH loExcel
   WITH [highlight #FCE94F].[/highlight]ActiveWorkBook.ActiveSheet
      LOCAL tbl, rng
      rng = [highlight #FCE94F].[/highlight]Range([highlight #FCE94F].[/highlight]Range("A1"...
   ENDWITH 
ENDWITH

Since the Object model of Excel is the same to VFP than to VBA, you have the same names and methods and objects, etc., there's -8almost) no change there, just notice within WITH and ENDWITH anything addressing a method or subobject neds to start with a dot (as highlighted).

Chriss

 

[Chris Miller]

PS: Imagine the concatenation of the object references of nested WITH statements, ie in my first example the inner nested WITH statement address [highlight #FCE94F]_VFP[/highlight][highlight #EDD400].Application.Caption[/highlight], so this could also be written as _VFP.Application.Caption = 'Test'.


Chriss

 

[Mike Lewis]

A simplistic literal translation would be as follows:

LOCAL tbl, rng
WITH .activeworkbook.activesheet
    rng = .RANGE(RANGE("A1"), .RANGE("A1").specialcells(xllastcell))
    tbl = .listobjects.ADD(xlsrcrange, rng, , xlyes)
    tbl.tablestyle = "TableStyleMedium15"
ENDWITH

However, you wouldn't be able to run that code on its own. You would first need an object reference to your Excel application, and perhaps also actually open or create a workbook. Not to mention saving the workbook and cleaning up afterwards. It's difficult to give you code for those steps without knowing the context or the requirements.

Also, you would need to define your constants (xllastcell, etc.). Those definitions are not built into VFP.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Forum members are always happy to help translate VB to VFP. But, really, that's not the way to solve this kind of problem. Rather than asking for a literal translation and them blindly pasting the code into your application, you would do much better to learn the principles of Excel automation from within VFP, and then write the code yourself from scratch.

In fact, as you can see from the above posts, the syntax is very similar. You only need to learn the VFP specifics. There are plenty of resources available to help you do that. A good place to start would be this paper: Driving Word and Excel from Visual FoxPro

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[kyletreyfield]

Thanks to both of you. I've been trying to learn object-oriented programming and VBA related to VFP but always run into situations I can't figure out. I will read the article you mentioned.

As usual, I REALLY appreciate your help (both Chris and Mike).

Thanks again.

 

[kyletreyfield]

I'm an idiot. I read through the whole article you recommended and spent about 4 hours messing around with the script and it still doesn't work. I think it's because I didn't define everything properly as you suggested but after searching on the Internet I'm still at a loss.

All I'm trying to do is automatically format a small group of cells (table) within a spreadsheet. Now I get an error that says 'range.prg' does not exist.

Here is the entire set of code:

oX=CreateObject("EXCEL.APPLICATION")
WITH oX
oX.Visible=.T.
oX.Workbooks.Open("c:\rfportal\combine_project\test.xlsx")

LOCAL tbl, rng

With .ActiveWorkBook.ActiveSheet  
	    rng = .Range(Range("A1"), .Range("A1").specialcells(xllastcell))
	    tbl = .listobjects.ADD(xlsrcrange, rng, , xlyes)
	    tbl.tablestyle = "TableStyleMedium15"
ENDWITH 

oX.ActiveWorkbook.SaveAs('c:\rfportal\combine_project\test_final')

endwith

Thanks.

 

[Mike Lewis]

Well, you are making progress. But you still have a couple of steps to go.

I think the error occurred in the following line, in particular in the second reference Range(). You just need a dot in front of it:

rng = .Range([highlight #EDD400].[/highlight]Range("A1"), .Range("A1").specialcells(xllastcell))

In addition, your WITH line needs a reference to the application object:

With [highlight #EDD400]oX.[/highlight]ActiveWorkBook.ActiveSheet

You will also need to define the Excel constants: xlyes, xlsrcrange and xllastcell. These have meaning in Excel but not in VFP, so you will probably need to refer to the Excel Help to find the relevant values.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

You will also need to define the Excel constants: xlyes, xlsrcrange and xllastcell.

I've saved you the trouble. The relevant values are:

xlYes       1
xlLastCell  11
xlSrcRange  1

Just plug those numbers into your code, in place of the above names.

For future reference, I found these values in the Excel Class Browser. The article I mentioned (above) explains how to do that.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[tomk3]

Hello,

if you need the value of a constant :

In VBA Editor mark (highlight) constant, right mouseklick , quickinfo.
or search for excel constants list, shows something like this :

https://docs.microsoft.com/de-de/office/vba/api/excel.constants

There was a way to use VFP ibjectcatalog (extras), but it seems not to work in win10 (or office2019) anymore.

regards
tom

 

[Chris Miller]

Just to stres out once more how objcts are addressed (in any language, that's not a VFP specific thing):

There are root objects, like THISFORM, THIS, or a variable containing an object reference, like loExcel once you created the automation object with loExcel = CreateObject('Excel.Application')
You address propertis of such objects via -> or th dot, most languages now us the shorter dot, -> is very speciic ti C and C++.

You can addreess propreties of an object with thing like

THISFORM[highlight #FCE94F].Caption[/highlight] = 'My Window Caption'
Mssagebox(THIS[highlight #FCE94F].Name[/highlight])

And you can call methods of objects the same way

THISFORM[highlight #FCE94F].Release()[/highlight]

For nested objects (for example a textbox in a page of a pageframe on a form you can have multiple dots, like this:

THISFORM.PageFrame1.Page2.Text1.Value = 'Hello, World'

It's the same in the Object model of Excel automation, you have he root Application object, and in case of Excel that has a collection of Workbooks, which each have a collection of Sheets, etc.

You don't want to write code like

loRange1 = loExcel.ActiveWorkbook.ActiveSheet.Range('A1:C3')
loRange2 = loExcel.ActiveWorkbook.ActiveSheet.Range('D1:F3')

WITH helps to reduce what you need to write out:

WITH loExcel.ActiveWorkbook.ActiveSheet
   loRange1 = .Range('A1:C3')
   loRange2 = .Range('D1:F3')
ENDWITH

And this can also be nested. you coul want to addresss both the Activesheet (to add something there) and then the ActiveWorkbook, to save it, so you'd nest the WITH statements like this:

WITH loExcel.ActiveWorkbook
   WITH .ActiveSheet
      loRange1 = .Range('A1:C3')
      loRange2 = .Range('D1:F3')
      ...do something with the ranges
   ENDWITH
   .SaveAs('MyWorkbook.xlsx')
ENDWITH

If you never need to address another object than the ActiveSheet, you can also jump right in to the Sheet object in one WITH:

WITH loExcel.ActiveWorkbook..ActiveSheet
   loRange1 = .Range('A1:C3')
   loRange2 = .Range('D1:F3')
   ...do something with the ranges
ENDWITH

Your code snippet can't be working in itself, it must be embedded in another WITH, as ActivWorkbook is no root object. At least not to VFP. For Excel automation, the only root object is the application object, anything else is subordinated to it.

Chriss

 

[kyletreyfield]

It works! Thanks again. I think I'm learning how to do it now.

Appreciate you.