Export/import sheet ends up in Class Module

[Mike]

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.

 

[joel]

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

 

[Chip Pearson]

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)

 

[Mike]

Thanks Chip.
Mike

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)