Old data still in workbook (bloat)

[djreiswig]

I have a workbook with several sheets and lots of macros. When the workbook is opened, a sub loads some data from a text file into a sheet and allows the user to use this data to fill out and print a form. When the sheet is closed it is not saved (not allowed by my macros), thus the data that was loaded is never saved in the workbook. I know this is the case because I have loaded the workbook without macros and checked the data sheet and it has only one header row with data. The rest of the sheet is blank and the end of the sheet is at row 1 (or 2 I can't remember).

Just for kicks I loaded the sheet into Notepad, and I can see a bunch of the data that is from the text file inside the Excel file. It isn't there when I load the sheet in Excel. How can I get rid of this data? I don't want to recreate the sheets or the workbook because it is set up to work exactly how I want it to. Is there some way to get rid of this extra data?

 

[capparfrank]

What version of excel?

Can you post the code that loads the text file?

 

[djreiswig]

2003

The code is just a basic line input into the rows of a sheet, and then a text to columns to break it up.

This sub calls the second sub to fill the sheet, and then splits the data into columns. I use Chr(160) for my delimiter because I wanted something that couldn't be typed (ex , ; : .) I ran into problems early on with using any of the normal delimiters. It looks like a space, but isn't.

Sub ReadItems()
    Application.Cursor = xlWait
    ReadText MyDocumentsDir & "\Item List.dat", ThisWorkbook.Sheets("ItemData")
    'split data into columns
    With ThisWorkbook.Sheets("ItemData")
        'fill in header rows
        .Range("A1").Value = "SKU" & Chr(160) & "Description" & Chr(160) & "FilterGroup" & Chr(160) & _
            "PrintGroup" & Chr(160) & "Pkg Qty"
        'find last row in data sheet
        LastRow = .Range("A" & Rows.Count).End(xlUp).Row
        Application.DisplayAlerts = False

        'uses Chr(160) for delimiter since shouldn't be contained in any entries
        .Range("A1:A" & LastRow).TextToColumns DataType:=xlDelimited, TextQualifier:=xlTextQualifierNone, _
            ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, _
            OtherChar:=Chr(160), FieldInfo:=Array(Array(1, xlGeneralFormat), Array(2, xlTextFormat), _
            Array(3, xlTextFormat), Array(3, xlTextFormat), Array(4, xlGeneralFormat))
            Application.DisplayAlerts = True
    End With
    Application.Cursor = xlDefault
End Sub

This sub actually imports the file.

Sub ReadText(ByVal strPathAndFilename As String, shtSheetToRead As Worksheet)
    i = FreeFile
    Open strPathAndFilename For Binary Access Read As #i
    If LOF(i) > 0 Then
        While Not EOF(i)
            'import data into column A of sheet
            Line Input #i, GotText
            rowI = rowI + 1
            shtSheetToRead.Range("A" & rowI + 2).Value = CStr(GotText)
        Wend
        Close #i
    Else
        'delete file if it is empty
        Close #i
        On Error Resume Next
        Kill strPathAndFilename
        On Error GoTo 0
    End If
End Sub

Incidentally, I use the second sub in another place, and I don't see any of that data in the file. Just the data that is loaded by the first sub.

Some of this data used to be in stored this workbook in a previous version of this program, but I removed the data and now store it in a text file. I suppose it could be hanging around from that time, but how do I get rid of it? I can't see any of it anywhere in any of my sheets. I have checked all of the sheets, and the end of the sheet is where I expect it to be, and none of the data is there until it is loaded.

 

[djreiswig]

Okay, never mind.

It appears that my Before Save routine didn't run and my data didn't get removed from the file before it was saved. I fixed it, and saved it again, and now the data doesn't appear to be in the file. I have several versions of this workbook, and I must have been looking at an older one that saved correctly when I saw that the sheets were empty, and a newer one that didn't when I saw the data in notepad.

All's good now.

 

[capparfrank]

Glad you found your trouble. Sry I didn't get to be more help.

 

[djreiswig]

More help than you know. Got me to looking at things again.