Alpha Video Training
Page 2 of 2 FirstFirst 12
Results 31 to 42 of 42

Thread: Export to Excel Tabs?

  1. #31
    "Certified" Alphaholic Keith Hubert's Avatar
    Real Name
    Keith Hubert
    Join Date
    Jul 2000
    Location
    London, UK
    Posts
    6,930

    Default 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.
    Regards
    Keith Hubert
    Alpha Guild Member
    London.
    KHDB Management Systems
    Skype = keith.hubert


    For your day-to-day Needs, you Need an Alpha Database!

  2. #32
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default 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.
    There can be only one.

  3. #33
    "Certified" Alphaholic Keith Hubert's Avatar
    Real Name
    Keith Hubert
    Join Date
    Jul 2000
    Location
    London, UK
    Posts
    6,930

    Default 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?
    Regards
    Keith Hubert
    Alpha Guild Member
    London.
    KHDB Management Systems
    Skype = keith.hubert


    For your day-to-day Needs, you Need an Alpha Database!

  4. #34
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default 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.
    There can be only one.

  5. #35
    "Certified" Alphaholic Keith Hubert's Avatar
    Real Name
    Keith Hubert
    Join Date
    Jul 2000
    Location
    London, UK
    Posts
    6,930

    Default 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?
    Regards
    Keith Hubert
    Alpha Guild Member
    London.
    KHDB Management Systems
    Skype = keith.hubert


    For your day-to-day Needs, you Need an Alpha Database!

  6. #36
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default 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.

    Code:
    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.


    Code:
    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
    There can be only one.

  7. #37
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,610

    Default Re: Export to Excel Tabs?

    Quote Originally Posted by Keith Hubert View Post
    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.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  8. #38
    "Certified" Alphaholic Keith Hubert's Avatar
    Real Name
    Keith Hubert
    Join Date
    Jul 2000
    Location
    London, UK
    Posts
    6,930

    Default Re: Export to Excel Tabs?

    Thanks Guys.

    You are just tooo much.
    Regards
    Keith Hubert
    Alpha Guild Member
    London.
    KHDB Management Systems
    Skype = keith.hubert


    For your day-to-day Needs, you Need an Alpha Database!

  9. #39
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,610

    Default Re: Export to Excel Tabs?

    Quote Originally Posted by Keith Hubert View Post
    Thanks Guys.

    You are just tooo much.
    Hey, I resemble that remark... ;)
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  10. #40
    Member
    Real Name
    Donald Raymond
    Join Date
    Jul 2000
    Location
    Long Island, NY
    Posts
    153

    Default 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

  11. #41
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Grand Rapids, Michigan
    Posts
    4,192

    Default 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.
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  12. #42
    Member
    Real Name
    Donald Raymond
    Join Date
    Jul 2000
    Location
    Long Island, NY
    Posts
    153

    Default 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.

    HTML Code:
    	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")

Similar Threads

  1. Export to Excel
    By efrierson in forum Alpha Five Version 9 - Desktop Applications
    Replies: 0
    Last Post: 10-20-2009, 12:40 PM
  2. Excel Export
    By steinmanal in forum Alpha Five Version 9 - Desktop Applications
    Replies: 3
    Last Post: 02-19-2009, 01:48 PM
  3. Import excel then export to excel
    By popellis in forum Alpha Five Version 8
    Replies: 4
    Last Post: 04-03-2008, 10:13 PM
  4. Export to Excel
    By Bob Whitaker in forum Alpha Five Version 5
    Replies: 0
    Last Post: 07-13-2005, 11:53 PM
  5. Export to Excel
    By Mike Reed in forum Alpha Five Version 1
    Replies: 3
    Last Post: 07-14-2004, 01:27 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •