glTaurus

There are two ways to get a list of files inside a folder with VBA. The first one is the Microsoft Scripting Library way, where the FileSystemObject is used. The second one is the "traditional" Dir command which returns a file each time the function is called. 'the scripting lib way Public Function GetListOfFiles(path As String, ParamArray Extensions()) As String() Dim fs As New FileSystemObject Dim fld As Folder: Set fld = fs.GetFolder(path) Dim fl As file Dim list() As String, count As Long: count = 0 For Each fl In fld.files Dim ext As String, extIncluded As Boolean, i As Long ext = fs.GetExtensionName(fl.name) extIncluded = False For i = 0 To UBound(Extensions) If LCase(ext) = LCase(Extensions(i)) Then extIncluded = True Exit For End If Next 'If ext = "xls" Or ext = "xlsb" Or ext = "xlsx" Or ext = "xlsm" Then ...

There are some ways to create and access Unicode files using VBA. The most straightforward way is to use the Microsoft Scripting Runtime library. The two following ways may be used to open a file for write operations: 'the scripting lib way Dim fs As New FileSystemObject, txt As TextStream Dim pth As String : pth = "d:\data.txt" Set txt = fs.CreateTextFile(pth, True, True) ''alternative way to open the textfile 'Set txt = fs.OpenTextFile(pth, ForWriting, True, TristateTrue) 'to write simply write the following in Unicode txt.WriteLine "test text" If we want to avoid using the Microsoft Scripting Runtime, we may use the following code; we access the output file in binary mode and we manually convert text to Unicode: 'the manual way Open pth For Binary Access Write As #1 Dim buffer() as Byte : buffer = "test text" Put #1, , buffer Close #1

Automation in Powerpoint 2007 is not so easy as it seems, because the macro recording is not available any more. What I wanted to do is to change the shape of some graphs to have a specific size and location. The following code changes the size and location of the current shape according to our preferences. Public Sub ChangeImage() With ActiveWindow.Selection.ShapeRange(1) .LockAspectRatio = msoFalse .Width = 720 .Height = 153.9213 .Top = 77.8604 .Left = 0 ' optional '.ZOrder msoSendToBack End With End Sub Public Sub PrintInfo() Debug.Print vbCrLf & "Public Sub ChangeImage()" Debug.Print vbTab & "With ActiveWindow.Selection.ShapeRange(1)" With ActiveWindow.Selection.ShapeRange(1) Debug.Print vbTab & vbTab & ".LockAspectRatio = msoFalse" Debug.Print vbTab & vbTab & ".Width = " & Str(.Width) Debug.Print vbTab & vbTab & ".Heigh...

Automation can really gives us the tools to do something very fast. The following code shows how to export all the modules in the current workbook to a destination folder. Public Sub ExportAll(targetPath as String) Dim xlApp As Excel.Application Dim xlWb As Excel.Workbook Dim VBComp As VBIDE.VBComponent ' Load workbook Set xlApp = Application 'xlApp.Visible = False Set xlWb = ActiveWorkbook 'xlApp.Workbooks.Open(sWorkbook) ' Loop through all files (components) in the workbook For Each VBComp In xlWb.VBProject.VBComponents ' Export the file If VBComp.Type = vbext_ct_StdModule Then _ VBComp.Export targetPath & VBComp.Name & ".bas" Next VBComp End Sub Two possible issues must be solved to correctly run this code. The first is to allow the code to access the VBA Object model programmatically. This can be done by correctly setting this from the Trust Center (from Excel Options). The second is to reference the Microsoft Visua...