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