+ Reply to Thread
Results 1 to 5 of 5

Macro to determine size of each tab in workbook

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-23-2009
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2003 work, 2007 home
    Posts
    199

    Macro to determine size of each tab in workbook

    Hi Guys

    I am using the below macro to determine the size of each tab in a workbook of 12.4mb, however my results are becoming skewed somewhere along the line ie when I add up all the individual file sizes produced on the RESULTS tab I am getting 14.4mb. Can anyone see anything in the macro that could be causing this and suggest how to amend please?

    using 2003 for this one

    thanks

    Noel


    Sub FindSheetSizes()
    Dim Input_Workbook As Workbook
    Dim Output_Workbook As Workbook
    Dim ResultsSheet As Worksheet
     
    Dim objSheet As Object
     
    Dim StrPath As String
    StrPath = "P:\File Size Check\"
     
    Set objFSO = CreateObject("Scripting.FileSystemObject")
     
    Application.ScreenUpdating = False
    Set Input_Workbook = ActiveWorkbook
     
    Application.DisplayAlerts = False
     
    On Error Resume Next
    Sheets("RESULTS").Delete
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "RESULTS"
     
    Application.DisplayAlerts = True
     
    nRowCount = 0
     
    For Each objSheet In Input_Workbook.Sheets
     
    If objSheet.Name = "RESULTS" Then
     Application.ScreenUpdating = True
     Exit Sub
    End If
     
    objSheet.Copy
    Set Output_Workbook = ActiveWorkbook
    Output_Workbook.SaveAs StrPath & objSheet.Name & ".xls"
    Output_Workbook.Close
     
    Set objFile = objFSO.GetFile(StrPath & objSheet.Name & ".xls")
    objSize = objFile.Size / 1024
    objFile.Delete
     
    nRowCount = nRowCount + 1
    Sheets("RESULTS").Cells(nRowCount, 1).Value = objSheet.Name & "'s file size is " & objSize & " KB"
    Next
     
    Application.ScreenUpdating = True
      
    End Sub

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Macro to determine size of each tab in workbook

    Hello somesoldiers,

    You are not summing the size of the individual worksheets, but individual workbooks that contain a single worksheet.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Contributor
    Join Date
    04-23-2009
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2003 work, 2007 home
    Posts
    199

    Re: Macro to determine size of each tab in workbook

    Hi Leith

    I see what you mean, my first thoughts were to split the tabs into seperate workbooks and then return the individual workbook sizes but the spike in sizes is outside any acceptable range, can you suggest how I might get around the splitting and just produce the RESULTS tab within the active workbook?

    thanks a lot

    Noel
    Last edited by somesoldiers; 12-23-2009 at 03:40 PM. Reason: clarification

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Macro to determine size of each tab in workbook

    Hello somesoldiers,

    Each worksheet is embedded within the Excel workbook partly as executable directives, partly as constants, variables, and other objects. Excel also pads the worksheet buffer area based on its best guess of your future needs. Given all this, there is no built in function nor a macro that could be created outside of Excel to accurately tell you how much space the worksheet occupies on the disk.

  5. #5
    Forum Contributor
    Join Date
    04-23-2009
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2003 work, 2007 home
    Posts
    199

    Re: Macro to determine size of each tab in workbook

    Hi Leith

    ok, I understand, thanks for taking the time to explain

    Noel

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1