Excel Automation with VFP 2

[mikeisvfp]

Hello Experts

I have a problem with the information being displayed on my excel workbook

here is a diagram of what is suppose to be displayed


Invoice No Description quantity Price
12345 Tires 1 $40
Lights 1 $10
handles 3 $30

here is what i am seeing on the workbook

Invoice No Description quantity Price
12345 Tires 1 $40

for some reason I am only seeing the first line or entry on description

I dont know if it is My select Statement that is effecting this please help

 

[mikeisvfp]

here is my select statment in case

SELECT hedr.invoiceid, hedr.date, ;
cust.name, cust.address, cust.city, cust.phone, cust.email, ; 
hedr.licenseno, hedr.vin, hedr.makemodel, hdtl.description, hdtl.quantity, hdtl.price, ;
hdtl.labour_rate, hdtl.labour, hdtl.total, hedr.subtotal, hedr.disclaimeramount, hedr.hst, ;
hedr.total as grandtotal FROM invoicehdr hedr ;
INNER JOIN customers cust ON cust.customerid = hedr.customerid ;
JOIN invoicedtl hdtl ON hdtl.invoiceid = hedr.invoiceid  ;
where hedr.invoiceid = lcinvoiceid AND cust.customerid = lcCustomerid ; 
into cursor curInvoice

 

[Mike Lewis]

Well, the obbvious first step is to determine whether the problem lies in the SQL or with Excel automation. I suggest you stop the program after the SELECT, and take a look at the curInvoice cursor. If it shows all the rows you are expecting, then the problem lies with the Automation code. Otherwise, you need to attack the SQL.

Once you've done that, come back, and we'll try to help.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[mikeisvfp]

Hi Mike

I set step on after the select statement and all the rows are there nothing is missing and nothing is mis-spelt.

here is my code

local oExcel, oSheet
oExcel = CreateObject([Excel.Application])
if vartype(oExcel) != "O"
  * could not instantiate Excel object
  MESSAGEBOX("Invoice Auto was unable to open your version of Excel or Excel is not Installed",0+16, ;
  "Invoice Report to Excel")
  return .F.
endif
oExcel.Visible = .T.
oExcel.Workbooks.Add()
oExcel.Activesheet.Name = "Invoice Report Total"
oSheet = oExcel.ActiveSheet

*Assign Directory/Folder name to a varaible
LOCAL myDir
myDir = "C:\XLFolder"
*If Folder does not exist create folder
IF ! DIRECTORY(myDir)
     MD &myDir
ENDIF

lnRow = 0

lcInvoiceid = invoicehdr.invoiceid
lcCustomerid = customers.customerid

SELECT hedr.invoiceid, hedr.date, ;
cust.name, cust.address, cust.city, cust.phone, cust.email, ; 
hedr.licenseno, hedr.vin, hedr.makemodel, hdtl.description, hdtl.quantity, hdtl.price, ;
hdtl.labour_rate, hdtl.labour, hdtl.total, hedr.subtotal, hedr.disclaimeramount, hedr.hst, ;
hedr.total as grandtotal FROM invoicehdr hedr ;
INNER JOIN customers cust ON cust.customerid = hedr.customerid ;
JOIN invoicedtl hdtl ON hdtl.invoiceid = hedr.invoiceid  ;
where hedr.invoiceid = lcinvoiceid AND cust.customerid = lcCustomerid ; 
into cursor curInvoice

SET STEP ON 

IF _tally = 0
	MESSAGEBOX("There was no Data Found in your query",0+16, "Invoice Report Total")
		oExcel.DisplayAlerts = .F.
		oExcel.workbooks.close()
		oExcel.quit()
		oExcel = NULL
RELEASE oExcel
		RETURN .f.
ELSE
	DODEFAULT()
ENDIF

GO TOP

oSheet.Range("A1").value = [Invoiceid]
oSheet.Range("A2").value = curInvoice.invoiceid
oSheet.Range("A1").Interior.ColorIndex = 6
oSheet.Range("A1").Borders.Linestyle = 1
oSheet.Range("A2").Borders.Linestyle = 1
oSheet.Range("A1").Font.Bold = .T.


oSheet.Range("B1").value = [Date]
oSheet.Range("B2").value = curInvoice.date
oSheet.Range("B1").Interior.ColorIndex = 6
oSheet.Range("B1").Borders.Linestyle = 1
oSheet.Range("B2").Borders.Linestyle = 1
oSheet.Range("B1").Font.Bold = .T.


oSheet.Range("F3").value = curInvoice.name
*!*	oSheet.Range("F3").Interior.ColorIndex = 6
oSheet.Range("F3").Borders.Linestyle = 1
oSheet.Range("F3").Font.Bold = .F.

