Export to Excel Tabs?

[DORaymond]

Re: Export to Excel Tabs?

Mike - thanks for your reply. I worked out what I wanted to accomplish using bits of the code on this thread and some internet searching. I have an app where I needed one records worth of data at a time to be injected into different excel sheets.

	Dim templatex AS C
	Dim cutsheetx AS C 
	Dim xlApp as p
	dim myCell as c
	
	
	templatex = "P:/Donald/" +alltrim(var->Template)+ ".xlsx" 'This is the template file to use                                                        
	cutsheetx = "P:/Donald/" +alltrim(str(var->Cutsheetkey))+ ".xlsx" 'This is the name of the file that is created/saved
	
	if file.exists(cutsheetx) then
	    templatex = cutsheetx
	    
	 else
	 	templatex = templatex
	end if
	
	xlApp = ole.create("Excel.Application")
	xlApp.Workbooks.Open(templatex)
	xlApp.sheets("Sheet2").Select()
	'xlApp.Visible = .T. 'comment this line out if you don't want to see Excel
	tbl = table.open("cutsheet")
	query.filter = "Cutsheetkey = var->cutsheetkey"
	query.order = "recno()"
	query.options = "N"
	query.description = "Temporary Query"
	ix = tbl.query_create()
	recs = ix.records_get()

	
	
		xlApp.Range("B3").Select()
		xlApp.ActiveCell.Formula = alltrim(tbl.Unit_Label)
		xlApp.Range("B4").Select()
		xlApp.ActiveCell.Formula = alltrim(tbl.Str_Type)
		xlApp.Range("B5").Select()
		xlApp.ActiveCell.Formula = alltrim(tbl.Insidedim)
		xlApp.Range("B6").Select()
		xlApp.ActiveCell.Formula = alltrim(str(tbl.Topofwall))
		
		query.filter = ""
			
        tbl.close()
	xlApp.Columns("B").Autofit()
	xlApp.Range("A1").Select()
	xlApp.sheets("Sheet1").Select()
	xlApp.Range("A1").Select()
	xlapp.DisplayAlerts = .F.
	xlApp.ActiveWorkbook.SaveAs(cutsheetx) '-4143 is latest supported version,-4143
	xlApp.Workbooks.Close()
	xlapp.DisplayAlerts = .T.
	xlApp.Quit()
	
	sys_open("P:/Donald/" +alltrim(str(var->Cutsheetkey))+ ".xlsx")

[Mike Wilson]

Re: Export to Excel Tabs?

Hi Don,
When you say that you need to "integrate" Alpha with Excel, can you explain what that means more specifically? The application I developed and manage now has so many interfaces with Excel that I wonder what your definition of "integrate" is.

[DORaymond]

Re: Export to Excel Tabs?

Stan - This is an old post, but I have a project where I need to integrate Alpha with Excel. Do you know where I can find documentation where I can learn more about the code you have used in this thread?

Thanks - Don

[Al Buchholz]

Re: Export to Excel Tabs?

Thanks Guys.

You are just tooo much.

Hey, I resemble that remark... ;)

[Keith Hubert]

Re: Export to Excel Tabs?

Thanks Guys.

You are just tooo much.

[Al Buchholz]

Re: Export to Excel Tabs?

Hi Stan,

Sorry, did not explain properly.

What I had in mind was loading data from another table into the same spreadsheet but on another named tab.

The other aspect is, how do I control the path to see the spreadsheet in the My Documents folder?

Keith

Using the Alpha DAO export, I export multiple tables to a single spreadsheet with each table in it's own tab.

I can control what field go into a column.

Stan's method is fantastic if you need to have individual cell control, but requires a bit more work - as expected.

[Stan Mathews]

Re: Export to Excel Tabs?

I believe the normal location of My Dcouments is in the pattern

C:\Documents and Settings\smathews\My Documents

So you have to know the user name and concatenate.

user = "smathews"

? "C:\Documents and Settings\\"+user+"\My Documents"
= "C:\Documents and Settings\smathews\My Documents"

loading data from another table into the same spreadsheet but on another named tab.

You select the other tab as described and open the other table.

From your code export_sales2, just add another section.

