PDA

View Full Version : Updating Range with Visual FoxPro Array



wrussell
10-06-2005, 02:12 AM
I would like to add values to Excel using Visual FoxPro. The code is detailed below.

The problem that I have is that the whole range of cells are filled with the contents of the first element only, in the example below all cells contain "ORD 1"

Any help with this would be most appreciated.

Wayne

*Start a new workbook in Excel
oExcel = CreateObject("Excel.Application")

oBook = oExcel.Workbooks.Add

oExcel.Visible=.t.

*Create an array with 3 columns and 100 rows

Dime DataArray(100,3)

For r = 1 To 100

DataArray(r, 1) = "ORD" + STR(r,6,0)

DataArray(r, 2) = RAnd() * 1000

DataArray(r, 3) = DataArray(r, 2) * 0.7

Next

oSheet = oBook.Worksheets(1)

*Transfer the array to the worksheet starting at cell A2

oSheet.Range("A2").Resize(100, 3).Value = DataArray

*Save the Workbook and Quit Excel

oBook.SaveAs("C:Book1.xls")

oExcel.Quit

Norie
10-06-2005, 07:32 AM
I just tried this code in VBA and it worked.

Option Base 1
Sub Test
Dim DataArray(100, 3)
For r = 1 To 100
DataArray(r, 1) = "ORD " & r
DataArray(r, 2) = Rnd() * 1000
DataArray(r, 3) = DataArray(r, 2) * 0.7
Next
Set oSheet = Worksheets(1)

oSheet.Range("A2").Resize(100, 3).Value = DataArray
End Sub

wrussell
10-06-2005, 08:08 AM
Thank you for your reponse. The code that I have shown is written in Visual FoxPro and not in VBA. I am creating references to EXCEL via Automation and this my problem with the Array.

Norie
10-06-2005, 08:46 AM
I know your code is in FoxPro, but I can't see any code that is specific to FoxPro other than the Str and RAnd functions.

Have you checked that your array is being populated as expected?