Excel VBA File Size by Worksheet in File

[Biz]

Dear All,

I am trying to create a VBA that can tell and Excel File size eg 50mb and sheet 1 = 1mb, sheet2 = 1mb, sheet3 = 48mb.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Is this possible?<o></o>
<o> </o>
Your help would be greatly appreciated.<o></o>


Biz

 

[crewaustin66]

i have a question thought. how could you amend the code, so that each time i run the code, it clears the Immediate window of previously printed info and replaces it with the updated one?

I found this:

Sub ClearDebugConsole
  Dim i as integer
  for i = 0 to 100
    debug.print ""
  next

I'm putting my output into a form with a table table object.

 

[jamilm]

I found this:

Sub ClearDebugConsole
  Dim i as integer
  for i = 0 to 100
    debug.print ""
  next

I'm putting my output into a form with a table table object.

this one does not work


try this one and call it from the same code.

[LIST]
[*][INDENT]Option Explicit

Private Declare Function GetWindow Lib "user32" (ByVal hWnd As Long, ByVal wCmd As Long) As Long

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Private Declare Function GetKeyboardState Lib "user32" (pbKeyState As Byte) As Long
Private Declare Function SetKeyboardState Lib "user32" (lppbKeyState As Byte) As Long
Private Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long

Private Const WM_KEYDOWN As Long = &H100
Private Const KEYSTATE_KEYDOWN As Long = &H80

Private savState(0 To 255) As Byte

Sub ClearImmediateWindow()
'Adapted by keepITcool
'Original from Jamie Collins fka “OneDayWhen”
'http://www.dicks-blog.com/excel/2004/06/clear_the_immed.html

Dim hPane As Long
Dim tmpState(0 To 255) As Byte

hPane = GetImmHandle
If hPane = 0 Then MsgBox "Immediate Window not found."
If hPane < 1 Then Exit Sub

'Save the keyboardstate
GetKeyboardState savState(0)

'Sink the CTRL (note we work with the empty tmpState)
tmpState(vbKeyControl) = KEYSTATE_KEYDOWN
SetKeyboardState tmpState(0)
'Send CTRL+End
PostMessage hPane, WM_KEYDOWN, vbKeyEnd, 0&
'Sink the SHIFT
tmpState(vbKeyShift) = KEYSTATE_KEYDOWN
SetKeyboardState tmpState(0)
'Send CTRLSHIFT+Home and CTRLSHIFT+BackSpace
PostMessage hPane, WM_KEYDOWN, vbKeyHome, 0&
PostMessage hPane, WM_KEYDOWN, vbKeyBack, 0&

'Schedule cleanup code to run
Application.OnTime Now + TimeSerial(0, 0, 0), "DoCleanUp"

End Sub

Sub DoCleanUp()
' Restore keyboard state
SetKeyboardState savState(0)
End Sub

Function GetImmHandle() As Long
'This function finds the Immediate Pane and returns a handle.
'Docked or MDI, Desked or Floating, Visible or Hidden

Dim oWnd As Object, bDock As Boolean, bShow As Boolean
Dim sMain$, sDock$, sPane$
Dim lMain&, lDock&, lPane&

On Error Resume Next
sMain = Application.VBE.MainWindow.Caption
If Err <> 0 Then
MsgBox "No Access to Visual Basic Project"
GetImmHandle = -1
Exit Function
' Excel2003: Registry Editor(Regedit.exe)
' HKLM \ SOFTWARE \ Microsoft \ Office \ 11# \ Excel \ Security
' Change or add a DWORD called ‘AccessVBOM’, set to 1
' Excel2002: Tools/Macro/Security
' Tab ‘Trusted Sources’, Check ‘Trust access..’
End If

For Each oWnd In Application.VBE.Windows
If oWnd.Type = 5 Then
bShow = oWnd.Visible
sPane = oWnd.Caption
If Not oWnd.LinkedWindowFrame Is Nothing Then
bDock = True
sDock = oWnd.LinkedWindowFrame.Caption
End If
Exit For
End If
Next
lMain = FindWindow("wndclass_desked_gsk", sMain)
If bDock Then
'Docked within the VBE
lPane = FindWindowEx(lMain, 0&, "VbaWindow", sPane)
If lPane = 0 Then
'Floating Pane.. which MAY have it’s own frame
lDock = FindWindow("VbFloatingPalette", vbNullString)
lPane = FindWindowEx(lDock, 0&, "VbaWindow", sPane)
While lDock > 0 And lPane = 0
lDock = GetWindow(lDock, 2)

lPane = FindWindowEx(lDock, 0&, "VbaWindow", sPane)
Wend
End If
ElseIf bShow Then
lDock = FindWindowEx(lMain, 0&, "MDIClient", _
vbNullString)
lDock = FindWindowEx(lDock, 0&, "DockingView", _
vbNullString)
lPane = FindWindowEx(lDock, 0&, "VbaWindow", sPane)
Else
lPane = FindWindowEx(lMain, 0&, "VbaWindow", sPane)
End If

GetImmHandle = lPane

End Function[/INDENT]




[/LIST]

 

[President]

when i run the code of Sheetsize at post #9 then i get the following in my immediate window

ExcelFile.xlsb 24,249 Bytes
Wb Object 2,670 Bytes
Sheet1 7,193 Bytes
Sheet2 7,194 Bytes
Sheet3 7,192 Bytes

now i tried


Dim i as integer for i = 0 to 100 debug.print "" next</pre>
did not work.

also i tried code on post #12 it only deletes one line.


any help?

 

[ZVI]

1. Instead of Debug.Print you may use MsgBox or show userform with output information.
2. This can be used as a simple alternative: Debug.Print String(255, vbLf)
3. See the 32-bit API solution in this link Daily Dose of Excel » Blog Archive » Clear the Immediate Window

 

[President]

thank you Vladimir Zakharov

it worked.

1. Instead of Debug.Print you may use MsgBox or show userform with output information.
2. This can be used as a simple alternative: Debug.Print String(255, vbLf)
3. See the 32-bit API solution in this link Daily Dose of Excel » Blog Archive » Clear the Immediate Window