Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export/import sheet ends up in Class Module
In Excel 2007, when I Export a sheet using File/Export and then Import it to
another workbook, the sheet ends up in a Class Module instead of under the Microsoft Excel Objects where it originally resided. Am I missing something here? Thanks for any help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export/import sheet ends up in Class Module
It is not clear exactly what you want. It sound like you have a group of
tables on one worksheet that has blank rows between each table. In each table there is a row that contains "Use Start". You only want to keep certain rows in the table above and below "Use Start" When you delete rows the easiest way is to start at the end of the worksheet and go up the worksheet to row 1. This way when you delete a row you can still decrement by one to get to the next row. When you move in the other direction you need to add one row when you don't delete and don't add one row when you do delete. Sub DeleteRows() Keep1 = Array("Mgr", "Agent") Keep2 = Array("Mon1", "Tue1", "Wed1", "Thur1", "Fri1", "Sat") LastRow = Range("A" & Rows.Count).End(xlUp).Row RowCount = LastRow AboveUseStart = True Do While RowCount = 1 Select Case Range("A" & RowCount) Case "": AboveUseStart = True Case "Use Start": AboveUseStart = False Case Else If AboveUseStart = True Then SearchStr = Keep2 Else SearchStr = Keep1 End If Found = False For Each itm In SearchStr If UCase(Range("A" & RowCount)) = ucase(itm) Then Found = True Exit For End If Next itm If Found = False Then Rows(RowCount).Delete End If End Select RowCount = RowCount - 1 Loop End Sub "Mike" wrote: In Excel 2007, when I Export a sheet using File/Export and then Import it to another workbook, the sheet ends up in a Class Module instead of under the Microsoft Excel Objects where it originally resided. Am I missing something here? Thanks for any help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export/import sheet ends up in Class Module
You're not missing anything. Exporting any of the Sheet modules or the
ThisWorkbook module creates a file with a .cls extension. The import process can't determine that the class file is ThisWorkbook or a Sheet module. ("Can't" isn't the right term. "Doesn't" is better -- it could have been done, but wasn't.) You can import the file with VBA's I/O procedures: Sub AAA() Dim FNum As Integer Dim FName As String Dim S As String Dim N As Long FName = "C:\ThisWorkbook.cls" '<<< CHANGE FILE NAME FNum = FreeFile Open FName For Input Access Read As #FNum With ThisWorkbook.VBProject.VBComponents("ThisWorkbook" ).CodeModule .DeleteLines 1, .CountOfLines Line Input #FNum, S ' skip headers For N = 1 To 8 Line Input #FNum, S Next N Line Input #FNum, S Do Until EOF(FNum) .InsertLines .CountOfLines + 1, S Line Input #FNum, S Loop .InsertLines .CountOfLines + 1, S End With End Sub Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 29 Apr 2009 09:48:14 -0700, "Mike" wrote: In Excel 2007, when I Export a sheet using File/Export and then Import it to another workbook, the sheet ends up in a Class Module instead of under the Microsoft Excel Objects where it originally resided. Am I missing something here? Thanks for any help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export/import sheet ends up in Class Module
Thanks Chip.
Mike "Chip Pearson" wrote in message ... You're not missing anything. Exporting any of the Sheet modules or the ThisWorkbook module creates a file with a .cls extension. The import process can't determine that the class file is ThisWorkbook or a Sheet module. ("Can't" isn't the right term. "Doesn't" is better -- it could have been done, but wasn't.) You can import the file with VBA's I/O procedures: Sub AAA() Dim FNum As Integer Dim FName As String Dim S As String Dim N As Long FName = "C:\ThisWorkbook.cls" '<<< CHANGE FILE NAME FNum = FreeFile Open FName For Input Access Read As #FNum With ThisWorkbook.VBProject.VBComponents("ThisWorkbook" ).CodeModule .DeleteLines 1, .CountOfLines Line Input #FNum, S ' skip headers For N = 1 To 8 Line Input #FNum, S Next N Line Input #FNum, S Do Until EOF(FNum) .InsertLines .CountOfLines + 1, S Line Input #FNum, S Loop .InsertLines .CountOfLines + 1, S End With End Sub Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 29 Apr 2009 09:48:14 -0700, "Mike" wrote: In Excel 2007, when I Export a sheet using File/Export and then Import it to another workbook, the sheet ends up in a Class Module instead of under the Microsoft Excel Objects where it originally resided. Am I missing something here? Thanks for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import data from another sheet that matches to a class list? | Excel Worksheet Functions | |||
Right-Click Sheet, Move or Copy, & Class Module | Excel Programming | |||
Default Folder for Import/Export Module | Excel Programming | |||
Import into a Sheet module | Excel Programming | |||
Variable from a sheet module in a class module in XL XP | Excel Programming |