Alpha Video Training
Results 1 to 1 of 1

Thread: Collect data from all tabs of spreadsheet to the first tab

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

    Default Collect data from all tabs of spreadsheet to the first tab

    Ever had someone supply you with a spreadsheet where the format was the same for each tab but each represented some subset of the data? Possibly you then copied and pasted from sheets 2 to the end back to sheet 1?

    Alpha can do that for you.

    Code:
    'Date Created: 08-Jul-2002 10:14:12 PM
    'Last Updated: 06-Jun-2013 02:54:58 PM
    'Created By  : SMathews
    'Updated By  : SMathews
    'sample usage - pull_tabs_to_one_sheet("c:\temp\book1.xls")
    'combined to one tab version saves as c:\temp\master combined.xls
    FUNCTION pull_tabs_to_one_sheet AS L (docName AS C)
    	pull_tabs_to_one_sheet = .F.
    	Dim xlApp as p
    	Dim myCell as c
    	'concatenate spreadsheet name
    	Dim sht_name as c
    	sht_name = "Master combined"
    
    	xlApp = ole.getobject("","Excel.Application")
    	xlApp.Workbooks.Open(docname)
    	'debug(1)
    	'xlApp.Visible = .T. 'comment this line out if you don't want to see Excel
    	IF xlApp.ActiveWorkbook.ReadOnly
    		ui_msg_box("Alert","Halting because file is read-only.",UI_OK)
    		xlapp.DisplayAlerts = .F.
    		xlApp.Workbooks.Close()
    		xlapp.DisplayAlerts = .T.
    		xlApp.Quit()
    		delete xlApp
    		exit function
    	END IF
    	xlApp.DisplayAlerts = .F.
    	xlApp.ActiveWorkbook.Saveas(docname,-4143)
    	xlApp.DisplayAlerts = .T.
    	
    	shts = xlApp.Worksheets.Count
    	
    	FOR qx = 2 TO shts 
    		xlApp.Worksheets(qx).Select()
    		lastrow = xlApp.Activecell.SpecialCells("11").Row
    		lastcol = xlApp.Activecell.SpecialCells("11").Column
    		lastcell = xlindx_to_txt(lastcol)+lastrow
    		xlApp.Range("A1:"+lastcell).Select()
    		xlApp.Selection.Copy()
    		xlApp.Worksheets(1).Select()
    		lastrow = xlApp.Activecell.SpecialCells("11").Row
    		xlApp.Range("A"+alltrim(str(lastrow+1))).Select()
    		xlApp.ActiveSheet.Paste()
    		xlapp.DisplayAlerts = .F.
    		xlApp.ActiveWorkbook.Saveas("C:\temp\\" + sht_name,-4143)
    		xlapp.DisplayAlerts = .T.
    	NEXT qx
    xlApp.Range("A1").Select()
    xlapp.DisplayAlerts = .F.
    xlApp.ActiveWorkbook.Saveas("C:\temp\\" + sht_name,-4143)
    xlApp.Workbooks.Close()
    xlApp.DisplayAlerts = .T.
    xlApp.Quit()
    Delete xlApp
    pull_tabs_to_one_sheet = .F.
    END FUNCTION
    Importable version of function.
    pull_tabs_to_one_sheet - 06-06-2013 -1.txt

    The format of each tab doesn't have to be the same nor is each tab limited to the same number of columns. Just a likely scenario.

    Code:
    'Date Created: 29-Jan-2009 04:49:54 PM
    'Last Updated: 29-Jan-2009 04:49:54 PM
    'Created By  : SMathews
    'Updated By  : SMathews
    FUNCTION xlindx_to_txt AS C (colnum AS N )
      If Colnum > 26 Then
        xlindx_to_txt = Chr(Int((Colnum - 1) / 26) + 64) + Chr((mod((Colnum - 1),26) ) + 65)
      Else
        xlindx_to_txt = Chr(Colnum + 64)
      End If
    END FUNCTION
    Last edited by Stan Mathews; 06-06-2013 at 05:28 PM. Reason: added called function
    There can be only one.

Similar Threads

  1. Tab Control - Tabs Vanishinh
    By richkerr in forum Alpha Five Version 10 - Desktop Applications
    Replies: 4
    Last Post: 11-19-2009, 03:02 PM
  2. Tab control - tabs dissappearing
    By fddizon in forum Alpha Five Version 8
    Replies: 5
    Last Post: 04-17-2008, 10:53 AM
  3. Tab control - tabs dissappearing
    By aschone in forum Alpha Five Version 8
    Replies: 12
    Last Post: 11-30-2007, 11:00 AM
  4. Edit Data in Excel Spreadsheet
    By AndyLieberman in forum Alpha Five Version 8
    Replies: 6
    Last Post: 05-21-2007, 06:57 PM
  5. Tab control - with no tabs, easy separator
    By Steve Wood in forum Archived Wishlist
    Replies: 2
    Last Post: 12-15-2005, 06:48 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
  •