oSheet.Range("F4").value = curInvoice.address
*!*	oSheet.Range("F3").Interior.ColorIndex = 6
oSheet.Range("F4").Borders.Linestyle = 1
oSheet.Range("F4").Font.Bold = .F.

oSheet.Range("F5").value = curInvoice.city
*!*	oSheet.Range("F3").Interior.ColorIndex = 6
oSheet.Range("F5").Borders.Linestyle = 1
oSheet.Range("F5").Font.Bold = .F.

oSheet.Range("F6").value = curInvoice.phone
*!*	oSheet.Range("F3").Interior.ColorIndex = 6
oSheet.Range("F6").Borders.Linestyle = 1
oSheet.Range("F6").Font.Bold = .F.

oSheet.Range("F7").value = curInvoice.email
*!*	oSheet.Range("F3").Interior.ColorIndex = 6
oSheet.Range("F7").Borders.Linestyle = 1
oSheet.Range("F7").Font.Bold = .f.

oSheet.Range("A6").value = [License Plate]
oSheet.Range("A7").value = curInvoice.licenseno
oSheet.Range("A6").Interior.ColorIndex = 6
oSheet.Range("A6").Borders.Linestyle = 1
oSheet.Range("A7").Borders.Linestyle = 1
oSheet.Range("A6").Font.Bold = .T.

oSheet.Range("B6").value = [Vin]
oSheet.Range("B7").value = curInvoice.vin
oSheet.Range("B6").Interior.ColorIndex = 6
oSheet.Range("B6").Borders.Linestyle = 1
oSheet.Range("B7").Borders.Linestyle = 1
oSheet.Range("B6").Font.Bold = .T.

oSheet.Range("C6").value = [Make & Model]
oSheet.Range("C7").value = curInvoice.makemodel
oSheet.Range("C6").Interior.ColorIndex = 6
oSheet.Range("C6").Borders.Linestyle = 1
oSheet.Range("C7").Borders.Linestyle = 1
oSheet.Range("C6").Font.Bold = .T.

oSheet.Range("A9").value = [Description]
oSheet.Range("A10").value = curInvoice.description
oSheet.Range("A9").Interior.ColorIndex = 6
oSheet.Range("A9").Borders.Linestyle = 1
oSheet.Range("A10").Borders.Linestyle = 1
oSheet.Range("A9").Font.Bold = .T.

oSheet.Range("B9").value = [Quantity]
oSheet.Range("B10").value = curInvoice.quantity
oSheet.Range("B9").Interior.ColorIndex = 6
oSheet.Range("B9").Borders.Linestyle = 1
oSheet.Range("B10").Borders.Linestyle = 1
oSheet.Range("B9").Font.Bold = .T.

oSheet.Range("C9").value = [Price]
oSheet.Range("C10").value = curInvoice.price
oSheet.Range("C9").Interior.ColorIndex = 6
oSheet.Range("C9").Borders.Linestyle = 1
oSheet.Range("C10").Borders.Linestyle = 1
oSheet.Range("C9").Font.Bold = .T.

oSheet.Range("D9").value = [Labour Rate]
oSheet.Range("D10").value = curInvoice.labour_rate
oSheet.Range("D9").Interior.ColorIndex = 6
oSheet.Range("D9").Borders.Linestyle = 1
oSheet.Range("D10").Borders.Linestyle = 1
oSheet.Range("D9").Font.Bold = .T.

oSheet.Range("E9").value = [Labour]
oSheet.Range("E10").value = curInvoice.labour
oSheet.Range("E9").Interior.ColorIndex = 6
oSheet.Range("E9").Borders.Linestyle = 1
oSheet.Range("E10").Borders.Linestyle = 1
oSheet.Range("E9").Font.Bold = .T.

oSheet.Range("F9").value = [Total]
oSheet.Range("F10").value = curInvoice.total
oSheet.Range("F9").Interior.ColorIndex = 6
oSheet.Range("F9").Borders.Linestyle = 1
oSheet.Range("F10").Borders.Linestyle = 1
oSheet.Range("F9").Font.Bold = .T.


oSheet.Range("A1:A20").Borders.Linestyle = 1 && Example: Creates borders for cells in range
oSheet.Range("B1:B20").Borders.Linestyle = 1 
oSheet.Range("C1:C20").Borders.Linestyle = 1 
oSheet.Range("D1:D20").Borders.Linestyle = 1 
oSheet.Range("E1:E20").Borders.Linestyle = 1 
oSheet.Range("F1:F20").Borders.Linestyle = 1

osheet.Columns("A").ColumnWidth = 15
osheet.Columns("B").ColumnWidth = 15 
oSheet.columns("C").ColumnWidth = 15 
oSheet.columns("D").ColumnWidth = 15 
oSheet.columns("E").ColumnWidth = 15
oSheet.columns("F").ColumnWidth = 15

