Listing A Simple Example of Standard Workbook Properties

Sub TestPrintGenera1WBInfo()

PrintGeneralWorkbookInfo ThisWorkbook End Sub

Sub PrintGenera1WorkbookInfo(wb As Workbook) Debug.Print "Name: " & wb.Name Debug.Print "Full Name: " & wb.FullName Debug.Print "Code Name: " & wb.CodeName Debug.Print "FileFormat: " & GetFileFormat(wb) Debug.Print "Path: " & wb.Path If wb.ReadOnly Then

Debug.Print "The workbook has been opened as read-only.'

Else

Debug.Print "The workbook is read-write." End If

If wb.Saved Then

Debug.Print "The workbook does not need to be saved."

Else

Debug.Print "The workbook should be saved." End If End Sub

Function GetFileFormat(wb As Workbook) As String Dim lFormat As Long Dim sFormat As String lFormat = wb.FileFormat Select Case lFormat

Case Case Case Case Case Case Case Case Case Case Case Case Case Case Case Case Case Case Case Case Case Case Case Case Case Case Case Case Case Case Case Case

Excel 2 Far East"

Excel 4 Workbook"

AddIn: sFormat = "Add-in" CSV: sFormat = "CSV" CSVMac: sFormat = "CSV Mac" CSVMSDOS: sFormat = "CSV MS DOS" CSVWindows: sFormat = "CSV Windows"

CurrentPlatformText: sFormat = "Current Platform Text" DBF2: sFormat = "DBF 2" DBF3: sFormat = "DBF 3" DBF4: sFormat = "DBF 4" DIF: sFormat = "DIF" Excel2: sFormat = "Excel 2" Excel2FarEast: sFormat = " Excel3: sFormat = "Excel 3 Excel4: sFormat = "Excel 4 Excel4Workbook: sFormat = Excel5: sFormat = "Excel 5 Excel7: sFormat = "Excel 7 Excel9795: sFormat = "Excel 97/95" Html: sFormat = "HTML" IntlAddIn: sFormat = "Int'l AddIn" IntlMacro: sFormat = "Int'l Macro" SYLK: sFormat = "SYLK" Template: sFormat = "Template" TextMac: sFormat = "Text Mac" TextMSDOS: sFormat = "Text MS DOS" TextPrinter: sFormat = "Text Printer" TextWindows: sFormat = "Text Windows" UnicodeText: sFormat = "Unicode Text" WebArchive: sFormat = "Web Archive" WJ2WD1: sFormat = "WJ2WD1" WJ3: sFormat = "WJ3" WJ3FJ3: sFormat = "WJ3FJ3"

Case

xlWK1: sFormat =

"WK1"

Case

xlWK1ALL: sFormat

= "WK1ALL"

Case

xlWK1FMT: sFormat

= "WK1FMT"

Case

xlWK3: sFormat =

"WK3"

Case

xlWK3FM3: sFormat

= "WK3FM3"

Case

xlWK4: sFormat =

"WK4"

Case

xlWKS: sFormat =

"WKS"

Case

xlWorkbookNormal:

sFormat = "Normal workbook

Case

xlWorks2FarEast:

sFormat = "Works 2 Far East

Case

xlWQ1: sFormat =

"WQ1"

Case

xlXMLSpreadsheet:

sFormat = "XML Spreadsheet

Case

Else: sFormat = "

Unknown format code"

End Select

GetFileFormat = sFormat End Function

GetFileFormat = sFormat End Function

If you didn't care about translating the value returned from the FileFormat property into a more user-friendly value, you could do away with the lengthy GetFileFormat function. Running the TestPrintGeneralWBInfo from Listing 6.8 produces the following output (your output may vary).

Name: Chapter Six Examples.xls

Full Name: C:\Chapter Six Examples.xls

Code Name: ThisWorkbook

FileFormat: Normal workbook

The workbook is read-write. The workbook should be saved.

0 0

Post a comment