| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook size?
When you right-click a workbook in windows, and select 'Properties' you can
see the size of the workbook in bytes. Is it possible to get a workbooks size from within the workbook by VBA code? TIA, |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Workbook size?
Look for FileLen in VBA's help to get the size of the file already saved to
disk. MsgBox FileLen(ActiveWorkbook.FullName) "Charlotte E." wrote: When you right-click a workbook in windows, and select 'Properties' you can see the size of the workbook in bytes. Is it possible to get a workbooks size from within the workbook by VBA code? TIA, -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Workbook size?
Charlotte,
Try MyFileSize = FileLen(ThisWorkbook.FullName) which returns the size in bytes Mike "Charlotte E." wrote: When you right-click a workbook in windows, and select 'Properties' you can see the size of the workbook in bytes. Is it possible to get a workbooks size from within the workbook by VBA code? TIA, |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Workbook size?
Thanks guys :-)
Problem with the method is that it requires the wookbook to be saved! What I was looking for was a way to determine the size of the workbook AFTER changes has been made to it, but BEFORE saving it??? Anyway to do that? "Mike H" skrev i en meddelelse ... Charlotte, Try MyFileSize = FileLen(ThisWorkbook.FullName) which returns the size in bytes Mike "Charlotte E." wrote: When you right-click a workbook in windows, and select 'Properties' you can see the size of the workbook in bytes. Is it possible to get a workbooks size from within the workbook by VBA code? TIA, |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Workbook size?
Not that I know--but maybe someone else has an idea.
"Charlotte E." wrote: Thanks guys :-) Problem with the method is that it requires the wookbook to be saved! What I was looking for was a way to determine the size of the workbook AFTER changes has been made to it, but BEFORE saving it??? Anyway to do that? "Mike H" skrev i en meddelelse ... Charlotte, Try MyFileSize = FileLen(ThisWorkbook.FullName) which returns the size in bytes Mike "Charlotte E." wrote: When you right-click a workbook in windows, and select 'Properties' you can see the size of the workbook in bytes. Is it possible to get a workbooks size from within the workbook by VBA code? TIA, -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Workbook size?
Not that I know--but maybe someone else has an idea.
Got an idea of my own... Make the VBA code do this: 1) Read the Windows envioment variabel for the Windows Temp-directory (Normal: "C:\woindows\Temp\") 2) Save a temporary copy of the Workbook in that directory 3) Read the size of the file with the FilLen() as suggested 4) Return the Workbook to its previous location and name. 5) Delete the temporary copy. Now question is: How are that code gonna be??? Especially number 1 and 4 is giving me a hard time! Can anybody help??? TIA, Problem with the method is that it requires the wookbook to be saved! What I was looking for was a way to determine the size of the workbook AFTER changes has been made to it, but BEFORE saving it??? Anyway to do that? "Mike H" skrev i en meddelelse ... Charlotte, Try MyFileSize = FileLen(ThisWorkbook.FullName) which returns the size in bytes Mike "Charlotte E." wrote: When you right-click a workbook in windows, and select 'Properties' you can see the size of the workbook in bytes. Is it possible to get a workbooks size from within the workbook by VBA code? TIA, -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Workbook size?
Maybe get the size of a saved copy of your file, your original unsaved wb
remains unchanged (will exclude any vba code modules except sheet modules). Sub Test() Dim sFile As String, nSize as long sFile = Application.DefaultFilePath & "\TmpExcelFile.xls" On Error Resume Next ' just in case Kill Application.DefaultFilePath & "\TmpExcelFile.xls" On Error GoTo 0 ActiveWorkbook.Sheets.Copy ' the new copied wb is now active ActiveWorkbook.SaveAs sFile ActiveWorkbook.Close nSize = FileLen(sFile) Kill sFile MsgBox "indicative filesize " & Format(nSize / 1024, "0,000 Kb") End Sub Regards, Peter T "Charlotte E." wrote in message ... Not that I know--but maybe someone else has an idea. Got an idea of my own... Make the VBA code do this: 1) Read the Windows envioment variabel for the Windows Temp-directory (Normal: "C:\woindows\Temp\") 2) Save a temporary copy of the Workbook in that directory 3) Read the size of the file with the FilLen() as suggested 4) Return the Workbook to its previous location and name. 5) Delete the temporary copy. Now question is: How are that code gonna be??? Especially number 1 and 4 is giving me a hard time! Can anybody help??? TIA, Problem with the method is that it requires the wookbook to be saved! What I was looking for was a way to determine the size of the workbook AFTER changes has been made to it, but BEFORE saving it??? Anyway to do that? "Mike H" skrev i en meddelelse ... Charlotte, Try MyFileSize = FileLen(ThisWorkbook.FullName) which returns the size in bytes Mike "Charlotte E." wrote: When you right-click a workbook in windows, and select 'Properties' you can see the size of the workbook in bytes. Is it possible to get a workbooks size from within the workbook by VBA code? TIA, -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Workbook size?
Small typo, change the format string from
"0,000 Kb" to "#,##0 Kb" Peter T "Peter T" <peter_t@discussions wrote in message <snip MsgBox "indicative filesize " & Format(nSize / 1024, "0,000 Kb") |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Workbook size?
Excel's VBA has a workbook.savecopyas command that won't affect the current
workbook. Option Explicit Sub testme() Dim myStr As String myStr = Environ("Temp") & "\" & Format(Now, "yyyymmdd-hhmmss") & ".xls" ActiveWorkbook.SaveCopyAs Filename:=myStr MsgBox FileLen(myStr) Kill myStr End Sub ======= But this is cheating <vbg. You're still doing a save! "Charlotte E." wrote: Not that I know--but maybe someone else has an idea. Got an idea of my own... Make the VBA code do this: 1) Read the Windows envioment variabel for the Windows Temp-directory (Normal: "C:\woindows\Temp\") 2) Save a temporary copy of the Workbook in that directory 3) Read the size of the file with the FilLen() as suggested 4) Return the Workbook to its previous location and name. 5) Delete the temporary copy. Now question is: How are that code gonna be??? Especially number 1 and 4 is giving me a hard time! Can anybody help??? TIA, Problem with the method is that it requires the wookbook to be saved! What I was looking for was a way to determine the size of the workbook AFTER changes has been made to it, but BEFORE saving it??? Anyway to do that? "Mike H" skrev i en meddelelse ... Charlotte, Try MyFileSize = FileLen(ThisWorkbook.FullName) which returns the size in bytes Mike "Charlotte E." wrote: When you right-click a workbook in windows, and select 'Properties' you can see the size of the workbook in bytes. Is it possible to get a workbooks size from within the workbook by VBA code? TIA, -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Workbook size?
"SaveCopyAs"
Not the first time that's cropped up and I still forget about it! Peter T |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Workbook size?
Thanks, guys - got it to work just as I wanted - yes, it is cheating, but
sometimes that's just what is needed ;-) "Dave Peterson" skrev i en meddelelse ... Excel's VBA has a workbook.savecopyas command that won't affect the current workbook. Option Explicit Sub testme() Dim myStr As String myStr = Environ("Temp") & "\" & Format(Now, "yyyymmdd-hhmmss") & ".xls" ActiveWorkbook.SaveCopyAs Filename:=myStr MsgBox FileLen(myStr) Kill myStr End Sub ======= But this is cheating <vbg. You're still doing a save! "Charlotte E." wrote: Not that I know--but maybe someone else has an idea. Got an idea of my own... Make the VBA code do this: 1) Read the Windows envioment variabel for the Windows Temp-directory (Normal: "C:\woindows\Temp\") 2) Save a temporary copy of the Workbook in that directory 3) Read the size of the file with the FilLen() as suggested 4) Return the Workbook to its previous location and name. 5) Delete the temporary copy. Now question is: How are that code gonna be??? Especially number 1 and 4 is giving me a hard time! Can anybody help??? TIA, Problem with the method is that it requires the wookbook to be saved! What I was looking for was a way to determine the size of the workbook AFTER changes has been made to it, but BEFORE saving it??? Anyway to do that? "Mike H" skrev i en meddelelse ... Charlotte, Try MyFileSize = FileLen(ThisWorkbook.FullName) which returns the size in bytes Mike "Charlotte E." wrote: When you right-click a workbook in windows, and select 'Properties' you can see the size of the workbook in bytes. Is it possible to get a workbooks size from within the workbook by VBA code? TIA, -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Workbook size | Excel Discussion (Misc queries) | |||
| can i size an Excel workbook window's opening size? | Excel Discussion (Misc queries) | |||
| My workbook links are not updating (its 30,000 KB size workbook). | Excel Discussion (Misc queries) | |||
| workbook size | Excel Programming | |||
| Workbook size | Excel Programming | |||
Linear Mode