oSheet.Columns("C").NumberFormatLocal = "$0.00_ " &&add decimal place 0.00
oSheet.Columns("D").NumberFormatLocal = "$0.00_ " 
oSheet.Columns("F").NumberFormatLocal = "$0.00_ " 
oSheet.columns("A:F").HorizontalAlignment = 2 &&Align Left
      
nAnswer = MESSAGEBOX("Would You Like To Save XLWorkBook C:\XLFolder", 4+32, "Save Active XLWorkBook")

IF nAnswer = 6
	STORE "C:\XLFolder\xlworkbook" + PADL(invoicehdr.invoiceid,8,[0]) + ".xlsx" TO mcExcelFile
IF FILE(mcExcelFile)
 	DELETE FILE (mcExcelFile) 
ENDIF	
	oSheet.SaveAs(mcExcelFile)	
ENDIF

nAnswer = MESSAGEBOX("Would You Like To Print XLWorkBook", 4+32, "Print Active XLWorkBook")
IF nAnswer = 6
	oSheet.pagesetup.Orientation = 2
	oSheet.pagesetup.LeftMargin = 0.8
	oSheet.pagesetup.RightMargin = 0
	oSheet.pagesetup.TopMargin = 0.75
	oSheet.pagesetup.BottomMargin = 0.75
	oSheet.pagesetup.HeaderMargin = 0.3
	oSheet.pagesetup.FooterMargin = 0.3
	oSheet.PrintOut(1,2,1) 
ENDIF

 

[TamarGranor]

It looks like you're only copying one row. You need to loop through your cursor and send each record.

Alternatively, use COPY TO to create an Excel file, and then use Automation to open it and format it as you want.

Tamar

 

[mikeisvfp]

Hi Tamar

How do I loop through My cursor?

 

[mikeisvfp]

can anyone give me an example that i can work with on looping through a cursor?

 

[JimmyTheFish]

At the risk of missing something more complicated:

Select YourCursor
Scan
* Do your stuff here
* e.g. load each row to Excel

endscan

 

[Mike Lewis]

How do I loop through My cursor?

You treat a cursor exactly like a table. So you can use a SCAN/ENDSCAN to loop through it.

It's difficult to give you the actual code you need, because I don't know how the rows in your spreadsheet relate to the records in the cursor. But you would probably start the SCAN loop where you have the GO TOP, and end it just before the message asking if they want to save it. Each time round the loop, you would need to increment the row number in some way.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

If you want one Excel per Record, then the only two lines to add are literally SCAN in the line right after GO TOP, or instead of GO TOP, and ENDSCAN at the end of the code.

But it's not really ideal, if there are many records to ask per record to create a file or not, printit or not.

If you want your data in one sheet, the main problem with your code are hardcoded Excel CELL Names. If you need several records in one worksheet, you would need to vary that and repeat whyt you output in A1:F20 in eg G1:L20. For that matter, you better work with Sheet.Cells(Rownumber, Columnnumber), that's adressing cells by numerical indexes, which you can shift by an offset, eg start in row1 or start in row 7,14,21, etc. by Sheet.Cells(LineOffset+Rownumber, Columnnumber) with LineOffset = (recno()-1)*7.

Bye, Olaf.

 

[foxmuldr3]

Also, it is much faster to populate a multi-dimensional array and then do a single range update with both your data, and formatting options.

Example: Range with Array

Best regards,
Rick C. Hodgin

 

[TamarGranor]

Just want to point out, btw, that you don't need GO TOP when you use SCAN. It's automatic, unless you include the FOR clause.

Tamar

 

[Mike Lewis]

Also, it is much faster to populate a multi-dimensional array and then do a single range update with both your data, and formatting options.

Another very fast way is to use _VFP.DataToClip() to copy the contents of the cursor to the clipoard (delimited with tabs), and then to programmaticaly paste that into the spreadsheet.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[foxmuldr3]

Another very fast way is to use _VFP.DataToClip() to copy the contents of the cursor to the clipoard (delimited with tabs), and then to programmaticaly paste that into the spreadsheet.

Never knew that one. Awesome.

Best regards,
Rick C. Hodgin

 

[Dan Olsson]

A small warning though - it will only copy records from your current row and downwards.
If you want the whole table - be sure to be at the top. /Dan

 

[mikeisvfp]

finally figured it out....thanks guys

@ Tamor looping through the cursor fixed my problem using SCAN

Thanks everyone once again

 

[Dan Freeman]

According to some tellings, _VFP.DataToClip() followed by a simple oXL.Paste() into Excel can be up to 80% faster than other row-by-row manipulations. It "reaches across" only once.