FUNCTION export_sales2 AS L (excelname AS C, newname AS C )
export_sales2 = .F.
Dim xlApp as p
dim myCell as c
xlApp = ole.create("Excel.Application")
xlApp.Workbooks.Open("keith_sheet")
XlApp.Sheets("Tab 1").Select()
xlApp.Visible = .T. 'comment this line out if you don't want to see Excel
tbl = table.open("new_sales")
query.filter = ".T."
query.order = "recno()"
query.options = "I"
query.description = "Temporary Query"
ix = tbl.query_create()
recs = ix.records_get()
tbl.fetch_first()
start_cell = 9
FOR t = 0 TO recs
xlApp.Range("A"+alltrim(str(start_cell+t))).Select()
xlApp.ActiveCell.Formula = alltrim(tbl.sales_rep)
xlApp.Range("B"+alltrim(str(start_cell+t))).Select()
xlApp.ActiveCell.Formula = tbl.Total_Invoice_Items__Extension
'xlApp.Range("C"+alltrim(str(start_cell+t))).Select()
'xlApp.ActiveCell.Formula = alltrim(tbl.email)
tbl.fetch_next()
NEXT t
tbl.close()
xlApp.Columns("A:B").Autofit()
xlApp.Range("A1").Select()
[COLOR="Blue"]XlApp.Sheets("[COLOR="red"]Tab 2[/COLOR]").Select()
xlApp.Visible = .T. 'comment this line out if you don't want to see Excel
tbl = table.open("some_other_table")
query.filter = ".T."
query.order = "recno()"
query.options = "I"
query.description = "Temporary Query"
ix = tbl.query_create()
recs = ix.records_get()
tbl.fetch_first()
start_cell = 9
FOR t = 0 TO recs
xlApp.Range("A"+alltrim(str(start_cell+t))).Select()
xlApp.ActiveCell.Formula = alltrim(tbl.sales_rep)
xlApp.Range("B"+alltrim(str(start_cell+t))).Select()
xlApp.ActiveCell.Formula = tbl.Total_Invoice_Items__Extension
'xlApp.Range("C"+alltrim(str(start_cell+t))).Select()
'xlApp.ActiveCell.Formula = alltrim(tbl.email)
tbl.fetch_next()
NEXT t
tbl.close()
xlApp.Columns("A:B").Autofit()
xlApp.Range("A1").Select()[/COLOR]
xlapp.DisplayAlerts = .F.
xlApp.ActiveWorkbook.SaveAs(newname,-4143) '-4143 is latest supported version
xlApp.Workbooks.Close()
xlapp.DisplayAlerts = .T.
xlApp.Quit()
ex_keith = .T.
END FUNCTION

[Keith Hubert]

Re: Export to Excel Tabs?

Hi Stan,

Sorry, did not explain properly.

What I had in mind was loading data from another table into the same spreadsheet but on another named tab.

The other aspect is, how do I control the path to see the spreadsheet in the My Documents folder?

[Stan Mathews]

Re: Export to Excel Tabs?

Not sure I understand the question fully

If you want to address a specific sheet you use

XlApp.Sheets("sheetname").Select() 'note quotes

or

XlApp.Sheets(sheetnumber).Select()

If you want to put the same data

(a) but on a different tab

you repeat the code section and change the sheet number or name. You could also create a list of sheet numbers or names and feed them to a

for each..

next
loop

For (b) different data onto a different tab you change sheetnumbers or names and have a different set of data insertion instructions.

[Keith Hubert]

Re: Export to Excel Tabs?

Hi Stan,

Back to this again.

OK, I can enter data into a selected tab, the next question is what is the best method to use to enter data into the same spreadsheet (a) but on a different tab and (b) different data onto a different tab?

[Stan Mathews]

Re: Export to Excel Tabs?

If they're not going to give you the password there's not much we can do. If they give you the password then you can unprotect it manually and save it. Then operate on the unprotected version.

[Keith Hubert]

Re: Export to Excel Tabs?

Hi Stan,

Yes you are correct, Excel does stop and ask for a password.

The spreadsheet I'm trying to export to is not mine, which means I dont have full access to it.

[Stan Mathews]

Re: Export to Excel Tabs?

The reason I asked about the protection is that the command to unprotect the sheet, in xbasic, is simply

xlApp.ActiveSheet.Unprotect()

I don't see a way to supply the password, if there is one, to the command.

[Stan Mathews]

Re: Export to Excel Tabs?

Try changing the xlapp = ole.create() to

xlApp = ole.getobject("","Excel.Application")

The OLE.GETOBJECT() method uses an existing OLE automation object, if available. Otherwise, it creates a new one.

[Keith Hubert]

Re: Export to Excel Tabs?

Hi Stan,

Thanks for getting back so quickly.

Unfortunately the FOR t = 0 TO recs - 1 did not make nay difference, I still get the last record duplicated.

As I was writing this I changed the start_cell to start_cell = 8 and that has eliminated the duplication.

If I remove the .Autofit() I no longer get the error message but I have to make sure Excel is not open first. I need to experiment to see what happens when I try to add data to another tab on the same spreadsheet.