Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Off Topic - Visual Fox Pro Pro's

Author  Topic 

sikharma13
Starting Member

44 Posts

Posted - 2013-10-31 : 08:05:05
Anyone? Please Let me know. i have some questions that
you might know.. this is the only site that i know
most of pro's are online.. Thanks

VFP9.0 via MySQL 5.0

sikharma13
Starting Member

44 Posts

Posted - 2013-10-31 : 08:22:44
its about excel automation via sql

i want to insert my query here.. im new to sql and vfp
and i just dont know what to do..sorry for being noob. :)

here is my query..


"select province,municipality,barangay,household_id,entry_id,lastname,firstname,middlename,ext_name,no_eli_for_educ_grant,no_ncom_educ_m1,no_ncom_educ_m2,educ_grant_m1,educ_grant_m2,t_educ_grant,no_ncom_dew,no_eli_for_health_grant,no_ncom_hcv_m1,no_ncom_hcv_m2,no_ncom_bhcv,fncom_m1,fncom_m2,hg_m1,hg_m2,h_grant,total from py_abcdeeh "


and here is the code i where i want to insert my query..

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


please i really need it.. thank you so much!

VFP9.0 via MySQL 5.0
Go to Top of Page
   

- Advertisement -