Excel Export Puzzler

[LzyOldFrt]

I've been trying (fruitlessly) to solve this problem. Back in the day, I was able to do this quite easily in Access, but I have committed to move everything to A5 and can't get this to work.

In the detail view of a customer grid, I want a button that will trigger the following function. I would like the button to open an Excel file and populate certain cells with customer data. The estimator can then use the excel file to complete an onsite estimation. He would then be able to save the file and upload it back. But, alas, I can't even get the first part done. Can anyone please tell me where I'm messing up or steer me in the right direction?

FUNCTION Interior AS F ( )

dim MyExcel as P
dim MyWorkbook as P
dim Mywoorkbook1 as P
dim MyRange as P
dim fn as C
dim LName as C
dim FName as C
dim Email as C
dim HomePhone as C
dim Address1 as C
dim City as C
dim WorkPhone as C
dim Zip as C
dim Referral as C

LName= e._currentRowDataNew.Lastname
FName=e._currentRowDataNew.Firstname
Email=e._currentRowDataNew.Email
HomePhone=e._currentRowDataNew.Hmphone
Address1=e._currentRowDataNew.Address1
City=e._currentRowDataNew.City
WorkPhone=e._currentRowDataNew.Workphone
Zip=e._currentRowDataNew.Zip
Referral=e._currentRowDataNew.Referral

fn = "interior.xlsx"
MyWorkbook = ole.GetObject(fn)

MyWorksheet1 = MyWorkbook.Worksheets(1)

MyRange = MyWorksheet1.Range("A10")
MyRange.value = (LName+", "+FName)

MyRange = MyWorksheet1.Range("F10")
MyRange.value = Email
MyRange=MyWorksheet1.Range("I10")
MyRange.value = HomePhone
MyRange=MyWorksheet1.Range("A12")
MyRange.value = Address1
MyRange = MyWorksheet1.Range("L12")
MyRange.value = City
MyRange = MyWorksheet1.Range("N10")
MyRange.value = WorkPhone
MyRange = MyWorksheet1.Range ("R12")
MyRange.value = Zip
MyRange = MyWorksheet1.Range ("U12")
MyRange.value = Date()
MyRange = MyWorksheet1.Range ("D13")
MyRange.value = Referral

MyWorkbook.Application.visible = .T.

MyWorkbook.windows(1).activate()

END FUNCTION

[marvinbase]

Re: Excel Export Puzzler

Hello,

Your current code is relying on an OLE object to interact with excel, which means you will need to have excel installed on your A5 server. Also, it doesn't look like you are specifying the full filepath for your "interior.xlsx" file

With v11, you no longer need to rely on an OLE object to create excel files, so you don't need a copy of excel installed anywhere. However, it will require you to rewrite your code to use the Office Class. See the wiki here for the full documentation.

[Clipper87]

Re: Excel Export Puzzler

This is some code I wrote to update Excel fields with data from records. xlsx format has issues and I could not get it to work. xls does work.

function UpdateExcelFromRecord as c (e as p)

'This is an AJAX callback function (serverside)

'If no Excel file exists for the record (the first time a download is invoked):
'Load Excel template file from Template table
'Update Excel fields with record data
'Save the Excel file into the current record
'Download the Excel file

'If an excel file exists for the record
'Download the Excel file

dim flag as l
dim cn as sql::connection
dim sqlCommand as c
dim rs as sql::ResultSet
dim args as sql::arguments
'using the ExcelDocument object will generate corrupted .xlsx sheets (the sheet.write method has some issue)
'dim Doc as Office::ExcelDocument 
'Use the Excel2003Document object (.xls files) and the sheets will be ok
dim Doc as Office::Excel2003Document 
dim Sheet as Office::Spreadsheet
dim memblob as b 

flag = cn.open("::Name::KNW")

args.Add("recID",e._currentRowDataNew.ID)

sqlCommand = "select * from portefolio where ID = :recID"
flag = cn.execute(sqlCommand,args)
rs = cn.ResultSet

if rs.data("ExcelFileName") = null_value()
 'Load Excel template file from Template table
 sqlCommand = "Select * from Template"
 flag = cn.execute(sqlCommand)
 rs = cn.ResultSet
 memblob = rs.data("TemplFile")
 sqlCommand = "Select * from portefolio where ID = :recID"
 flag = cn.execute(sqlCommand,args)
 rs = cn.ResultSet
else
 'Load existing Excel file & update with current field data
 memblob = rs.data("ExcelFile")
end if

Doc.LoadFromBlob(memblob)
Sheet = Doc.GetSheet(1)

'Update Excel fields with record data

'Write in cells a2,a3,a4
sheet.write(2,1,rs.data("contact_id"))
sheet.write(3,1,rs.data("Avtalenr"))
sheet.write(4,1,rs.data("Produkt"))

Doc.SaveToBlob(memblob)
Doc.Clear()

args.add("newblob",memblob)
args.add("newfilename",rs.data("Avtalenr")+".xls")
args.add("newfileext","xls")

'Save the Excel file into the current record
sqlCommand = "Update portefolio set ExcelFile = :newblob, ExcelFileName = :newfilename, ExcelFileExt = :newfileext where ID = :recID"
flag = cn.execute(sqlCommand,args)

cn.Close()

'Return javascript to the browser to refresh the grid so you can see the excel filename appear
UpdateExcelFromRecord = "RefreshGridAndDownload();"

end function