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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Off Topic - Visual Fox Pro Pro's
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sikharma13
Starting Member

Philippines
44 Posts

Posted - 10/31/2013 :  08:05:05  Show Profile  Reply with Quote
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

Philippines
44 Posts

Posted - 10/31/2013 :  08:22:44  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000