Export to Excel Tabs?

[Keith Hubert]

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]

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]

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]

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]

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]

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()
XlApp.Sheets("Tab 2").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()
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

[Al Buchholz]

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.

[Keith Hubert]

Thanks Guys.

You are just tooo much.

[Al Buchholz]

Thanks Guys.

You are just tooo much.

Hey, I resemble that remark... ;)

[DORaymond]

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

[Mike Wilson]

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]

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")